Обсуждение: tricky CHECK condition

How could I specify a condition in 'CHECK' constraint for a column to
check that its value must only appear once in *some* rows of a table?
Sort of a local "UNIQUE"... For example, in CDs world:

create table tracks (
       -- Unique track identifier
       id serial primary key,
       -- Which disk the track is from
       disk_id int4 references disks (id),
       -- Track number in a disk
       track_number int2,
       check (WHAT I WANT GOES HERE) );

I want to check values of `track_number' to be unique for the
disk. Basically, I want to check that an SQL query

select track_number from tracks T
       where T.disk_id=disk_id and T.track_number=track_number;

is empty... Is it possible to run an SQL query in a condition clause?
I looked thru documentation, but couldn't find anything relevant.
--
Arcady Genkin                                 http://www.thpoon.com
Nostalgia isn't what it used to be.

Multi-column unique index?


> How could I specify a condition in 'CHECK' constraint for a column to
> check that its value must only appear once in *some* rows of a table?
> Sort of a local "UNIQUE"... For example, in CDs world:
>
> create table tracks (
>        -- Unique track identifier
>        id serial primary key,
>        -- Which disk the track is from
>        disk_id int4 references disks (id),
>        -- Track number in a disk
>        track_number int2,
>        check (WHAT I WANT GOES HERE) );
>
> I want to check values of `track_number' to be unique for the
> disk. Basically, I want to check that an SQL query
>
> select track_number from tracks T
>        where T.disk_id=disk_id and T.track_number=track_number;
>
> is empty... Is it possible to run an SQL query in a condition clause?
> I looked thru documentation, but couldn't find anything relevant.
> --
> Arcady Genkin                                 http://www.thpoon.com
> Nostalgia isn't what it used to be.
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> Multi-column unique index?

Excellent! Thank you very much!
--
Arcady Genkin
Nostalgia isn't what it used to be.

Arcady Genkin <a.genkin@utoronto.ca> writes:
> I want to check values of `track_number' to be unique for the
> disk.

Not sure if it can be done with a CHECK condition, but the traditional
solution is to create a unique index on the two columns:

create unique index tracks_disk_track_i on tracks(disk_id, track_number);

Updating an index should be a lot faster than running a whole subquery
for each insert, and the planner may be able to use the index to speed
up other queries too.  So, it's a win all round...

            regards, tom lane