How to Create Shortcut Between Tables by Use of Foreign Keys

user_4462430 0
Viewed 97

I am trying to figure out a method of creating a shortcut between three tables .

I have a master table, A, a table that references the first table, B, and a table that references B, C. I want to create a shortcut between A and C so I don't have to use table B.

In the below example, I want to ensure that the foreign keys FK_A_ID1 and FK_A_ID2 are always equal to each other, causing it to fail when the last insert statement is executed.

CREATE TABLE A (
    ID int unique identity,
    num int)

CREATE TABLE B (
    ID int unique identity, 
    A_ID int NOT NULL, 
    CONSTRAINT FK_A_ID1 FOREIGN KEY (A_ID) REFERENCES A (ID))

CREATE TABLE C (
    ID int unique identity, 
    A_ID int NOT NULL, 
    B_ID int NOT NULL, 
    CONSTRAINT FK_A_ID2 FOREIGN KEY (A_ID) REFERENCES A (ID),
    CONSTRAINT FK_B_ID  FOREIGN KEY (B_ID) REFERENCES B (ID))

INSERT INTO A VALUES (0);
DECLARE @A1 int = SCOPE_IDENTITY();
INSERT INTO A VALUES (1);
DECLARE @A2 int = SCOPE_IDENTITY();
INSERT INTO B Values (@A1);
DECLARE @B1 int = SCOPE_IDENTITY();
INSERT INTO C Values (@A2, @B1);

Is this possible by use of foreign keys or is there another built-in function that I don't know of?

The goal of this is to have a reliable 'shortcut' between tables A and C

sql-server
2 Answers

One way to do this is with triggers. A trigger can do the joins necessary to ensure C.A = C.B->B.A. We use this method to verify parent keys match up between parent, child, and grandchild tables.

For example:

-- untested code
create trigger C_IU_Verify_A on C
for insert, update as
    if exists
    (
        select 1
        from inserted
        inner join b on b.id = inserted.b_id
        where b.a_id <> inserted.a_id
    )
    begin
        raiserror('Parent table keys do not match.', 16, 1)
        rollback
    end

Another way to do this is with compound primary keys. Define the primary key of B as (a_id, id). Setup a foreign key from B(a_id) to A(id). Setup a second foreign key from C(a_id, b_id) to B(a_id, id). At this point, you have referential integrity between C.a_id and B.a_id.

For example:

create table a (id int primary key clustered)
create table b(a_id int, id int, primary key (a_id, id), unique(id))
create table c(a_id int, b_id int, id int, primary key (a_id, b_id, id), unique(id))

alter table B add constraint fk_b_aid foreign key (a_id) REFERENCES A(id)
alter table C add constraint fk_c_aid_bid foreign key (a_id, b_id) REFERENCES B(a_id, id)

insert into a (id) select 1
insert into a (id) select 2
insert into b (a_id, id) select 1, 1
insert into b (a_id, id) select 1, 2
--insert into b (a_id, id) select 2, 1 -- error: duplicate b.id
insert into b (a_id, id) select 2, 3
--insert into b (a_id, id) select 3, 1 -- error: there is no A with id = 3
insert into c (a_id, b_id, id) select 1, 1, 1
insert into c (a_id, b_id, id) select 1, 1, 2
insert into c (a_id, b_id, id) select 1, 2, 3
insert into c (a_id, b_id, id) select 2, 3, 4
--insert into c (a_id, b_id, id) select 1, 3, 5 -- error: there is no B with B.a_id = 1 and B.id = 3

drop table c;
drop table b;
drop table a;
user_279680

I'm reasonably sure the following does what you want. Using psuedo code:

--  Has a primary key
CREATE TABLE A
(
    A_id   PrimaryKey
)


--  Has both a primary key and a compound unique constraint, as well as a foreign key to A
CREATE TABLE B
(
    B_id   PrimaryKey   Unique_1of2
   ,A_id                Unique_2of2   ForeignKey_to_A
)


--  Has a primary key and a compound foreign key to B
CREATE TABLE C
(
    C_id   PrimaryKey
   ,B_id   ForeignKey_to_B_1of2
   ,A_id   ForeignKey_to_B_2of2
)

Done this way:

  • It is not possible to put a value for A_id in B that is not also in A
  • it is not possible to put a values for A_id and B_id in table C that are not also a pair in B

NULL values may or may not mess with this--if you have to deal with them as well, you have bigger problems.

user_74910
Related