主从表,主表是问题表question,字段为questionIdId, questionText, 子表为选择项表option,字段为optionId, questionId, optionText.用erwein来创建的.
现在子表中的记录,如果相同的questionId只有一条记录时,删除不了.抛错提示:
服务器: 消息 30010,级别 16,状态 1,过程 tD_ba_option,行 34
Cannot DELETE last option because question exists.请问这是什么原因?
现在子表中的记录,如果相同的questionId只有一条记录时,删除不了.抛错提示:
服务器: 消息 30010,级别 16,状态 1,过程 tD_ba_option,行 34
Cannot DELETE last option because question exists.请问这是什么原因?
question:
1 中国有多大
2 鸟可以飞吗
option:1 1 很大
2 1 很小
3 2 不可以
现在 option 的3 不可以删除,因为questionId为2的只有一条记录,
而option 的1,2 只能删除一个,当剩一个时,也无法删除.
ALTER TABLE [option] NOCHECK CONSTRAINT ALL--再删除数据--最后打开约束
ALTER TABLE [option] CHECK CONSTRAINT ALL
反的不是这样提示:
Cannot DELETE last option because question exists.而是这样提示:
DELETE 语句与 COLUMN REFERENCE 约束 'FK__ba_option__quest__634EBE90' 冲突。该冲突发生于数据库 'callsale',表 'ba_option', column 'questionId'。
语句已终止。
Cannot DELETE last option because question exists.
把代码贴出来
/*
ACTION is CREATE Table question
*/CREATE TABLE question (
questionId int IDENTITY(1,1),
questionText varchar(500) NOT NULL,
type varchar(50) NOT NULL,
class varchar(50) NULL
)
go
ALTER TABLE question
ADD PRIMARY KEY (questionId ASC)
go
/*
ACTION is CREATE Table optionInfo
*/CREATE TABLE optionInfo (
optionId int IDENTITY(1,1),
optionValue varchar(10) NULL,
optionText varchar(50) NULL,
questionId int NULL
)
go
ALTER TABLE optionInfo
ADD PRIMARY KEY (optionId ASC)
go
ALTER TABLE optionInfo
ADD FOREIGN KEY (questionId)
REFERENCES question (questionId)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go
--下面还有,太长
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* DELETE trigger on question */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* question R/34 question_questionnaire ON PARENT DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="question"
CHILD_OWNER="", CHILD_TABLE="question_questionnaire"
P2C_VERB_PHRASE="R/34", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_34", FK_COLUMNS="questionId" */
if exists (
select * from deleted,question_questionnaire
where
/* %JoinFKPK(question_questionnaire,deleted," = "," and") */
question_questionnaire.questionId = deleted.questionId
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE question because question_questionnaire exists.'
goto error
end /* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* question R/33 answerDetail ON PARENT DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="question"
CHILD_OWNER="", CHILD_TABLE="answerDetail"
P2C_VERB_PHRASE="R/33", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_33", FK_COLUMNS="questionId" */
if exists (
select * from deleted,answerDetail
where
/* %JoinFKPK(answerDetail,deleted," = "," and") */
answerDetail.questionId = deleted.questionId
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE question because answerDetail exists.'
goto error
end /* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* question R/32 optionInfo ON PARENT DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="question"
CHILD_OWNER="", CHILD_TABLE="optionInfo"
P2C_VERB_PHRASE="R/32", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_32", FK_COLUMNS="questionId" */
if exists (
select * from deleted,optionInfo
where
/* %JoinFKPK(optionInfo,deleted," = "," and") */
optionInfo.questionId = deleted.questionId
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE question because optionInfo exists.'
goto error
end
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
gocreate trigger tU_question on question for UPDATE as
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* UPDATE trigger on question */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insquestionId int,
@errno int,
@errmsg varchar(255) select @numrows = @@rowcount
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* question R/34 question_questionnaire ON PARENT UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="question"
CHILD_OWNER="", CHILD_TABLE="question_questionnaire"
P2C_VERB_PHRASE="R/34", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_34", FK_COLUMNS="questionId" */
if
/* %ParentPK(" or",update) */
update(questionId)
begin
if exists (
select * from deleted,question_questionnaire
where
/* %JoinFKPK(question_questionnaire,deleted," = "," and") */
question_questionnaire.questionId = deleted.questionId
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE question because question_questionnaire exists.'
goto error
end
end /* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* question R/33 answerDetail ON PARENT UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="question"
CHILD_OWNER="", CHILD_TABLE="answerDetail"
P2C_VERB_PHRASE="R/33", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_33", FK_COLUMNS="questionId" */
if
/* %ParentPK(" or",update) */
update(questionId)
begin
if exists (
select * from deleted,answerDetail
where
/* %JoinFKPK(answerDetail,deleted," = "," and") */
answerDetail.questionId = deleted.questionId
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE question because answerDetail exists.'
goto error
end
end /* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* question R/32 optionInfo ON PARENT UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="question"
CHILD_OWNER="", CHILD_TABLE="optionInfo"
P2C_VERB_PHRASE="R/32", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_32", FK_COLUMNS="questionId" */
if
/* %ParentPK(" or",update) */
update(questionId)
begin
if exists (
select * from deleted,optionInfo
where
/* %JoinFKPK(optionInfo,deleted," = "," and") */
optionInfo.questionId = deleted.questionId
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE question because optionInfo exists.'
goto error
end
end
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_optionInfo on optionInfo for DELETE as
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* DELETE trigger on optionInfo */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* question R/32 optionInfo ON CHILD DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="question"
CHILD_OWNER="", CHILD_TABLE="optionInfo"
P2C_VERB_PHRASE="R/32", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_32", FK_COLUMNS="questionId" */
if exists (select * from deleted,question
where
/* %JoinFKPK(deleted,question," = "," and") */
deleted.questionId = question.questionId and
not exists (
select * from optionInfo
where
/* %JoinFKPK(optionInfo,question," = "," and") */
optionInfo.questionId = question.questionId
)
)
begin
select @errno = 30010,
@errmsg = 'Cannot DELETE last optionInfo because question exists.'
goto error
end
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
gocreate trigger tU_optionInfo on optionInfo for UPDATE as
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* UPDATE trigger on optionInfo */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insoptionId int,
@errno int,
@errmsg varchar(255) select @numrows = @@rowcount
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
/* question R/32 optionInfo ON CHILD UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="question"
CHILD_OWNER="", CHILD_TABLE="optionInfo"
P2C_VERB_PHRASE="R/32", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_32", FK_COLUMNS="questionId" */
if
/* %ChildFK(" or",update) */
update(questionId)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,question
where
/* %JoinFKPK(inserted,question) */
inserted.questionId = question.questionId
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
select @nullcnt = count(*) from inserted where
inserted.questionId is null
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE optionInfo because question does not exist.'
goto error
end
end
/* ERwin Builtin Tue Jan 30 00:16:44 2007 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go