Can anyone explain to me where I made a mistake and how to fix this error? I've tried several ways that are suggested on the stackoverflow site, but it still doesn't work.

Am using Laravel 8 and MySql 8.0.25 version.

This table contains information about customer addresses. The customer can have multiple addresses but only one primary one. The primary address is in the "primarna" column and is set to be unique. I also set up a unique "subjekti_id" (en: customer_id) column. The other columns are foreign keys that point to the country, city, and post ID. It should be noted that I have only on one foreign key defined onDelete('CASCADE') function for the reason that when the customer is deleted I want all addresses from this customer to be deleted from this table. All other foreign keys for country, city and post onUpdate and onDelete is set to SET NULL.

This is ERROR:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'subjekat_adrese_subjekti_id_unique' (SQL: insert into subjekat_adrese (naziv, adresa, subjekti_id, drzave_id, gradovi_id, poste_id, primarna, updated_at, created_at) values (Test name, Test address, 1, 2, 5, 2, ?, 2022-06-07 06:46:17, 2022-06-07 06:46:17))

Databas Schema:

Database records

Only one record have.

id | naziv |   Adresa     | subjekat_id | drzave_id| gradovi_id | poste_id | primarna 
-----------------------------------------------------------------------------------
'1','Test',' Test address'      '1',         '2',       '5',        '2',     '1',

Indexes & Foreign keys Only one key is set onDlete cascade. All other is SET NULL.

What i try:

insert into `subjekat_adrese` (`naziv`, `adresa`, `subjekti_id`, `drzave_id`, `gradovi_id`, `poste_id`, `primarna`)
 values ('Test name', 'Test address', '1', '2', '5', '2', null');
       

This return:

 Error Code: 1062. Duplicate entry '1' for key 'subjekat_adrese_subjekti_id_unique'

If i try to insert non existing unique ('subjekat_id')

insert into `subjekat_adrese` (`naziv`, `adresa`, `subjekti_id`, `drzave_id`, `gradovi_id`, `poste_id`, `primarna`)
     values ('Bla bla', 'bla bla', '2', '3', '3', '3', null');
       

This return this:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`laravel`.`subjekat_adrese`, CONSTRAINT `subjekat_adrese_subjekti_id_foreign` FOREIGN KEY (`subjekti_id`) REFERENCES `subjekti` (`id`) ON DELETE CASCADE ON UPDATE SET NULL)

UPDATE (ADD MIGRATION)

 public function up()
    {
        Schema::create('subjekat_adrese', function (Blueprint $table) {
           
            $table->bigIncrements('id');
           
            $table->string('naziv')->default("N/O");
            $table->string('adresa');

            $table->boolean('primarna')->unique()->nullable()->default(NULL);

            $table->timestamps();

            
        });

        Schema::table('subjekat_adrese', function($table) {

            $table->foreignId('subjekti_id')
                ->unique()
                ->nullable()
                ->constrained('subjekti')
                ->onDelete('cascade')
                ->onUpdate('set null');

            $table->foreignId('drzave_id')
                ->nullable()
                ->constrained('drzave')
                ->onDelete('set null')
                ->onUpdate('set null');

            $table->foreignId('gradovi_id')
                ->nullable()
                ->constrained('gradovi')
                ->onDelete('set null')
                ->onUpdate('set null');

            $table->foreignId('poste_id')
                ->nullable()
                ->constrained('poste')
                ->onDelete('set null')
                ->onUpdate('set null');
        });
    }

Update 2 Screenshot of the subjekti table records.

CodePudding user response：

the reason of the first error Error Code: 1062. Duplicate entry '1' for key 'subjekat_adrese_subjekti_id_unique'

is the making of subjekti_id as unique , this field should'nt be unique here as it's a foreign key and it may be attached to multiple rows.

and about why the second error appears Error Code: 1062. Duplicate entry '1' for key 'subjekat_adrese_subjekti_id_unique'

is that there is no row at subjekti with id 2

CodePudding user response：

Your foreign key can not be unique alone. For your scenario you should make unique subjekti_id primarna together.

$table->unique(['subjekti_id', 'primarna']);