有两张表
create table tab_stu
(
sno int not null,
sname varchar(20) not null,
sex varchar(3) not null,
sage int not null,
constraint pk_sno Primary key (sno) --设置sno为主键
)
create table tab_score
(
sno int not null,
kno varchar(6) not null,
score int not null,
constraint fk_sno foreign key (sno) references tab_stu(sno) --设置外键引用自表tab_stu
)
插入数据:
insert into tab_stu (sno,sname,sex,sage) values ('1','梁静茹','女',27)
insert into tab_score (sno,kno,score) values (1,'k01',78)现在问题是。。想修改sno值为5,下面这样写,SQL错误提示没看明白。。谢谢大家!!!
update tab_stu
set sno=5
from tab_stu,tab_score
where tab_stu.sno=tab_score.sno and tab_score.score=78sql错误提示:
服务器: 消息 547,级别 16,状态 1,行 1
UPDATE 语句与 COLUMN REFERENCE 约束 'fk_sno' 冲突。该冲突发生于数据库 'Library',表 'tab_score', column 'sno'。
语句已终止。
create table tab_stu
(
sno int not null,
sname varchar(20) not null,
sex varchar(3) not null,
sage int not null,
constraint pk_sno Primary key (sno) --设置sno为主键
)
create table tab_score
(
sno int not null,
kno varchar(6) not null,
score int not null,
constraint fk_sno foreign key (sno) references tab_stu(sno) --设置外键引用自表tab_stu
)
插入数据:
insert into tab_stu (sno,sname,sex,sage) values ('1','梁静茹','女',27)
insert into tab_score (sno,kno,score) values (1,'k01',78)现在问题是。。想修改sno值为5,下面这样写,SQL错误提示没看明白。。谢谢大家!!!
update tab_stu
set sno=5
from tab_stu,tab_score
where tab_stu.sno=tab_score.sno and tab_score.score=78sql错误提示:
服务器: 消息 547,级别 16,状态 1,行 1
UPDATE 语句与 COLUMN REFERENCE 约束 'fk_sno' 冲突。该冲突发生于数据库 'Library',表 'tab_score', column 'sno'。
语句已终止。
你 TAB_SCORE 表里有应用 SNO=1的 外键
修改成
create table tab_stu
(
sno int not null,
sname varchar(20) not null,
sex varchar(3) not null,
sage int not null,
constraint pk_sno Primary key (sno) --设置sno为主键
)
create table tab_score
(
sno int not null,
kno varchar(6) not null,
score int not null,
constraint fk_sno foreign key (sno) references tab_stu(sno) ON UPDATE CASCADE --设置外键引用自表tab_stu
) insert into tab_stu (sno,sname,sex,sage) values ('1','梁静茹','女',27)
insert into tab_score (sno,kno,score) values (1,'k01',78) update tab_stu
set sno=5
from tab_stu,tab_score
where tab_stu.sno=tab_score.sno and tab_score.score=78 constraint fk_sno foreign key (sno) references tab_stu(sno) ON UPDATE CASCADE 在外键上添加级联修改既可修改成功
tab_score sno为外键。修改时会发生错误。
使用触发器create trigger t1
on tab_stu
for update
as
if update
update tab-score
set sno=(select sno from inserted )
where son=9select sno fron inserted)