MySQL Cannot Add Foreign Key Constraint

MySQL Cannot Add Foreign Key Constraint

To find the specific error run this:

SHOW ENGINE INNODB STATUS;

And look in the LATEST FOREIGN KEY ERROR section.

The data type for the child column must match the parent column exactly. For example, since medicalhistory.MedicalHistoryID is an INT, Patient.MedicalHistory also needs to be an INT, not a SMALLINT.

Also, you should run the query set foreign_key_checks=0 before running the DDL so you can create the tables in an arbitrary order rather than needing to create all parent tables before the relevant child tables.

MySQL Error 1215: Cannot add foreign key constraint

I'm guessing that Clients.Case_Number and/or Staff.Emp_ID are not exactly the same data type as Clients_has_Staff.Clients_Case_Number and Clients_has_Staff.Staff_Emp_ID.

Perhaps the columns in the parent tables are INT UNSIGNED?

They need to be exactly the same data type in both tables.

MySQL error #1215: Cannot add Foreign Key Constraint

Make sure that:

• the foreign key references ALL the attributes of the key of the referenced relation (you cannot reference only some of them). SO if the key of R is (A,B), you should reference both A and B. You cannot reference only A or only B.
• the attribute(s) you are referencing in a foreign key constraint is defined as UNIQUE or is the Primary key of the relation. In Mysql this can also be done by defining the attribute a KEY or UNIQUE KEY.
• If you are referencing the primary key you don't need to indicate the name of the attribute after the table (the default is its primary key).

In your case,

• make sure that the table recipe_steps has an attribute recipe_steps_id, and
• this attribute is either UNIQUE KEY, KEY or the primary key of the relation
• Also, make sure the corresponding index (for the key) has been created.

MySQL Error Code 1215: cannot add foreign constraints

Please create child table like below as reference column of parent table is missing:

CREATE OR REPLACE TABLE customer(
  book_id int unsigned not null,
  customer_id char (50),
  PRIMARY KEY (customer_id),
  foreign key (book_id) references book (book_id)
) ENGINE = InnoDB DEFAULT CHARSET = latin1; 

MYSQL - Cannot add foreign key constraint, but why?

You want just one foreign key instead of two, that references the set of columns that is the primary key in the parent table.

So change this:

FOREIGN KEY (building) REFERENCES Classroom(building),
FOREIGN KEY (room_no) REFERENCES Classroom(room_no)

To:

FOREIGN KEY (building, roo_no) REFERENCES Classroom(building, roo_no)

It is important to note that:

• the datatypes (and length, or precision) of the columns must be identical

• the columns in the foreign key must appear in the same order as in the primary key

Unrelated: year is a language keyword in MySQL, hence not a good choice for a column name.

Error when creating foreign key: MySQL Error 1215: Cannot add foreign key constraint

The foreign key and primary key need to have the exact same definition. The worker table defines DEFAULT CHARACTER SET = latin1.

I'm not sure why you would want different character sets for different tables. I would recommend just using the database default.

If you do need them, then make the character sets compatible. You can do this at the column level:

CREATE TABLE IF NOT EXISTS `task` (
  `idtask` INT(11) NOT NULL,
  `attGora` VARCHAR(200) NULL,
  `status` VARCHAR(45) NULL,
  `to` VARCHAR(45) NULL,
  `jobstatus_id` INT(11) NOT NULL,
  `worker_personnummer` VARCHAR(45) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`idtask`, `jobstatus_id`, `worker_personnummer`),
  INDEX `fk_task_jobstatus1_idx` (`jobstatus_id` ASC),
  INDEX `fk_task_worker1_idx` (`worker_personnummer` ASC),
  CONSTRAINT `fk_task_worker1`
    FOREIGN KEY (`worker_personnummer`)
    REFERENCES `worker` (`personnummer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint laravel 9

I had the same problem.
The problem arises when a model has a relationship with itself (self-relation).
To solve this problem, first, the migration file must be created and then the foreign key must be assigned in another migration file.
You must remove the foreign key assignment from the migration file and create the new migration file after that, then add relations statements to assign a foreign key. (the order of the migration files is important).

create_category_table

public function up(): void
{
    $table->bigIncrements('id');
    $table->string('key', 64)->unique();
    $table->string('parent_key', 64)->nullable()->index();
    $table->string('title', 256)->index()->unique();

}

create_category_relation_table

public function up(): void
{
    $table->foreign('parent_key')->references('key')
            ->on((new Category())->getConnection()->getDatabaseName() . '.' . Category::TABLE)
            ->onDelete('cascade');
}

And then php artisan migration

---

Related Topics