在SQL中我该怎么使用alter加入级联删除？怎么操作呢？

Question

已经定义好的表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实现。

Answer 1

这个简单哎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 --本行表示级联删除的开启

Answer 2

建删除触发器 给你个例子CREATE TRIGGER YHDZD_D ON YHDZD FOR DELETE ASBEGINDECLARE @TMPCOUNT INTSELECT @TMPCOUNT=@@ROWCOUNTIF @@NESTLEVEL<>1RETURNIF Exists (select 1 from deleted where QZRQ<>null)BEGINROLLBACK TRANSACTIONROLLBACKTRIGGER WITH RAISERROR 17006 "已对帐的记录不能删除!"RETURNENDIF (@TMPCOUNT > 1) AND (@TMPCOUNT <> (select count(*) from deleted where QZRQ <>null))BEGINROLLBACK TRANSACTIONROLLBACK TRIGGER WITH RAISERROR 17006"有未对帐的记录存在!"ENDELSE IF @TMPCOUNT > 1BEGINRETURNENDDECLARE @KMID IntDECLARE @RQ smalldatetimeDECLARE @INX numeric(9,0)DECLARE @DiffYE moneyDECLARE curYHDZD_D CURSOR FORSELECT KMID,RQ,INX,JFJE-DFJE FROM deletedWHERE QZRQ=nullORDER BY RQ,INXOPEN curYHDZD_DFETCH curYHDZD_D INTO @KMID,@RQ,@INX,@DiffYEWHILE (@@sqlstatus = 0)BEGINUPDATE YHDZD SET YE=YE-@DiffYEWHERE KMID=@KMID AND (RQ>@RQ ORRQ=@RQ AND INX>@INX)FETCH curYHDZD_D INTO @KMID,@RQ,@INX,@DiffYEENDCLOSE curYHDZD_DDEALLOCATE CURSOR curYHDZD_DEND