Check Constraints or Triggers

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

(sebastian.streiger@gmail.com)  writes:
> 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,
> .............................. )
>
>
> Create Table Type2Customer (
>   CustomerCode Integer Not Null,
> .............................. )
>
> 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.
>...
> Are triggers the only way to go?

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(Cust
omerType, 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/techin.../2000/books.asp

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

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