I cannot seem to get this right, I am trying to modify a field to be a foreign key, with cascading delete... what am i doing wrong?
ALTER TABLE my_table
ADD CONSTRAINT $4
FOREIGN KEY my_field
REFERENCES my_foreign_table
ON DELETE CASCADE;
Add a comment
|
4 Answers
It would help if you posted the error message. But I think you are just missing the parenthesis:
ALTER TABLE my_table
ADD CONSTRAINT my_fk
FOREIGN KEY (my_field)
REFERENCES my_foreign_table
ON DELETE CASCADE;
4 Comments
Richard D
@Stephane See the accepted answer to this question "A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete." Seems like something you wouldn't want to automatically include unless you wanted to bind the tables together instead of just creating a reference.
Line
@RichardD, when You don't include it, You must use CASCADE keyword with query, and You remember of consequences then :)
Steen Schütt
@Line it can be very useful for the delete to cascade automatically if the data with the foreign key doesn't make sense without the data it is referencing. Then there would be no point in saving it after deleting the referenced data anyway.
NuclearPeon
If you need to reference a column in a table, use
REFERENCES my_foreign_table(my_foreign_column).Just guessing: shouldn't you add a foreign key instead of a constraint?
ALTER TABLE my_table ADD FOREIGN KEY (my_field) REFERENCES my_foreign_table;
Comments
I'm still somehow missing here an answer with foreign column (foreign_field) explicitly specified:
ALTER TABLE my_table
ADD CONSTRAINT my_fk
FOREIGN KEY (my_field)
REFERENCES my_foreign_table (foreign_field)
ON DELETE CASCADE;
Comments
This works to me, I add the column to the table and then add the constraint with references to the other table:
-- add column to table
ALTER TABLE schema.table ADD COLUMN my_column type;
-- add constraint to column
ALTER TABLE schema.table ADD CONSTRAINT fk_name FOREIGN KEY (column)
REFERENCES schema.table (column) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION;