Learning PostgreSql: differences in implementation of constraints

Constraints in PostgreSql are implemented somewhat differently. To use them efficiently, there are quite a few details we need to be aware of.

NULLs and uniqueness

In PostgreSql, unique constraints allow multiple NULLs. This behavior is ANSI standard. SQL Server's implementation of unique constraints is not ANSI standard. 

The following example demonstrates how unique constraints allow multiple NULLs: The insert

CREATE TABLE public.test(ID INTEGER NOT NULL,CONSTRAINT PK_test PRIMARY KEY(ID),SomeValue INTEGER NULL,CONSTRAINT UNQ_test UNIQUE(SomeValue)
)
INSERT INTO public.test(ID, SomeValue)VALUES(1,NULL),(2,NULL);

The insert works on PostgreSql. It fails on SQL Server. This can be a breaking change.

Uniqueness and multi-row updates

By default, PostgreSql verifies uniqueness after every row. As a result, the following statement fails, even though IDs would be unique at the end of the statement:

UPDATE public.testSET    id = 3 - id;
-- new IDs would be unique at the end of the statementSELECT 3 - ID FROM public.test;2
1

This is not ANSI standard, and this is a breaking change - in this case SQL Server behaves exactly as specified in ANSI standard, verifying uniqueness at the end of the statement.

It is possible to create a PRIMARY KEY constraint that behaves as specified in ANSI standard:

ALTER TABLE public.test DROP CONSTRAINT PK_test;
ALTER TABLE public.test ADD CONSTRAINT PK_testPRIMARY KEY(ID) DEFERRABLE INITIALLY IMMEDIATE;
-- now this update succeedsUPDATE public.testSET    id = 3 - id;

By default UNIQUE constraints behave exactly as PRIMARY KEY ones - they verify uniqueness one row at a time. If that is a problem, we can fix it in exactly the same way.

The difference between DEFERRABLE INITIALLY IMMEDIATE and DEFERRABLE INITIALLY DEFERRED

As we have already seen, DEFERRABLE INITIALLY DEFERRED constraints verify at the end of statement. As such, the following transaction fails:

UPDATE public.test SET SomeValue=ID;BEGIN TRANSACTION;
UPDATE public.test SET ID=2 WHERE SomeValue=1;UPDATE public.test SET ID=1 WHERE SomeValue=2;
COMMIT;

DEFERRABLE INITIALLY DEFERRED constraints verify at the end of transaction. Let us re-create our PRIMARY KEY:

ALTER TABLE public.test DROP CONSTRAINT PK_test;
ALTER TABLE public.test ADD CONSTRAINT PK_testPRIMARY KEY(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE public.test DROP CONSTRAINT UNQ_test;

That done, we can rerun the transaction, and it will succeed. This is a great feature - it allows us to change from one valid state to another valid state in more than one DML command. In other words, we have less need to write complex monster modifications - instead, we can write several simpler, easier to understand ones.

Using deferrable constraints

Deferrable constraints allow for simple solutions for some very common problems. For example, we can easily guarantee that every Order in our system has OrderItems. This is a very common requirement, and we can easily implement it:

CREATE TABLE public.Orders(order_id INT NOT NULL, has_order_item_number INT NOT NULL,some_data VARCHAR,CONSTRAINT PK_orders PRIMARY KEY(order_id)
);
CREATE TABLE public.Order_Items(order_id INT NOT NULL, CONSTRAINT FK_order_items_orders 
  FOREIGN KEY(order_id)
  REFERENCES public.orders(order_id),item_number INT NOT NULL,some_data VARCHAR,CONSTRAINT PK_order_items PRIMARY KEY(order_id, item_number)
);
ALTER TABLE public.OrdersADD CONSTRAINT FK_orders_order_items 
  FOREIGN KEY(order_id, has_order_item_number)
  REFERENCES public.order_items(order_id, item_number)
  DEFERRABLE; 

Let us run a short test:

BEGIN TRANSACTION;
SET CONSTRAINTS FK_orders_order_items DEFERRED;
INSERT INTO public.Orders(order_id, has_order_item_number, some_data)VALUES(1, 1, 'Some order data');
INSERT INTO public.Order_Items(order_id, item_number, some_data)VALUES(1, 1, 'Some order item data');
COMMIT;

As we have seen, deferrable constraints may be very useful.