0

Since this is my first day of learning SQL, I am trying to add a foreign key to a column in a table referring to another column in another table. I am getting an error like "Error Code 1215: Cannot add Foreign Key Constraint". I have read the documentation on many websites, I couldn't get any solutions. I am not familiar with technical language or knowledge in SQL, so help me with simple answers to proceed further. Thanks in advance.

Here is the snippet I used to add a foreign key to an existing table. "ALTER TABLE location_details ADD CONSTRAINT FK_location FOREIGN KEY (Zip) REFERENCES student_details(Zip);"

June7
  • 19,874
  • 8
  • 24
  • 34
user123
  • 21
  • 1
  • Do you have existing data that conflicts with this foreign key? Like a row in `location_details` with a `Zip` that doesn't exist in any row in `student_details`? – obe May 23 '21 at 14:19
  • What is the DDL of your tables? You can add FK only to unique columns (unique or PK constrained) – astentx May 23 '21 at 14:24
  • i have **Zip** as a column in both the **location_details** and **student_details**, but **Zip** in **students_details** table is not Unique. – user123 May 23 '21 at 14:34
  • @astentx I have added DDL in the question. – user123 May 23 '21 at 14:38
  • *I have added DDL in the question.* Where? https://i.stack.imgur.com/ncuId.png - I do not see.. – Akina May 23 '21 at 14:41
  • I hope this is the DDL, "ALTER TABLE location_details ADD CONSTRAINT FK_location FOREIGN KEY (Zip) REFERENCES student_details(Zip);" Correct me if i m wrong. – user123 May 23 '21 at 14:46
  • DDL of the tables. But I'm sorry for wrong statement, My SQL allows you to build weird model with non-unique referenced column: [db<>fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=74181e98c25107ca4b036a42a809c83d) – astentx May 23 '21 at 14:57
  • Does this answer your question? [MySQL Error 1215: Cannot add foreign key constraint](https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) – Paul T. May 23 '21 at 22:33
  • Solved the issue. Also pls clarify that, "ALTER TABLE location_details ADD CONSTRAINT FK_location FOREIGN KEY (Zip) REFERENCES student_details(Zip);" is this said to be DDL of the table? – user123 May 24 '21 at 11:00
  • What do we do if a Foreign Key from child table referrring to Primary Key in Parent Table(if more than one column is said to be primary key)? – user123 May 24 '21 at 11:16

0 Answers0