SQL Reference multiple tables

Question

I want to define a mapping in SQL. The mapping is from one source table to several target tables. I'm using referential integrity, so if a value in the source table is deleted, the value in the mapping table should be deleted as well. The defination of the tables should look somehow like that:

-- Table with source members
Create Table dbo.Source_Members
(
    Id int IDENTITY(1,1) Not Null,
    Name [nvarchar](50) Null
    CONSTRAINT [PK_Source_Members_Id] PRIMARY KEY CLUSTERED
    (
        Id
    )
);
-- Table with target members
Create Table dbo.Target_Members1
(
    Id int IDENTITY(1,1) Not Null,
    Name [nvarchar](50) Null,
    Target_Table_Id int DEFAULT 1
    CONSTRAINT [PK_Target_Members1_Id] PRIMARY KEY CLUSTERED
    (
        Id, Target_Table_Id
    )
);
Create Table dbo.Target_Members2
(
    Id int IDENTITY(1,1) Not Null,
    Name [nvarchar](50) Null,
    Target_Table_Id int DEFAULT 2
    CONSTRAINT [PK_Target_Members2_Id] PRIMARY KEY CLUSTERED
    (
        Id, Target_Table_Id
    )
);
-- Mapping table
Create Table dbo.Mapping
(
    Id int IDENTITY(1,1) Not Null,
    Id_Source_Members int Not Null,
    Id_Target_Members int Not Null,
    Target_Table_Id int Not Null
    CONSTRAINT [PK_Mapping] PRIMARY KEY CLUSTERED
    (
        Id
    )
);
-- Define first Foreign Key, referencing the source member (just one table)
Alter Table dbo.Mapping WITH CHECK
ADD CONSTRAINT [FK_Source_Members] FOREIGN KEY (Id_Source_Members) REFERENCES dbo.Source_Members (Id)
ON UPDATE CASCADE
ON DELETE CASCADE
-- Define second Foreign Key, referencing the target member (several tables)
Alter Table dbo.Mapping WITH CHECK
ADD CONSTRAINT [FK_Target_Members1] FOREIGN KEY (Id_Target_Members, Target_Table_Id) REFERENCES dbo.Target_Members1 (Id, Target_Table_Id)
ON UPDATE CASCADE
ON DELETE CASCADE
Alter Table dbo.Mapping WITH CHECK
ADD CONSTRAINT [FK_Target_Members2] FOREIGN KEY (Id_Target_Members, Target_Table_Id) REFERENCES dbo.Target_Members2 (Id, Target_Table_Id)
ON UPDATE CASCADE
ON DELETE CASCADE

But this defination is not working. I would like to insert values in the mapping table like that:

INSERT INTO dbo.Mapping(Id_Source_Members, Id_Target_Members, Target_Table_Id)
SELECT 1, 1, 1
UNION
SELECT 2, 1, 2
UNION
SELECT 3, 2, 2

Like that, the first source value should be mapped to the first value in Target_Members1. The second source value should be mapped to the first value in Target_Members2. The third source value should be mapped to the second value in Target_Members2 and so on. As I mentioned before, it's not working like that. How am I doing something like that, where is the failure?

Patrick Hofman · Answer 1 · 2014-04-01T09:53:14.897

0

You cannot insert in multiple tables at once. You need separate SQL statements to do that.

So you will need:

INSERT INTO Source_Members(Name)
SELECT 'x'
UNION
SELECT 'y'
UNION
SELECT 'z'
;

INSERT INTO Target_Members1(Target_Table_Id)
SELECT 1
UNION
SELECT 2
;

INSERT INTO Target_Members2(Target_Table_Id)
SELECT 1
UNION
SELECT 2
;

INSERT INTO Mapping(Id_Source_Members, Id_Target_Members, Target_Table_Id)
SELECT 1, 1, 1
UNION
SELECT 3, 2, 2
;

edited Apr 01 '14 at 09:53

answered Apr 01 '14 at 06:57

Patrick Hofman

