## Forum Stats

• 3,728,256 Users
• 2,245,586 Discussions

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

Member Posts: 337
edited April 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?

• Member Posts: 9,583 Gold Crown
edited April 2014 Accepted Answer

I had a bit of trouble matching your description to your example, but if you want to enforce uniqueness on (secunda, tertia) only for rows where tertia is not null then the following should work:

create unique index xxx on moo(

case when tertia is not null then secunda end,

tertia

);

Regards

Jonathan Lewis

P.S.  When doing a quick test of how best to define this index, I found what seems to be a bug in 11.2.0.4 (maybe other versions) with the expression:

case tertia when null then cast(null as int) else secunda end

Update: now blogged at: Easy &amp;#8211; Oops. | Oracle Scratchpad

• Member, Moderator Posts: 40,316 Red Diamond
edited April 2014

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.

```
1. CREATE TABLE Moo
2. (
3. Prima        INT PRIMARY KEY,
4. Secunda    INT NOT NULL,
5. Tertia        VARCHAR2(1)
6. );
7. CREATE INDEX Cow ON Moo(Secunda, NVL(Tertia, TO_CHAR(Prima)));
8. INSERT INTO Moo(Prima, Secunda, Tertia)
9. SELECT 1, 1, NULL FROM Dual UNION ALL
10. SELECT 2, 1, NULL FROM Dual;
11. DROP TABLE Moo;
```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.

• Member Posts: 337
edited April 2014

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;

```
```INSERT INTO Moo(Prima, Secunda, Tertia)
*
ERROR at line 1:
ORA-00001: unique constraint (BTKATCH2.SYS_C0086823) violate
```
• Member, Moderator Posts: 40,316 Red Diamond

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:

```
1. CREATE TABLE Moo
2. (
3. Prima        INT PRIMARY KEY,
4. Secunda    INT NOT NULL,
5. Tertia        VARCHAR2(1),
6. UNIQUE(Secunda, Tertia)
7. );
8. INSERT INTO Moo(Prima, Secunda, Tertia)
9. SELECT 1, 1, NULL FROM Dual UNION ALL
10. SELECT 2, 1, NULL FROM Dual;
11. DROP TABLE Moo;
```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.

• Member Posts: 9,583 Gold Crown
edited April 2014 Accepted Answer

I had a bit of trouble matching your description to your example, but if you want to enforce uniqueness on (secunda, tertia) only for rows where tertia is not null then the following should work:

create unique index xxx on moo(

case when tertia is not null then secunda end,

tertia

);

Regards

Jonathan Lewis

P.S.  When doing a quick test of how best to define this index, I found what seems to be a bug in 11.2.0.4 (maybe other versions) with the expression:

case tertia when null then cast(null as int) else secunda end

Update: now blogged at: Easy &amp;#8211; Oops. | Oracle Scratchpad

• Member Posts: 337

> if you want to enforce uniqueness on (secunda, tertia) only for rows where tertia is not null

Yes, exactly. I see that being concise was not better in this case.

And that is a better way to do it as it does not require the PK. Took a second look to realize how it worked, but its perfect. Thanx!

This discussion has been closed.