CODEWITHSUNDEEP
postgresql
Anu
Anu

Reputation: 51

How to alter a foreign key in postgresql

I created a table in PostgreSQL with a foreign key constraint.

I dropped the table to which the foreign key belongs. Now how to alter the table or how to defer the foreign key present in the table?

To clarify:

I have a table named test. It has a column called subjectName, which is a foreign key of subject Table. Now I dropped subject table. How to remove the FK constaint on table test

Upvotes: 0

Views: 1070

Answers (2)

Belayer
Belayer

Reputation: 14861

Perhaps your question in not exactly what you mean. Are you wanting to remove the which was a foreign key from the table. As amply indicated if you dropped the parent table then the FK is also dropped. However the column itself is not dropped from the child table. To remove that you need to alter the table.

alter table test drop column subject_name;

See demo here

Upvotes: 0

user330315
user330315

Reputation:

Assuming the following tables:

create table subject
(
  name varchar(10) primary key
);

create table test
(
  some_column integer,
  subject_name varchar(10) not null references subject
);

there are two scenarios what could have happened when you dropped the table subject:

1. you didn't actually drop it:

drop table subject;

ERROR: cannot drop table subject because other objects depend on it
  Detail: constraint test_subject_name_fkey on table test depends on table subject
  Hint: Use DROP ... CASCADE to drop the dependent objects too.

2. you did drop it, then the foreign key is gone as well.

drop table subject cascade;
NOTICE:  drop cascades to constraint test_subject_name_fkey on table test

which tells you that the foreign key constraint was automatically dropped.

Upvotes: 1

Related Questions