已經定義好的表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實現。
2 回答
胡子哥哥
TA貢獻1825條經驗 獲得超6個贊
這個簡單哎
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 --本行表示級聯刪除的開啟
撒科打諢
TA貢獻1934條經驗 獲得超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
添加回答
舉報
0/150
提交
取消