sql-server - 如何使用外键创建表之间的快捷方式

Question

我试图找出一种在三个表之间创建快捷方式的方法。

我有一个主表 A、一个引用第一个表 B 的表和一个引用 B、C 的表。我想在 A 和 C 之间创建一个快捷方式，因此我不必使用表 B。

在下面的示例中，我想确保外键 FK_A_ID1 和 FK_A_ID2 始终彼此相等，导致执行最后一个插入语句时失败。

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);

这可以通过使用外键实现，还是有另一个我不知道的内置功能？

这样做的目的是在表 A 和 C 之间建立可靠的“捷径”

标签： sql-server

Answer 1

一种方法是使用触发器。触发器可以执行必要的连接以确保 CA = CB->BA 我们使用此方法来验证父表、子表和孙表之间的父键匹配。

例如：

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

另一种方法是使用复合主键。定义B的主键为(a_id, id)。设置从 B(a_id) 到 A(id) 的外键。设置从 C(a_id, b_id) 到 B(a_id, id) 的第二个外键。此时，您在 C.a_id 和 B.a_id 之间具有参照完整性。

例如：

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;