Here, you will see how to add another column student_id into the exam table as follows. Step-7: Adding another column student_id into the exam table : Output : Field Type Null Key Default Extra exam_id int NO PRI NULL exam_name varchar(20) YES NULL In this step, you can verify the table you have created.
Step-6: Viewing the description of the table : In this step, you will see one more table for reference. Field Type Null Key Default Extra student_id int NO PRI NULL student_name varchar(20) YES NULL student_branch varchar(20) YES NULL Here, as you can see in the description the key column of student_id is PRI which means it is the primary key in that table student. Difference between Natural join and Inner Join in SQL.SQL | Join (Cartesian Join & Self Join).Difference between Where and Having Clause in SQL.SQL general functions | NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL and NANVL.
Difference between DELETE, DROP and TRUNCATE.How to find Nth highest salary from a table.
On the other hand, you may not add or update a child row because the child column must align its values with the parent's to keep the referential integrity that we defined early in this post. The added foreign key constraint here will be used to enforce the referential integrity.įor example, you may not delete or update on the parent column because the child column prevents your change from violating the integrity. Everything you do such as update or delete is bound to the rigid relationship. Referential Integrity (RI)įrom now on, you have to respect the relationship. mysql> alter table orders add constraint orders_customer_id_fk foreign key (customer_id) references customers (id) on delete cascade on update cascade Now, we can create the foreign key constraint without errors. mysql> alter table orders modify column customer_id int(11) unsigned not null So next, let's make them equivalent in size and sign by modifying the column customer_id as unsigned. Is it too picky? Sorry, the integer column pair pursuits exactly the same. That's why we cannot create the foreign key constraint. So the final verdict is that they are different. mysql> desc orders Īs you can see above, the column customer_id is not unsigned in the first table orders. Now, let's go deeper to compare the difference between the referencing and referenced column. That's why we got ERROR 1215 (HY000) in the first place. For nonbinary (character) string columns, the character set and collation must be the same. The length of string types need not be the same. The size and sign of integer types must be the same.
Corresponding columns in the foreign key and the referenced key must have similar data types.
I quote one precondition of adding a foreign key constraint from MySQL 8.0 Reference Manual below to sustain my point. But how different is "different"? how same is "same"? Sometimes, they should be exactly the same even in every tiny specification. Same data type of two columns is good to go. Column Definition Mismatchīasically, we can't build referential relationship between two different data types of columns. If you still got "ERROR 1215 (HY000): Cannot add foreign key constraint", you can go on reading this post because things are getting complicated. mysql> alter table customers add index (email) mysql> alter table customers add index customers_email_idx (email) For example, adding an index with an explicit name. The cure is pretty easy, just add indexes on columns that are required to have. So please make sure that you have indexes on the parent and child columns before adding any foreign key constraints. One obvious mistake is that you do not have indexes both on the referenced and referencing columns. We have to find out the root cause by ourselves. It only shows that it failed to create a foreign key constraint, but I don't see any hints or useful information in it. Maybe the message of ERROR 1215 (HY000) is somewhat shallow. ERROR 1215 (HY000) Cannot add foreign key constraint Rationale and Solutions for Cannot add foreign key constraint