Drop Foreign Keys Generally in Postgres

Drop foreign keys generally in POSTGRES

Loop it in DO statement, like:

b=# create table a (a int primary key, b int unique);
CREATE TABLE
b=# create table b (a int references a(a), b int references a(b));
CREATE TABLE
b=# do
$$
declare r record;
begin
for r in (select constraint_name from information_schema.table_constraints where table_schema = 'public' and table_name='b') loop
  raise info '%','dropping '||r.constraint_name;
  execute CONCAT('ALTER TABLE "public"."b" DROP CONSTRAINT '||r.constraint_name);
end loop;
end;
$$
;
INFO:  dropping b_a_fkey
INFO:  dropping b_b_fkey
DO

How to drop multiple tables having foreign keys in PostgreSQL?

You can tell Postgres to automatically drop all foreign keys referencing those tables by using the cascade keyword:

DROP TABLE foo, bar, baz CASCADE;

Delete rows with foreign key in PostgreSQL

To automate this, you could define the foreign key constraint with ON DELETE CASCADE.

I quote the the manual for foreign key constraints:

CASCADE specifies that when a referenced row is deleted, row(s)
referencing it should be automatically deleted as well.

Look up the current FK definition like this:

SELECT pg_get_constraintdef(oid) AS constraint_def
FROM   pg_constraint
WHERE  conrelid = 'public.kontakty'::regclass  -- assuming public schema
AND    conname = 'kontakty_ibfk_1';

Then add or modify the ON DELETE ... part to ON DELETE CASCADE (preserving everything else as is) in a statement like:

ALTER TABLE kontakty
   DROP CONSTRAINT kontakty_ibfk_1
 , ADD  CONSTRAINT kontakty_ibfk_1
   FOREIGN KEY (id_osoby) REFERENCES osoby (id_osoby) ON DELETE CASCADE;

There is no ALTER CONSTRAINT command. Drop and recreate the constraint in a single ALTER TABLE statement to avoid possible race conditions with concurrent write access.

You need the privileges to do so, obviously. The operation takes an ACCESS EXCLUSIVE lock on table kontakty and a SHARE ROW EXCLUSIVE lock on table osoby.

If you can't ALTER the table, then deleting by hand (once) or by trigger BEFORE DELETE (every time) are the remaining options.

Postgres Drop constraints order

No constraint on a PostgreSQL table depends on another constraint on the same table, so the order does not matter here.

The only dependency between constraints is the dependency of a foreign key on the primary or unique key on the target table.

So you can either remove all foreign key constraints first and then all other constraints, or you can use ALTER TABLE ... DROP CONSTRAINT ... CASCADE which will automatically drop all dependent constraints, then you don't have to care about the order at all.

Can FOREIGN KEY be omitted in PostgreSQL when using REFERENCES?

The two samples you show do the same thing, just with a different syntax.

The first method is called table constraint, the second column constraint, but the latter name is somewhat misleading because the constraint is on the table as well.

The main difference is that the column constraint syntax is shorter, but cannot be used for all constraints: if you have for example a primary key that contains two columns, you have to write it in the table constraint syntax.

How to drop foreign keys of a particular column

If you want to get more information about FK and specifically about a particular scheme and table than you can use.

 SELECT 
    t.Name as TableName,
    c.name as ColumnName, 
    fk.name as FK_NAME 

 FROM sys.foreign_keys as fk
 inner join sys.tables as t on fk.parent_object_id = t.object_id
 inner join sys.columns as c on c.object_id = t.object_id
 inner join sys.schemas as sc on t.schema_id = sc.schema_id
 WHERE sc.name = 'Schema' and t.name = 'Table' and c.name = 'Column'

If you are interested only about certain column then u can use Ross Presser answer.

Also if you want to drop all fk constraint you can execute this:

  Declare @sql  nvarchar(4000)
  SET @sql = N'';

  SELECT @sql = @sql + '
  ALTER TABLE [' + sc.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP  CONSTRAINT ' + '[' + fk.NAME + ']
   '
  FROM sys.foreign_keys as fk
  inner join sys.tables as t on fk.parent_object_id = t.object_id
  inner join sys.columns as c on c.object_id = t.object_id
  inner join sys.schemas as sc on t.schema_id = sc.schema_id
  WHERE sc.name = 'schemaName'  and c.name = 'columnName' -- you can include and fk name
  ORDER BY fk.NAME
  PRINT @sql;
--EXEC sys.sp_executesql @sql; 

CASCADE behaviour on the drop of a foreign key

The cascade option to drop a constraint is only needed when dropping primary keys, not when dropping a foreign key.

Consider this example in Postgres:

create table t1 (id integer, constraint pk_one primary key (id));
create table t2 (id integer primary key, id1 integer references t1);

When you try to run:

alter table t1 drop constraint pk_one;

You get:

ERROR: cannot drop constraint pk_one on table t1 because other objects depend on it
  Detail: constraint t2_id1_fkey on table t2 depends on index pk_one
  Hint: Use DROP ... CASCADE to drop the dependent objects too. 

If you run:

alter table t1 drop constraint pk_one cascade;

you get:

NOTICE:  drop cascades to constraint t2_id1_fkey on table t2

Telling you that the foreign key that needed the primary key was dropped as well.

---

Note that not all DBMS support a cascading drop. Postgres and Oracle do.

MySQL, SQL Server or Firebird do not. You need to drop the foreign keys manually in those DBMS.

---

Related Topics