THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

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.test
SET    id = 3 - id;

-- new IDs would be unique at the end of the statement
SELECT 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_test
PRIMARY KEY(ID) DEFERRABLE INITIALLY IMMEDIATE;

-- now this update succeeds
UPDATE public.test
SET    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_test
PRIMARY 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.Orders
ADD 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.

Published Friday, November 15, 2013 12:33 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement