try use cascade:
fro example:CREATE TABLE [dbo].[test1] (
[id] [int] NOT NULL ,
[descr] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[test2] (
[id] [int] NOT NULL ,
[ref_id] [int] NOT NULL ,
[descr] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[test1] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[test2] WITH NOCHECK ADD
CONSTRAINT [PK_test2] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[test2] ADD
CONSTRAINT [FK_test2_test1] FOREIGN KEY
(
[ref_id]
) REFERENCES [dbo].[test1] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE
GOinsert into test1 values(1,'a dog')
insert into test1 values(2,'a cat')
insert into test1 values(3,'a fish')
insert into test1 values(4,'a camel')
go
insert into test2 values (1,2,'a cat')
insert into test2 values (2,3,'a fish')
insert into test2 values (3,4,'a camel')
goselect * from test1
select * from test2
fro example:CREATE TABLE [dbo].[test1] (
[id] [int] NOT NULL ,
[descr] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[test2] (
[id] [int] NOT NULL ,
[ref_id] [int] NOT NULL ,
[descr] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[test1] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[test2] WITH NOCHECK ADD
CONSTRAINT [PK_test2] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[test2] ADD
CONSTRAINT [FK_test2_test1] FOREIGN KEY
(
[ref_id]
) REFERENCES [dbo].[test1] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE
GOinsert into test1 values(1,'a dog')
insert into test1 values(2,'a cat')
insert into test1 values(3,'a fish')
insert into test1 values(4,'a camel')
go
insert into test2 values (1,2,'a cat')
insert into test2 values (2,3,'a fish')
insert into test2 values (3,4,'a camel')
goselect * from test1
select * from test2
解决方案 »
- 多次update表的问题
- 使用log explorer恢复数据,选择时间开始点时很后面,今天五点以后的才找得到,请高手指教,是不是日志文件坏了?
- (这次应该粘对图了)我的SQL2008连不上,如图
- SQL datetime 类型能不能支持到毫秒?
- 请教一个简单问题,关于MSSQL批量替换
- 請問,如何把表名或字段名做為參數傳遞到存儲過程中的from和where字句中加以運用?
- 只有一个mdf,恢复数据库时发现的问题
- 视图中数据与表中数据不同步问题
- !!!请教SQL语句!!!
- 我像风,像雨,越吹越飘越迷茫
- 在SQL SEVER 2000中怎么写TRANSACT-SQL 语句来创建表与表之间的关系???
- named pipes 问题
id descr
1 a dog
2 a cat
3 a fish
4 a camel
table test2 result is
id ref_id descr
1 2 a cat
2 3 a fish
3 4 a camelthen
update test1 set id=6 where descr='a cat'id descr
1 a dog
3 a fish
4 a camel
6 a cattable test2 result is
id ref_id descr
1 6 a cat ---has change to 6
2 3 a fish
3 4 a camelyou know?!