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.
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 doesnt 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
Sebastin streigerIn 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:
> 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/techin.../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 Im not in position to change the tables
structures by now. So, Im trying to add constraints to ensure the
data consistency.
Thanks for your valuable feedback
