Hi, I=B4m facing teh following situation:

This are just sample table names, but should do for discussing
purpouses.

Create table Invoice
(
InvoiceID Integer Not Null,
CustomerType Integer Not Null,
CustomerCode Integer Not Null,
Amount DECIMAL(10,2) Not Null,
.................
)

Create Table Type1Customer
(
CustomerCode Integer Not Null,
..=2E............................
)


Create Table Type2Customer
(
CustomerCode Integer Not Null,
..=2E............................
)

I need to add a way to restrict the CustomerType and CustomerCode,
in the Invoice table to the correct values.
This means that if customerType equals 1 the customerCode should be
checked against Type1Customer and if customerType equals 2 the
customerCode should be checked against Type2Customer.

I succesfully created a check constraint. That ensures that the valid
values exists when the rows in the Invoice table are inserted or
updated, but doesn=B4t prevent from deleting records from tables
Type1Customer and Type2Customer that are referenced from the Invoice
table.

Are triggers the only way to go?

Thanks in advance

Sebasti=E1n streiger

In addition to Erland's suggestion,
I would recommend adding CustomerType to both Type1Customer and
Type2Customer, and adding CustomerType to their FK constraints

(sebastian.streiger@gmail.com) writes:
[quoted text, click to view]

With that data model, yes. But is that really the right data model?

I would rather have a CustomerCode table which could look like this:

CREATE TABLE CustomerCode (
CustomerType integer NOT NULL,
CustomerCode integer NOT NULL,
CONSTRAINT pk_CustomerCode(CustomerType, CustomerCode))

Then Invoices could refer to this table, and so could the child
tables Type1Customer and Type2Customer.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Erland and AK:
Thank you for answering.
I DO agree that the model is no the best one that we can have. But due
to organizational issues I=B4m not in position to change the tables
structures by now. So, I=B4m trying to add constraints to ensure the
data consistency.


Thanks for your valuable feedback