Re: Non-primary key constraints in Oracle

Ted,

First, fire the DBAs.
No, I'm serious! Well, at least keep them away from Oracle databases.

Second, place a foreign key constraint on the model column in the customer table that references the model column in the car table. This will require the car table has this column in a primary key or unique key index.

SQL> alter table customer add

           (constraint cust__mod_fk  foreign key (model)
             references car (model));

This will enforce the rule that all values inserted or updated into (or pre-existing in) the model column of the customer table must match a value that already exists in the model column of the car table.

If you already have invalid values such as GX-1s in the column that you want to place the foreign key constraint on, then you will have to clear up those exceptions before the constraint can be applied. Should this be the case, then find the script utlexcpt.sql and run it in this schema. This will create a small table into which the rowid of the offending columns will be deposited when you attempt to enable the constraint as in; SQL> alter table customer add

           (constraint cust__mod_fk  foreign key (model)
             references car (model)) exceptions into exceptions;

You can then compare the rowids in the exceptions table with the rowids in the customer table to find the offending values and fix them.

Sometimes it helps to have the model column in the customer table indexed for certain locking situations.

Regards,

Craig M. Wall - Sr. Oracle DBA / C.I.S.A.

ted_graham_at_mail.amsinc.com wrote in message <6moc51$b9p$1_at_nnrp1.dejanews.com>...
>Hello all:
>
>I'm a developer without much database experience, and we are having a
problem
>with setting up our tables.
>
>We want to have Oracle inforce an integrity constraint between two tables,
>where the column that we want to inforce is only part of the primary key
>on the other table.
>
>For example,
>imagine a Car table, with a primary key of two columns: Model and Type.
>
>CAR
>-----------
>Model Type
>------------------
>GX2 2-door
>GX2 4-door
>
>Then, in the Customer table, we want to track the Model of car that the
>customer has purchased. We want to make sure that the value in the
Customer's
>Model column is valid (by checking it against Car:Model), but we don't care
>what Type of car the customer has.
>
>Customer
>------------
>Name Model
>-------------------
>Joe GX2 <---------Fine
>Bob GX1 <-------We want Oracle to catch this
>
>
>Our DBAs say that this cannot be done, but we are having trouble believing
>that. Suggestions on how to accomplish this, or pointers to documentation
>would be great.
>
>Currently, my only access to newsgroups is through DejaNews, so please copy
>my with any answers at Ted_Graham_at_mail.amsinc.com
>
>TIA,
> Ted
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Tue Jun 23 1998 - 10:11:50 CDT