Thread

1. tricky CHECK condition

   Arcady Genkin <a.genkin@utoronto.ca> — 2000-05-23T04:25:58Z

   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.
   
   

2. Re: tricky CHECK condition

   Bruce Momjian <pgman@candle.pha.pa.us> — 2000-05-23T04:35:54Z

   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
   
   

3. Re: tricky CHECK condition

   Arcady Genkin <a.genkin@utoronto.ca> — 2000-05-23T04:52:40Z

   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.
   
   

4. Re: tricky CHECK condition

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-23T05:11:39Z

   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