Two COLUMNs need to be UNIQUE if the second isn't NULL.

Hi,

Brian Tkatch wrote:

Two COLUMNs need to be UNIQUE if the second isn't NULL. If the second is NULL, the first does not need to be UNIQUE.

 

CREATE TABLE Moo
(
 Prima        INT PRIMARY KEY,
 Secunda    INT NOT NULL,
 Tertia        VARCHAR2(1)
);
CREATE INDEX Cow ON Moo(Secunda, NVL(Tertia, TO_CHAR(Prima)));
INSERT INTO Moo(Prima, Secunda, Tertia)
SELECT 1, 1, NULL FROM Dual UNION ALL
SELECT 2, 1, NULL FROM Dual;
DROP TABLE Moo;

Is there another way to do it?
 

Sorry, it's unclear what you want to do. It would help if you posted a few examples of INSERT statements, some of which should work, and some of which should fail because they violate the uniqueness requirements.  Identify which statements should work, and which should fail.

The index you created won't enforce uniqueness; only a UNIQUE index or constraint will do that.

Perhaps a function-based index will do what you want.  For example:

CREATE UNIQUE INDEX moo_unique
ON moo ( NVL2 (secunda, prima,  NULL)
       , NVL2 (secunda, tertia, NULL)
       );

The effect of the index above is to have a unique index on (prima, tertia) that only applies to rows where secunda is not NULL.

Hi,

Brian Tkatch wrote:

Frank, thanx. I went through a few edits and ended up mistakenly dropping the UNIQUE keyword. I added it back to the script.

A case where a record will fail, is to use a CONSTRAINT instead of an INDEX:

CREATE TABLE Moo 
( 
Prima        INT PRIMARY KEY, 
Secunda    INT NOT NULL, 
Tertia        VARCHAR2(1),
UNIQUE(Secunda, Tertia)
); 
INSERT INTO Moo(Prima, Secunda, Tertia) 
SELECT 1, 1, NULL FROM Dual UNION ALL 
SELECT 2, 1, NULL FROM Dual; 
DROP TABLE Moo; 

Sorry, I didn't read this carefully at first.

So, prima is the primary key, but it doesn;'t really play any role in this problem.

Secunda is declared as NOT NULL, so there's no point in talking about cases where secunda is NULL.

If the unique constraint that you posted does what you want, then use it.  I can't see any better way to get the same results.

In general, don't (explicitly) use an index when you can use a constraint.  And don't use a trigger when you can use anything else.