Thread: 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