Question

SQL怎么用alter加入级联删除

已经定义好的表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 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
END

DECLARE @KMID Int
DECLARE @RQ smalldatetime
DECLARE @INX numeric(9,0)
DECLARE @DiffYE money
DECLARE curYHDZD_D CURSOR FOR
SELECT KMID,
RQ,INX,JFJE-DFJE FROM deleted
WHERE QZRQ=null

ORDER BY RQ,INX

OPEN curYHDZD_D
FETCH curYHDZD_D INTO @KMID,@RQ,@INX,@DiffYE
WHILE (@@sqlstatus = 0)
BEGIN
UPDATE YHDZD SET YE=YE-@DiffYE
WHERE KMID=@KMID AND (RQ>@RQ OR
RQ=@RQ AND INX>@INX)
FETCH curYHDZD_D INTO @KMID,@RQ,@INX,@DiffYE
END
CLOSE curYHDZD_D
DEALLOCATE CURSOR curYHDZD_D
END

Answer 3

在sql server建议用触发器级联删除
可参考
http://hi.baidu.com/java_road/blog/item/a560bdf056dbb9aba50f5236.html