Postgresql constraint

Question

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;

score 81 · Accepted Answer · edited Dec 11 '14 at 12:08

81

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;

edited Dec 11 '14 at 12:08

Line

1,529
3
18
42

answered Jun 09 '10 at 12:02

Magnus Hagander

23,890
5
56
43

4

@Stephane See the accepted answer to [this question](http://stackoverflow.com/questions/14141266/postgresql-foreign-key-on-delete-cascade) "_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. – Richard D Jun 21 '13 at 15:22
@RichardD, when You don't include it, You must use CASCADE keyword with query, and You remember of consequences then :) – Line Dec 11 '14 at 11:55
1

@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. – Steen Schütt Mar 28 '15 at 13:26
If you need to reference a column in a table, use `REFERENCES my_foreign_table(my_foreign_column)`. – NuclearPeon Dec 11 '15 at 09:04

score 13 · Answer 2 · answered Jun 09 '10 at 11:21

13

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;

Postgresql reference

answered Jun 09 '10 at 11:21

Anonymous Coward

856
1
11
27

Tregoreg · Answer 3 · 2016-03-26T17:46:25.127

13

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;

edited Mar 26 '16 at 17:46

answered Mar 26 '16 at 17:35

Tregoreg

18,872
15
48
69

score 2 · Answer 4 · answered Apr 25 '17 at 14:58

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;

Postgresql constraint

4 Answers4

Linked