asked
How to validate table entries against multiple tables
Excuse my lack of SQL skills, if this turns out to be an easy one ... Consider the following Widgets table, and it's populating script: CREATE TABLE dbo.Widgets( Code varchar(10) NOT NULL, Type varchar(1) NOT NULL, Name varchar(50) NOT NULL, CONSTRAINT PK_Widgets PRIMARY KEY CLUSTERED ( Code ASC, Type ASC )) GO INSERT INTO dbo.Widgets (Code ,Type ,Name) VALUES ('WIDGET1' ,'W' ,'Widget Number 1') GO INSERT INTO dbo.Widgets (Code ,Type ,Name) VALUES ('WIDGET2' ,'W' ,'Widget Number 2') GO INSERT INTO dbo.Widgets (Code ,Type ,Name) VALUES ('WIDGET3' ,'W' ,'Widget Number 3') GO Also, consider the following Gears table, and it's populating script: CREATE TABLE dbo.Gears( Code varchar(10) NOT NULL, Type varchar(1) NOT NULL, Name varchar(50) NOT NULL, CONSTRAINT PK_Gears PRIMARY KEY CLUSTERED ( Code ASC, Type ASC )) GO INSERT INTO dbo.Gears (Code ,Type ,Name) VALUES ('GEAR1' ,'W' ,'Gear Number 1') GO INSERT INTO dbo.Gears (Code ,Type ,Name) VALUES ('GEAR2' ,'W' ,'Gear Number 2') GO INSERT INTO dbo.Gears (Code ,Type ,Name) VALUES ('GEAR3' ,'W' ,'Gear Number 3') GO Lastly, consider the Details table: CREATE TABLE dbo.Details( Code varchar(10) NOT NULL, Type varchar(1) NOT NULL, Details text NULL, CONSTRAINT PK_Details PRIMARY KEY CLUSTERED ( Code ASC, Type ASC )) GO What I'd like to be able to do, is ensure that any row inserted into the Details table, has a matching row (via the Code and Type columns as a key) in either the Widgets table OR the Gears table. Can anyone suggest a way of enforcing this rule at the database level? Thanks T
**I must add if you need such constraint for an INSERT, then you do need the same constraint for an UPDATE as well.** Now to handle such situation, you need to implement TRIGGER on your Details table. Hence, whenever an INSERT/UPDATE is initiated, the inserted/updated values should be checked against both Gears and Widgets table. If matching row/s is/are found then INSERT/UPDATE, else ROLLBACK. I could have given you a script for that, but it would be good for you to explore it yourself to understand the functionality of TRIGGERS before implementation. Besides, I do not know your environment, but I think you may need TRIGGERS on Gears and Widgets tables as well. Imagine a scenario, where code/type values are changed in Gears/Widgets tables, and you may have old values in Details table. So please look into more details while implementing the solution. Cheers.
@sqlchamp No problem. But Can you please show us an example to implement it with a CONSTRAINT with the same data structures and requirements originally posted. I will wait for it. Thanks in advance.
What you are asking for can't be done with referential integrity. What can however be done is to have foreign keys to two tables, using different columns, make these columns null-able and create a check constraint which makes sure that one and exactly one of these two are NULL. An example: create table t1 (id int primary key) create table t2 (id int primary key) create table t3 (id int identity(1,1) primary key, t1id int, t2id int CONSTRAINT FK_t3_t2 FOREIGN KEY(t2id) REFERENCES t2(id), CONSTRAINT FK_t3_t1 FOREIGN KEY(t1id) REFERENCES t1(id), CONSTRAINT CK_t2id_t3id_isnotnull CHECK ((t2id is null and t1id is not null) OR (t1id is null and t2id is not null))) --Fill t1 and t2 insert into t1 (id) values(1),(2),(3) insert into t2 (id) values(1),(2),(3) --Fill t3 with valid values --for both t1id and t2id insert into t3 (t1id, t2id) values(null,1),(null,2),(null,3),(1,null),(2,null),(3,null) --This will break the CK_t2id_t3id_isnotnull constraint and therefore fail insert into t3 (t1id,t2id) values(null,null) --This will break the foreign key constraint and therefore fail insert into t3 (t1id, t2id) values(null,4) --This will break the CK_t2id_t3id_isnotnull constraint as well, since it demands that either t1id or t2id is null insert into t3 (t1id, t2id) values(1,3) I know it's not exactly what you're asking for, but it gets you close and it doesn't use triggers. I would have loved for SQL Server to allow a foreign key constraint to a view, because that would have solved your problem exactly as you want it. But that's not there, and if you want to keep exactly the table structure that you have, you probably would have to go with Usman Butt's suggestion to create triggers on all three tables instead.
Forign key must solve this issue.. foreign keys on the both tables.
Sorry, it will not work. This would mean that any values to be inserted in details table must exist in both tables i.e. Gears and Widgets, which is not the requirement.
