Menu
How to alter an existing table in MySQL, setting foreign key to another table, using the command line?
903
Here is how you would do that: ALTER TABLE my_table ADD FOREIGN KEY (key) REFERENCES other_table(id) ON DELETE SET NULL; And that's it!! That's how you change a foreign key constraint in MySQL!
Here's the syntax to create foreign key in MySQL. ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (foreign_key_name,...) REFERENCES parent_table(column_name,...); In the above query, table_name is the the table where you want to add foreign key.
Here, you will see how to make the student_id attribute foreign key in the exam table which is the primary key in the student table as follows. ALTER TABLE exam ADD FOREIGN KEY(student_id) REFERENCES student(student_id);
You have to drop existing foreign key and create another one. For example like this:
ALTER TABLE my_table DROP FOREIGN KEY my_key;
ALTER TABLE my_table ADD CONSTRAINT my_key FOREIGN KEY ('some_id')
REFERENCES some_new_table ('some_other_id') ON UPDATE CASCADE ON DELETE CASCADE;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
