CODEWITHSUNDEEP
sqltriggersdb2
rickyalbert
rickyalbert

Reputation: 2652

Replacing Unique Constraint with Trigger

I need to replace a unique constraint with a trigger, but I found a problem (I'm using DB2). Let's say I have a table R with a attribute A integer. I defined this trigger (similar for update):

CREATE TRIGGER R_A_UNIQUE
BEFORE INSERT ON R
REFERENCING NEW AS N
FOR EACH ROW
WHEN (EXISTS(SELECT * FROM R WHERE A = N.A))
SIGNAL SQLSTATE '70000' ('ATTRIBUTE A MUST BE UNIQUE');

It works in normal cases, but if add multiple rows like that:

INSERT INTO R VALUES (4),(4),(4);

all values are inserted with no problem!

Am I missing something? Thank you very much

Upvotes: 4

Views: 2359

Answers (2)

BateTech
BateTech

Reputation: 6496

You stated in comments above that the reason for using a trigger instead of unique constraint is that "In DB2 you can't define an attribute as unique if it allows null values".

According to what I have read, DB2 will allow a "unique index" on Nullable columns, but will not allow "unique constraints" on nullable columns.

Another issue is that DB2 by default will only allow one NULL value within a unique index on a single column, and will treat 2 NULL values as a violation of the unique index.

You can work around this issue, and still use a unique index, by using Generated columns for the columns that allow NULLs, using the pattern mentioned in this article (https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/unique_where_not_null_indexes26?lang=en), which states

What is needed is an extra column that makes all NULL keys unique but does not interfere with uniqueness enforcement if there is any NULL column in the key

Steps:

1) Alter table R to add a generated column which will make the NULL values be treated as unique within the index.

ALTER TABLE R 
ADD A_UNQKEY INT GENERATED ALWAYS AS (CASE WHEN A IS NULL THEN pk ELSE NULL END))

...where pk is the primay key of table r. Also note that you may need to change the data type from INT to whatever type your primary key is.

2) Create the unique index on both a and a_unqkey columns.

CREATE UNIQUE INDEX UIX_R_A ON R(a, a_unqkey);

So now, an example of values in table R:

pk, a, a_unqkey
1, NULL, 1
2, 'value1', NULL
3, 'value2', NULL
4, NULL, 4

Without the a_unqkey field, your unique index on column a would look like this: 

NULL
'value1'
'value2'
NULL   <<**VIOLATES UNIQUE INDEX b/c DUPLICATE OF NULL FROM ROW 1**

However, if you create the unique index on R(a, a_unqkey), then it would use the following values: 

NULL, 1
'value1', NULL
'value2', NULL
NULL, 4  <<Still unique b/c the other pair is NULL, 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

This is too long for a comment.

The insert is a single transaction. So, the state of the table does not change during the course of the inserts. That is, the trigger is called but on the original version of the table, all three times.

This makes it difficult to implement something like a unique constraint inside a trigger, and one that can handle multiple inserts at the same time. My advice is to stick with the unique constraint and not try to reproduce it in a trigger.

I should add that you might be able to change the semantics of the trigger by using an "after insert" trigger. However, this would allow the duplicates into the table, and then you would have to delete them afterwards.

Upvotes: 2

Related Questions