CREATE TABLE (Transact-SQL) | Microsoft Docs
ドキュメントでは、CREATE TABLE での FOREIGN KEY 指定はテーブルで指定する方法と列で指定する方法の2パターンあります。
テーブル
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{
{ PRIMARY KEY | UNIQUE }
{
NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
| NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )
}
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
| CHECK ( logical_expression )
}
列(通常の列と計算列)
<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ]
REFERENCES referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE } ]
[ ON UPDATE { NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
]
テーブルで指定するときは、自分の列と相手の列を指定する、
列で指定するときは、相手の列を指定する感じです。
ところで次の3つのクエリは全部同じ結果になります。
1: table_constraint で指定した場合
drop table if exists [Child] drop table if exists [Parent] create table [Parent] ( [Id] int not null primary key ) create table [Child] ( [Id] int not null primary key, [ParentId] int not null, [Remark] nvarchar(max), foreign key ([ParentId]) references [Parent]([Id]) )
2: column_constraint で指定した場合
drop table if exists [Child] drop table if exists [Parent] create table [Parent] ( [Id] int not null primary key ) create table [Child] ( [Id] int not null primary key, [ParentId] int not null foreign key references [Parent]([Id]), [Remark] nvarchar(max) )
3: column_constraint で指定した場合(他の列で指定している!)
drop table if exists [Child] drop table if exists [Parent] create table [Parent] ( [Id] int not null primary key ) create table [Child] ( [Id] int not null primary key foreign key([ParentId]) references [Parent]([Id]), [ParentId] int not null, [Remark] nvarchar(max) )
3: のパターンは、ドキュメントでは指定出来ないはずなんだけどどうなんですかね?
バグだったとしても実害はないでしょうけど。