gpt4 book ai didi

sql - SQL Server中表之间的约束,删除一个应该删除另一个

转载 作者:行者123 更新时间:2023-12-02 08:36:13 24 4
gpt4 key购买 nike

我正在创建两个表 RegistrationHeader 和 RegistrationDetail。我如何添加一个约束,当我删除 RegistrationHeader 表时自动删除 RegistrationDetail 表。

create table RegistrationHeader(
RegistrationNo numeric
,BillingID varchar(30) not null
,RegistrationDate date not null
,PaymentType varchar check (PaymentType = 'CC' or PaymentType = 'PO' or PaymentType = 'Check') not null
,CCNumber numeric check(CCNumber >= 15 and CCNumber <=16)
,PONumber varchar(30)
,CheckNumber varchar(10)
,primary key(RegistrationNo)
,foreign key(BillingId) references Person(UserID)
,constraint CC_CCNumber_constr check(
(PaymentType = 'CC' and CCNumber is not null)
or
(PaymentType != 'CC' and CCNumber is null)
)
,constraint PO_PONumber_constr check(
(PaymentType = 'PO' and (PONumber is not null or PONumber != ''))
or
(PaymentType != 'PO' and PONumber is null)
)
,constraint CheckNumber_type_constr check(PaymentType != 'Check' and CheckNumber is null)
);

create table RegistrationDetail(
RegistrationNo numeric
,LineNumber numeric
,CourseID numeric(10) not null
,AttendeeID varchar(30) not null
primary key(RegistrationNo,LineNumber)
);

感谢您的帮助!

最佳答案

你需要

1) 设置RegistrationHeader.RegistrationNoRegistrationDetail.RegistrationNo之间的外键关系

2) 添加 ON DELETE CASCADE 到 RegistrationDetail 外键定义

我还没有测试过这个,但我相信这应该是您在 SQL Server

中寻找的
ALTER TABLE RegistrationDetail
ADD CONSTRAINT FK_RegistrationHeader_RegistrationDetail_Cascade
FOREIGN KEY (RegistrationNo) REFERENCES RegistrationDetail (RegistrationNo) ON DELETE CASCADE

关于sql - SQL Server中表之间的约束,删除一个应该删除另一个,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/21242585/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com