Re: Primary key and Foreign key

From: Heinz Huber <hhuber_at_racon-linz.at>
Date: 16 Sep 2002 06:29:52 GMT
Message-ID: <Xns928B56719740Bhhuberraconlinzat@195.3.96.116>

kyfung_at_hotmail.com (K.Y. Fung) wrote in
news:c22bb40d.0209142326.2dd7ca_at_posting.google.com:

> Heinz Huber <hhuber_at_racon-linz.at> wrote in message
> news:<Xns9287500C34847hhuberraconlinzat_at_195.3.96.116>...
>

>> Another possibility would be to have an additional table where you
>> insert a record for every customer you want to keep:
>> KeepCustomer (
>> custId
>> )
>> Then create a FK relationship from KeepCustomer to Customer.
>> 
>> The result is that you can't delete a Customer row without deleting
>> the corresponding KeepCustomer row. Although as you wrote, you don't
>> yet know how to prevent somebody from deleting a KeepCustomer row.

>
> Referring to Heinz's suggestion, with:
> CustomerId is the primary key of Customer.
> CustId is the primary key of KeepCustomer.
>
> First we have:
> Customer(CustomerId, CustomerName, CustomerAddress, . . . )
>
> and
> KeepCustomer(CustId)
> Foreign key CustId reference Customer not allowed null
>
> Okay this can prevent the deletion of Customer record but not
> KeepCustomer, how about adding a new constraint to KeepCustomer:
>
> constraint ((project Customer over CustomerId) difference (project
> KeepCustomer over CustId)) is empty
>
> It looks a bit dump, but am I close to a home run now?

If I understand the constraint correctly, it is the same as a reverse foreign key definition from KeepCustomer to Customer.

The problem is that you can't insert any records into Customer or KeepCustomer any more.
That is unless, you defer the constraint checking until the next commit. In this case, you can delete a record in those tables though. You only have to delete it in both tables.

To cut the whole story short, you can't do what you want with constraint or fk definitions alone. You have to have some way of disabling a delete on a table.

Regards,
Heinz Received on Mon Sep 16 2002 - 01:29:52 CDT