Cannot be since you are allowed to have the same companynumber several times for type 2 - but you are right in refering to 'id' - in fact the design is faulty. Quite obviously the 'type' is an attribute of the company, not of the employee. There is no way but a trigger to check it, but it should belong to another table - the Company table. Which would solve most of the problems I have pointed to.
SF > >Sounds like 'Companynumber' and 'Type' should be >either a compound Primary >Key, or a unique index. Which begs the question of >what 'Id' is doing - >perhaps you don't need it. > >peter > > >-----Original Message----- >Sent: Friday, August 29, 2003 10:39 AM >To: Multiple recipients of list ORACLE-L > > >Hallo, > >I would like to gets ome help on this: > >I have this table > >Id Companynumber First Name LastName >Type > >1 123 Jim Andersson 1 >2 234 Tom Perkins > 2 >3 565 Henry Ford 1 > > >I would like to have a constraint so when I try to >add and the same >companynumber that already exists(for instance 234) > >I am not allowed to do this if the filed Type has >the value 1. >So if I add a companynumber which already exists >and the Type is 2, then I >should be allowed to add this companynumber. > >Maybe this is simple but woul dreally appreciate >some help onhow to write >this. > >Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).