153,850
22
249
325

I tried it like that before. If I do this: `INSERT INTO dbo.Mapping(Id_Source_Members, Id_Target_Members, Target_Table_Id) SELECT 1, 1, 1' I got the error message "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Target_Members2". The conflict occurred in database "Hyperion_Staging", table "dbo.Target_Members2"." If I do that: `INSERT INTO dbo.Mapping(Id_Source_Members, Id_Target_Members, Target_Table_Id) SELECT 2, 1, 2` I got (as expected) the message: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Target_Members1"..." – el smu Apr 01 '14 at 08:06
@elsmu: Did you fill that table too? – Patrick Hofman Apr 01 '14 at 08:07
Yes: INSERT INTO dbo.Target_Members1(Name) SELECT 'Target1_Member1' UNION SELECT 'Target1_Member2' UNION SELECT 'Target1_Member3' INSERT INTO dbo.Target_Members2(Name) SELECT 'Target2_Member1' UNION SELECT 'Target2_Member2' UNION SELECT 'Target2_Member3' – el smu Apr 01 '14 at 08:13
@elsmu: And what are the ID's? They should be matching the ones you use in your insert. – Patrick Hofman Apr 01 '14 at 08:15
It's for both 1, 2, 3 – el smu Apr 01 '14 at 08:22
@elsmu: The actual problem is in the last insert, second line. There is no match for those values in `Target1_Member1` (`SELECT 2, 1, 2`) – Patrick Hofman Apr 01 '14 at 08:28
Sure there is, it's in "dbo.Target_Members2". I have to target member tables, that's what it is all about, because I want to do referental integrity with several tables. – el smu Apr 01 '14 at 09:00
I ran your script, and since there is a multi-column constraint on the Mapping table, the second line fails since there is no matching line in the Target1_Member1 (the constraint points there, not to Target_Members2) – Patrick Hofman Apr 01 '14 at 09:02
The constraint points to both tables, Target_Members1 and Target_Members2. Did you run the whole script? – el smu Apr 01 '14 at 09:37
Yes, I did. The problem is that it hits the first constraint: FK_Target_Members1. It has a foreign key constraint on both Id and Target_Table_Id of Target_Members1. There is no matching line in the example you gave. – Patrick Hofman Apr 01 '14 at 09:42
OK, different approach. If I do that: INSERT INTO dbo.Mapping(Id_Source_Members, Id_Target_Members, Target_Table_Id) SELECT 1, 1, 1 I get the error "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Target_Members2". The conflict occurred in database "Hyperion_Staging", table "dbo.Target_Members2"." In the source table, there is Id = 1, in the Target_Members1 table there is a ID = 1 / Target_Table_Id = 1. And this is working for you??? – el smu Apr 01 '14 at 09:49
@elsmu: I updated the answer. This works for me on your script. – Patrick Hofman Apr 01 '14 at 09:53
OK, now I got it. But this is not what I wanted to do. I need both entries in both tables, but I want every entry just unique either in one table or in the other one. In Target_Members1, I want the Target_Table_Id always to be 1, in Target_Members2, I want the Target_Table_Id always to be 2. So in the Mapping, I want to reference either the one target table (with 1, 1, 1 in the Mapping table,e I want to reference number 1 in Target_Members1) or the other. Do you know if / how i can do that? – el smu Apr 01 '14 at 10:12
@elsmu: If you mean you want to have this constraint OR the other constraint, no. There is no option to do that. Can't you merge those tables? – Patrick Hofman Apr 01 '14 at 10:13
@elsmu: Need more help? – Patrick Hofman Apr 01 '14 at 11:07
Unfortunately I can't. Its two absolutely different tables, so merging them is no option. If there is no way to do so, I would even rather not implement the referental integrity. – el smu Apr 01 '14 at 11:33
@elsmu: You could create a post-update trigger to do the checking instead of the referential constraint. – Patrick Hofman Apr 01 '14 at 12:05

SQL Reference multiple tables

1 Answers1