Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Brian TkatchApr 2 2014 — edited Apr 2 2014

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 UNIQUE 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?

Added "UNIQUE"

This post has been answered by Jonathan Lewis on Apr 2 2014
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 30 2014
Added on Apr 2 2014
5 comments
7,750 views