27,579
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表: [a]
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([a] [int],[b] [int],primary key(a,b))
INSERT INTO [a]
SELECT '1','2'
--> 生成测试数据表: [b]
IF OBJECT_ID('[b]') IS NOT NULL
DROP TABLE [b]
GO
CREATE TABLE [b] ([c] [int],[d] [int],[e] [int],foreign key(d,c) references a(a,b))
INSERT INTO [b]
SELECT '2','1','3'
-->SQL查询如下:
SELECT * FROM [a]
SELECT * FROM [b]
/*
a b
----------- -----------
1 2
(1 行受影响)
c d e
----------- ----------- -----------
2 1 3
(1 行受影响)
*/
这跟参照的位置有关系。--> 生成测试数据表: [a]
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([a] [int],[b] [int],primary key(a,b))
INSERT INTO [a]
SELECT '1','2'
--> 生成测试数据表: [b]
IF OBJECT_ID('[b]') IS NOT NULL
DROP TABLE [b]
GO
CREATE TABLE [b] ([c] [int],[d] [int],[e] [int],foreign key(c,d) references a(a,b))
INSERT INTO [b]
SELECT '1','2','3'
-->SQL查询如下:
SELECT * FROM [a]
SELECT * FROM [b]
/*
a b
----------- -----------
1 2
(1 行受影响)
c d e
----------- ----------- -----------
1 2 3
(1 行受影响)
*/
CREATE TABLE dbo.a
(
a int NOT NULL,
b int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.a ADD CONSTRAINT
PK_a_1_1 PRIMARY KEY CLUSTERED
(
a,
b
) ON [PRIMARY]
GO
CREATE TABLE dbo.b
(
c int NOT NULL,
d int NOT NULL,
e int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.b ADD CONSTRAINT
PK_b_1_1 PRIMARY KEY CLUSTERED
(
c,
d,
e
) ON [PRIMARY]
GO
GO
ALTER TABLE dbo.b ADD CONSTRAINT
FK_a_b FOREIGN KEY
(
c,
d
) REFERENCES dbo.a
(
a,
b
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
--drop table a
--drop table b