CODEWITHSUNDEEP
postgresqlforeign-keys
Ryan
Ryan

Reputation: 583

Postgresql constraint

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;

Upvotes: 43

Views: 48741

Answers (4)

Gerardo Cortés Mendoza
Gerardo Cortés Mendoza

Reputation: 21

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;

Upvotes: 2

Tregoreg
Tregoreg

Reputation: 22296

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;

Upvotes: 13

Magnus Hagander
Magnus Hagander

Reputation: 25168

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;

Upvotes: 81

Anonymous Coward
Anonymous Coward

Reputation: 886

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

Upvotes: 13

Related Questions