--请使用下面的数据库和数据测试 :
create table Leavewords
(
LeavewordID int identity(1,1) not null constraint PK_Leavewords primary key,
Title varchar(200),
Body text,
CreateTime datetime
)
go create table Users
(
UserID int identity(1,1) not null constraint PK_Users primary key,
UserName varchar(32),
Password varchar(255),
Email varchar(200),
IsAdmin bit
)
go create table Replies
(
ReplyID int identity(1,1) not null constraint PK_Replies primary key,
Body text,
CreateTime datetime
)
go create table ReplyLeaveword
(
ReplyID int constraint FK_ReplyLeaveword_Replies references Replies(ReplyID) on delete cascade,
LeavewordID int constraint FK_ReplyLeaveword_Leavewords references Leavewords(LeavewordID) on delete cascade
)
go insert Leavewords values('a','bb','2006-04-09 22:03:26.043')
insert Leavewords values('a1','cc','2006-04-10 22:03:26.043')
insert Leavewords values('a2','dd','2006-04-11 22:03:26.043')
insert Leavewords values('a3','ee','2006-04-12 22:03:26.043')
go insert Replies values('cdef','2006-04-09 22:03:26.043')
insert Replies values('cdefg','2006-04-10 22:03:26.043')
insert Replies values('cdefgh','2006-04-11 22:03:26.043')
insert Replies values('cdefghi','2006-04-12 22:03:26.043')
insert Replies values('cdefghij','2006-04-13 22:03:26.043')
insert Replies values('cdefghijk','2006-04-14 22:03:26.043')
go insert ReplyLeaveword values(1,1)
insert ReplyLeaveword values(1,2)
insert ReplyLeaveword values(3,3)
insert ReplyLeaveword values(3,4)
insert ReplyLeaveword values(4,2)
insert ReplyLeaveword values(6,1)
go CREATE PROC DELETE_Leavewords (@ID INT)
AS
BEGIN
DELETE FROM Replies WHERE ReplyID IN (SELECT ReplyID FROM ReplyLeaveword WHERE ReplyLeaveword.LeavewordID=@ID )
DELETE FROM ReplyLeaveword WHERE ReplyLeaveword.LeavewordID=@ID
DELETE FROM Leavewords WHERE LeavewordID=@ID
END
GO
--执行存储过程
execute DELETE_Leavewords '2'
--结果错误
删除前:
ReplyID LeavewordID
1 1
1 2
3 3
3 4
4 2
6 1
删除后:(错误结果)
ReplyID LeavewordID
3 3
3 4
6 1
删除后:(正确结果)
ReplyID LeavewordID
1 1
3 3
3 4
6 1
存储过程多删除掉(1,1)呢?原因是因为这条数据也是相同的回复ID,但是我是对别的留言的回复。求解决这个问题的sql!
create table Leavewords
(
LeavewordID int identity(1,1) not null constraint PK_Leavewords primary key,
Title varchar(200),
Body text,
CreateTime datetime
)
go create table Users
(
UserID int identity(1,1) not null constraint PK_Users primary key,
UserName varchar(32),
Password varchar(255),
Email varchar(200),
IsAdmin bit
)
go create table Replies
(
ReplyID int identity(1,1) not null constraint PK_Replies primary key,
Body text,
CreateTime datetime
)
go create table ReplyLeaveword
(
ReplyID int constraint FK_ReplyLeaveword_Replies references Replies(ReplyID) on delete cascade,
LeavewordID int constraint FK_ReplyLeaveword_Leavewords references Leavewords(LeavewordID) on delete cascade
)
go insert Leavewords values('a','bb','2006-04-09 22:03:26.043')
insert Leavewords values('a1','cc','2006-04-10 22:03:26.043')
insert Leavewords values('a2','dd','2006-04-11 22:03:26.043')
insert Leavewords values('a3','ee','2006-04-12 22:03:26.043')
go insert Replies values('cdef','2006-04-09 22:03:26.043')
insert Replies values('cdefg','2006-04-10 22:03:26.043')
insert Replies values('cdefgh','2006-04-11 22:03:26.043')
insert Replies values('cdefghi','2006-04-12 22:03:26.043')
insert Replies values('cdefghij','2006-04-13 22:03:26.043')
insert Replies values('cdefghijk','2006-04-14 22:03:26.043')
go insert ReplyLeaveword values(1,1)
insert ReplyLeaveword values(1,2)
insert ReplyLeaveword values(3,3)
insert ReplyLeaveword values(3,4)
insert ReplyLeaveword values(4,2)
insert ReplyLeaveword values(6,1)
go CREATE PROC DELETE_Leavewords (@ID INT)
AS
BEGIN
DELETE FROM Replies WHERE ReplyID IN (SELECT ReplyID FROM ReplyLeaveword WHERE ReplyLeaveword.LeavewordID=@ID )
DELETE FROM ReplyLeaveword WHERE ReplyLeaveword.LeavewordID=@ID
DELETE FROM Leavewords WHERE LeavewordID=@ID
END
GO
--执行存储过程
execute DELETE_Leavewords '2'
--结果错误
删除前:
ReplyID LeavewordID
1 1
1 2
3 3
3 4
4 2
6 1
删除后:(错误结果)
ReplyID LeavewordID
3 3
3 4
6 1
删除后:(正确结果)
ReplyID LeavewordID
1 1
3 3
3 4
6 1
存储过程多删除掉(1,1)呢?原因是因为这条数据也是相同的回复ID,但是我是对别的留言的回复。求解决这个问题的sql!
AS
BEGIN
DELETE FROM Replies WHERE ReplyID IN (SELECT ReplyID FROM ReplyLeaveword WHERE ReplyLeaveword.LeavewordID=@ID )
DELETE FROM ReplyLeaveword WHERE ReplyLeaveword.LeavewordID=@ID
DELETE FROM Leavewords WHERE LeavewordID=@ID
你存储过程里面三个表都删除了,
数据和数据库不是都写在上面了,还怎么写?
按你说的改了,不用级联删除如下:
create table ReplyLeaveword
(
ReplyID int constraint FK_ReplyLeaveword_Replies references Replies(ReplyID),
LeavewordID int constraint FK_ReplyLeaveword_Leavewords references Leavewords(LeavewordID)
)
go
执行后报错如下:
服务器: 消息 547,级别 16,状态 1,过程 DELETE_Leavewords,行 4
DELETE 语句与 COLUMN REFERENCE 约束 'FK_ReplyLeaveword_Replies' 冲突。该冲突发生于数据库 'lb3',表 'ReplyLeaveword', column 'ReplyID'。
语句已终止。
服务器: 消息 547,级别 16,状态 1,过程 tr_Leavewordsdelete,行 10
DELETE 语句与 COLUMN REFERENCE 约束 'FK_ReplyLeaveword_Replies' 冲突。该冲突发生于数据库 'lb3',表 'ReplyLeaveword', column 'ReplyID'。
语句已终止。
发错了上面的不对,是下面的错误:
---------------------------------------------------------------------
服务器: 消息 547,级别 16,状态 1,过程 DELETE_Leavewords,行 4
DELETE 语句与 COLUMN REFERENCE 约束 'FK_ReplyLeaveword_Replies' 冲突。该冲突发生于数据库 'lb3',表 'ReplyLeaveword', column 'ReplyID'。
语句已终止。(所影响的行数为 2 行)
(所影响的行数为 1 行)
服务器: 消息 547,级别 16,状态 1,过程 DELETE_Leavewords,行 4
DELETE 语句与 COLUMN REFERENCE 约束 'FK_ReplyLeaveword_Replies' 冲突。该冲突发生于数据库 'lb3',表 'ReplyLeaveword', column 'ReplyID'。
语句已终止。
这个错误怎么解决?
你把列约束去掉