RE: RE: Question about Constraint

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: sfaroult_at_oriolecorp.com

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: ListGuru_at_fatcity.com (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).