MacLochlainns Weblog

You can’t disable a foreign key constraint in Postgres, like you can do in Oracle. However, you can remove the foreign key constraint from a column and then re-add it to the column.

Here’s a quick test case in five steps:

1. Drop the big and little table if they exists. The first drop statement requires a cascade because there is a dependent little table that holds a foreign key constraint against the primary key column of the big table. The second drop statement does not require the cascade keyword because there is not a dependent foreign key constraint.

   DROP TABLE IF EXISTS big CASCADE; DROP TABLE IF EXISTS little;

   DROP TABLE IF EXISTS big CASCADE; DROP TABLE IF EXISTS little;

2. Create the big and little tables:

   -- Create the big table. CREATE TABLE big ( big_id SERIAL , big_text VARCHAR(20) NOT NULL , CONSTRAINT pk_little_1 PRIMARY KEY (big_id));   -- Display the big table. \d big   -- Create little table. CREATE TABLE little ( little_id SERIAL , big_id INTEGER NOT NULL , little_text VARCHAR(20) NOT NULL , CONSTRAINT fk_little_1 FOREIGN KEY (big_id) REFERENCES big (big_id));   -- Display the little table. \d little

   -- Create the big table. CREATE TABLE big ( big_id SERIAL , big_text VARCHAR(20) NOT NULL , CONSTRAINT pk_little_1 PRIMARY KEY (big_id)); -- Display the big table. \d big -- Create little table. CREATE TABLE little ( little_id SERIAL , big_id INTEGER NOT NULL , little_text VARCHAR(20) NOT NULL , CONSTRAINT fk_little_1 FOREIGN KEY (big_id) REFERENCES big (big_id)); -- Display the little table. \d little

   If you failed to designate the big_id column as a primary key constrained, Postgres will raise the following exception:

   ERROR: there IS no UNIQUE CONSTRAINT matching given KEYS FOR referenced TABLE "big"

   ERROR: there is no unique constraint matching given keys for referenced table "big"

3. Insert a non-compliant row in the little table. An insert statement into the little table with a value for the foreign key column that does not exist in the big_id column of the big table would fail with the following error:

   ERROR: INSERT OR UPDATE ON TABLE "little" violates FOREIGN KEY CONSTRAINT "fk_little_1" DETAIL: KEY (big_id)=(2) IS NOT present IN TABLE "big".

   ERROR: insert or update on table "little" violates foreign key constraint "fk_little_1" DETAIL: Key (big_id)=(2) is not present in table "big".

   Re-enabling the foreign key constraint, the insert statement succeeds after you first insert a new row into the big table with the foreign key value for the little table as its primary key. The following two insert statements add a row to both the big and little table:

   -- Insert into a big table. INSERT INTO big (big_text) VALUES ('Cat in the Hat 2');   -- Insert into a little table. INSERT INTO little (big_id ,little_text) VALUES ( 2 ,'Thing 3');

   -- Insert into a big table. INSERT INTO big (big_text) VALUES ('Cat in the Hat 2'); -- Insert into a little table. INSERT INTO little (big_id ,little_text) VALUES ( 2 ,'Thing 3');

   Then, you can query it like this:

   SELECT * FROM big b JOIN little l ON b.big_id = l.big_id;

   SELECT * FROM big b JOIN little l ON b.big_id = l.big_id;

   big_id | big_text | little_id | big_id | little_text --------+------------------+-----------+--------+------------- 1 | Cat IN the Hat 1 | 1 | 1 | Thing 1 1 | Cat IN the Hat 1 | 2 | 1 | Thing 2 2 | Cat IN the Hat 2 | 3 | 2 | Thing 3 (3 ROWS)

   big_id | big_text | little_id | big_id | little_text --------+------------------+-----------+--------+------------- 1 | Cat in the Hat 1 | 1 | 1 | Thing 1 1 | Cat in the Hat 1 | 2 | 1 | Thing 2 2 | Cat in the Hat 2 | 3 | 2 | Thing 3 (3 rows)

4. You can drop a foreign key constraint with the following syntax:

   ALTER TABLE little DROP CONSTRAINT fk_little_1;

   ALTER TABLE little DROP CONSTRAINT fk_little_1;

5. You can add a foreign key constraint with the following syntax:

   ALTER TABLE little ADD CONSTRAINT fk_little_1 FOREIGN KEY (big_id) REFERENCES big (big_id);

   ALTER TABLE little ADD CONSTRAINT fk_little_1 FOREIGN KEY (big_id) REFERENCES big (big_id);

As always, I hope this helps you solve problems.