Subject Re: foreign Key Deletion
Author Adam
--- In, "Maurizio P." <mauriz_po@...>
> Thanks for Answers
> now i know more about foreign keys ,
> i have a question refering to the two tables of my previous message
(tBanks , tCustomers) :
> i want the user cannot delete a tBanks record if it exists in the
tCustomers table .
> No other constraints , e.g. a tCustomer record can be not connected
to any tBanks record .
> ...using foreign keys , i can't leave a tCustomer record without a
tBanks record connected .

OK, you want to define a foreign key on tCustomers.tbanksid
referencing tbanks.tbanksid with ON DELETE NO ACTION

This will achieve two things. Firstly, any tbanksid value that goes
into the tCustomer record must be a valid tbanks record. Secondly, any
attempt to delete a tbanks record will fail if there exists a record
in tCustomers that references that tbank.

The only thing you need to now do is to enforce some value exists, and
a simple not null constraint does this effectively.