Dear Guys,
How do we control total participation? By SQL or by program? Is there any constraint, which imposes a record should be "referenced" when it is inserted? i.e. I would like to insert one record into Customer table, however, I wish that there is another table (i.e. InsuranceCards table) which can reference this record right away!!! Then I can make sure that the Customers table is the "total participation." Coz it's not relevant that there is a customer, but he/she doesn't buy any insurance!!! All customers should at least buy a insurance (which has a record refer to this customer)!!!

Thx,

Neil

Inelegant solution: you could have a "redundant" foreign key from Customer to InsuranceCards (pick any row, e.g. "first" row). The constraint would need to be DEFERRED so that it isn't checked until COMMIT.

Better, if your DBMS supports it, is an ASSERTION that checks ((SELECT COUNT(*) FROM InsuranceCards ic WHERE ic.cust_id = customer.cust_id) > 0). But I don't know if any real DBMS supports that (Oracle does not for sure).

Another method that can be implemented (e.g. in Oracle) is to use a combination of database trigger and deferred check constraint:

1) Add a column to Customer:

ALTER TABLE Customer ADD (num_insurance_cards INTEGER DEFAULT 0);

2) Add a deferred check constraint:

ALTER TABLE Customer ADD CONSTRAINT cust_chk
CHECK (num_insurance_cards > 0)
DEFERRABLE INITIALLY DEFERRED;

3) Create a database trigger on InsuranceCards that maintains Customer.num_insurance_cards whenever InsuranceCard rows are inserted or deleted (or updated, if transfers are allowed).

This way, you can insert a new Customer but can only COMMIT successfully if the num_insurance_cards values is > 0. To be secure, you might want to protect that column from direct manipulation by the user. This could be done by creating a view on the table that omits the column, and allowing the users to perform DML only on the view.

If your DBMS doesn't support deferred constraints or assertions, than I am afraid you will have to enforce it via procedural code - e.g. in a stored procedure.

thx buddy.

Dear,
According to your ASSERTION method, how about InsuranceCard has foreign key reference to Customer. If I use assertion method, will that cause i can never insert a new customer?

neil

I'm not so clear on the ASSERTION method, because I have never had the opportunity to use it. Like all the other methods, the assertion constraint would need to be deferrable until COMMIT, because it is obviously violated otherwise (assuming Customer inserted first). So the sequence of events would be:

1) insert Customer (assertion would fail, but is not checked as it is "deferred")

2) insert InsuranceCard (foreign key constraint validated)

3) commit (deferred assertion is now checked and succeeds)

Alternatively, the foreign key could be deferred and the assertion immediate, in which case the sequence would be:

1) insert InsuranceCard (foreign key would fail, but is not checked as it is "deferred")

2) insert Customer (assertion validated)

3) commit (deferred foreign key is now checked and succeeds)