RE: Question about Constraint

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

You can't do that through declarative constraints and you'll have to resort to a trigger to implement it - a pre-insert row trigger which does a 'select type ... where CompanyNumber = :new.companynumber and rownum=1'. Quite obviously (????) for performance reasons you should have an index (albeit a non-unique one) on (companynumber, type). If you have found a type and it's one, raise an exception. You have quite a number of hidden traps though :

• I presume that during your insert you are inserting a type as well (unless you always use a default value ?). What happens if the inserted type contradicts the currently stored one ?
• What happens when you update the stored type from 2 to 1 and there are several rows associated with the current company id?
• etc, etc.

Regards,

Stephane Faroult
Oriole

-- 
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).