PostgreSQL – Recreate All Foreign Keys as Deferrable in Batch

ddlforeign keypostgresql

I want to make all foreign keys in my database deferrable. But there is no possibility to alter an existing constraint. So I need to drop and add again each foreign key. How to do it automatically?

Best Answer

You can do this by generating the necessary script.

select 'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||
       ' drop constraint '||quote_ident(conname)||';'||chr(10)||
       'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||
       ' add constraint '||quote_ident(conname)||' '||
       pg_get_constraintdef(c.oid, true)||' deferrable initially immediate;' as ddl
from pg_constraint c
  join pg_class tb on tb.oid = c.conrelid
  join pg_namespace ns on ns.oid = tb.relnamespace
where ns.nspname in ('public')   --<<< adjust the schema name(s) here
 and c.contype = 'f';

Spool the output of the above statement into a file and then run that generated script.

Related Solutions

Sql-server – Allow self-referential foreign keys to be null from an alter-table

If there are no illegal values in C.ParentID, then I am not clear what the problem might be. Is this Microsoft SQL Server?

I created a test case as follows:

Create Table dbo.Hier
(Id  INT IDENTITY Primary KEy,
 ParID INT NULL,
 Other varchar(10));
GO
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Able');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (1, 'Baker');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Charlie');
GO
ALTER TABLE dbo.Hier ADD 
      CONSTRAINT FK_Parent
      FOREIGN    KEY (ParID)
      REFERENCES Hier(ID);
GO
INSERT INTO dbo.Hier (ParID, Other) VALUES (3, 'Delta');
SELECT * FROM dbo.Hier;
GO
DROP TABLE dbo.Hier;
GO

Seems to work fine. If you are on a different SQL Server it might have different behavior. Or fluentMigrator may have a problem.

Foreign Keys – Primary Key Not Clustered Index

Taken from the MSDN article on FOREIGN KEY Constraints

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

I think it is perfectly fine to define a UNIQUE CLUSTERED INDEX on an IDENTITY column and define the PRIMARY KEY on different columns, often with a NONCLUSTERED INDEX but you don't have to specify an index.

Best Answer

Related Solutions

Sql-server – Allow self-referential foreign keys to be null from an alter-table

Foreign Keys – Primary Key Not Clustered Index

Related Question