Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PostgreSQL Deferrable Constraints: Unique, Primary Key, Foreign Key, and Exclude

PostgreSQL Deferrable Constraints: Unique, Primary Key, Foreign Key, and Exclude

In this article, the author explains various deferrable constraints in PostgreSQL, including unique, primary key, foreign key, and exclude.

By digoal

Background

Assume that a table has constraints and understand when these constraints are detected. Is it at the end of the statement or during the insert tuple? Can you defer the constraint detection? Can you defer it until the end of the statement or transaction?

It will be very inconvenient to import data if you cannot defer the detection. You must first transfer the dependent data and then transfer the relevant data. If both the data types are interdependent, it will be even more difficult to import the data.

To solve the constraint- and dependency-related problem, PostgreSQL usually adopts the following methods:

1) When data is imported, constraints are usually created after all the data is written. Therefore, you can avoid dumping failures after creating constraints.

2) PostgreSQL allows the deferred detection of constraints.

- Deferred constraint judgment is allowed. You can specify this feature when creating tables or constraints and modify them later.

- Set rules for deferred judgment to determine the constraint at the end of a statement or a transaction. You can set rules by modifying the constraint definition or set directly in the transaction.

Syntax reference: https://www.postgresql.org/docs/12/sql-createtable.html

Whether deferred constraint judgment is allowed:

  • DEFERRABLE
  • NOT DEFERRABLE

This controls whether you can defer the constraint. A constraint that is not deferrable will be checked immediately after every command. Checking of deferrable constraints can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. Note that you cannot use deferrable constraints as conflict arbitrators in an INSERT statement that includes an ON CONFLICT DO UPDATE clause.

Referred judgment rules: End of statement or transaction

  • INITIALLY IMMEDIATE
  • INITIALLY DEFERRED

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement, which is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. You can alter the constraint check time with the SET CONSTRAINTS command.

Set rules for deferred constraint judgment in transactions: https://www.postgresql.org/docs/12/sql-set-constraints.html

SET CONSTRAINTS { ALL | name [, ...]} { DEFERRED | IMMEDIATE }

Examples

1) Deferred constraint judgment is not allowed.

postgres=# create table pr(id int primary key, c1 int);  
CREATE TABLE  
postgres=# create table fp(id int references pr(id), c1 int);  
CREATE TABLE  
  
  
postgres=# insert into fp values(1,1);  
ERROR:  insert or update on table "fp" violates foreign key constraint "fp_id_fkey"  
DETAIL:  Key (id)=(1) is not present in table "pr".

2) Allow the deferred constraint judgment and set rules for deferral to end the transaction.

create table fp(id int references pr(id) INITIALLY DEFERRED, c1 int);  
  
postgres=# begin;  
BEGIN  
postgres=# insert into fp values(1,1);  
INSERT 0 1

3) Deferred judgment is allowed. Set the transaction after starting the transaction to finish the deferred judgement.

postgres=# alter table fp alter CONSTRAINT fp_id_fkey deferrable;  
ALTER TABLE  
postgres=# begin;  
BEGIN  
postgres=# insert into fp values(1,1);  
ERROR:  insert or update on table "fp" violates foreign key constraint "fp_id_fkey"  
DETAIL:  Key (id)=(1) is not present in table "pr".  
postgres=# rollback;  
ROLLBACK  
  
postgres=# begin;  
BEGIN  
  
设置事务结束延判, 所有约束生效  
  
postgres=# set constraints all  deferred;  
SET CONSTRAINTS  
postgres=# insert into fp values(1,1);  
INSERT 0 1  
postgres=# rollback;  
ROLLBACK

References

PostgreSQL Database Deferrable Constraint Unique Primary Key Foreign Key
0 0 0
Share on

Read previous post:

How to Mask Sensitive Information on PostgreSQL Using Anon Plug-in

Read next post:

PostgreSQL Time Series Database: How TimescaleDB Supports Compression Scheduling

digoal

281 posts | 24 followers

You may also like

Comments

digoal

281 posts | 24 followers

Related Products

More Posts by digoal

See All