SQL怎么用alter加入级联删除

来源：百度知道 编辑：UC知道 时间：2024/05/17 03:37:26
已经定义好的表create table SC
(
Sno char(9),
Cno char(4),
Grade smallint,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
)
现在想当删除Student中的某一项是同时把SC中的也删除。就是要达到
create table SC
(
Sno char(9),
Cno char(4),
Grade smallint,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno)
on delete cascade,
FOREIGN KEY (Cno) REFERENCES Course(Cno)
)
的效果，怎么用ALTER实现。

这个简单哎
1.先删除已经建立的外键约束
alter table SC drop constraint 外键名 --外键名可在表架构里查到
2.重建外键约束并且加入级联删除项
Alter TABLE sc ADD CONSTRAINT [FK_sc] FOREIGN KEY (sno) REFERENCES student(sno)
ON Update CASCADE ON Delete CASCADE --本行表示级联删除的开启

建删除触发器 给你个例子
CREATE TRIGGER YHDZD_D ON YHDZD FOR DELETE AS
BEGIN
DECLARE @TMPCOUNT INT
SELECT @TMPCOUNT=@@ROWCOUNT

IF @@NESTLEVEL<>1
RETURN
IF Exists (select 1 from deleted where QZRQ<>null)
BEGIN
ROLLBACK TRANSACTION
ROLLBACK
TRIGGER WITH RAISERROR 17006 "已对帐的记录不能删除!"
RETURN
END

IF (@TMPCOUNT > 1) AND (@TMPCOUNT <> (select count(*) from deleted where QZRQ <>null))
BEGIN
ROLLBACK TRANSACTION
ROLLBACK TRIGGER WITH RAISERROR 17006
"有未对帐的记录存在!"
END
ELSE IF @TMPCOUNT > 1
BEGIN
RETURN