已經(jīng)定義好的表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))現(xiàn)在想當(dāng)刪除Student中的某一項(xiàng)是同時(shí)把SC中的也刪除。就是要達(dá)到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實(shí)現(xiàn)。
2 回答
胡子哥哥
TA貢獻(xiàn)1825條經(jīng)驗(yàn) 獲得超6個贊
這個簡單哎
1.先刪除已經(jīng)建立的外鍵約束
alter table SC drop constraint 外鍵名 --外鍵名可在表架構(gòu)里查到
2.重建外鍵約束并且加入級聯(lián)刪除項(xiàng)
Alter TABLE sc ADD CONSTRAINT [FK_sc] FOREIGN KEY (sno) REFERENCES student(sno)
ON Update CASCADE ON Delete CASCADE --本行表示級聯(lián)刪除的開啟
撒科打諢
TA貢獻(xiàn)1934條經(jīng)驗(yàn) 獲得超2個贊
建刪除觸發(fā)器 給你個例子
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
添加回答
舉報(bào)
0/150
提交
取消