Posted by Erland Sommarskog on 08/27/05 00:38

(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(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

[Back to original message]