CODEWITHSUNDEEP
sqlpostgresqlforeign-keys
clue3434
clue3434

Reputation: 235

PostgreSQL SET CONSTRAINTS ALL DEFERRED not working

I am creating a lot of tables in one PostgreSQL script that has foreign keys in the tables but the way I need to set up my files requires to do a transaction and defer the foreign keys during the transaction but it is not working. Below is an example of what my code essentially is but it still gives me the error that "secondTable" does not exist even though the constraints should be deferred.

Putting the secondTable first fixes it of course but I can not do this for my real script because of other reasons. What could be the issue here?

START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
CREATE TABLE firstTABLE(
attribute1 INT NOT NULL references secondTable(attribute3), 
attribute2 INT
);
CREATE TABLE secondTable(
attribute3 INT PRIMARY KEY NOT NULL,
attribute4 varchar(50)
);
COMMIT;

Upvotes: 0

Views: 2353

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I think you are confusing the "compilation" of the code and the execution. Deferred constraints are -- indeed -- not checked until a transaction is committed (or the constraint type is changed).

However, the constraint still needs to exist in the database. This is the error -- Postgres cannot store the constraint because the referring table doesn't even exist. You want the table to exist, even if it is empty, initially.

Upvotes: 1

Related Questions