| Subject | RE: [firebird-support] unique constraint on a pair, one NOT NULL, one NULL |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2009-05-06T06:52:52Z |
If the constraint could include uniqueness for the combination TEXTID, ENDDATE even if ENDDATE wasn't NULL, then you could probably make your own workaround - even in InterBase:
Add another unique column, e.g. ENDDATENOTNULL, that you populate using BEFORE triggers containing code like:
new.ENDDATENOTNULL = coalesce(new.ENDDATE, '01.01.1900')
(not knowing whether InterBase supports coalesce, if not it is a simple IF statement - I assume no records in your database will ever have 01.01.1900 as their enddate)
ENDDATENOTNULL would then never be NULL and you could create your constraint on TEXTID, ENDDATENOTNULL.
HTH,
Set
-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of car.toper
Sent: 5. mai 2009 21:42
To: firebird-support@yahoogroups.com
Subject: [firebird-support] unique constraint on a pair, one NOT NULL, one NULL
I have a number of tables in which there are two fields:
TEXTID CHAR(10) NOT NULL,
ENDDATE TIMESTAMP NULL
I need to put a unique constraint on the system so that there can only be one row with a given TEXTID and have an ENDDATE of NULL. Interbase 7.5 is not allowing me to create such a contraint. It is possible or must it be done in triggers?
Add another unique column, e.g. ENDDATENOTNULL, that you populate using BEFORE triggers containing code like:
new.ENDDATENOTNULL = coalesce(new.ENDDATE, '01.01.1900')
(not knowing whether InterBase supports coalesce, if not it is a simple IF statement - I assume no records in your database will ever have 01.01.1900 as their enddate)
ENDDATENOTNULL would then never be NULL and you could create your constraint on TEXTID, ENDDATENOTNULL.
HTH,
Set
-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of car.toper
Sent: 5. mai 2009 21:42
To: firebird-support@yahoogroups.com
Subject: [firebird-support] unique constraint on a pair, one NOT NULL, one NULL
I have a number of tables in which there are two fields:
TEXTID CHAR(10) NOT NULL,
ENDDATE TIMESTAMP NULL
I need to put a unique constraint on the system so that there can only be one row with a given TEXTID and have an ENDDATE of NULL. Interbase 7.5 is not allowing me to create such a contraint. It is possible or must it be done in triggers?