添加一个sql表的唯一约束作为对另一个sql表的外键引用 [英] Add a unique constraint of a sql table as foreign key reference to an another sql table

问题描述

如何在sql server 2005中添加一个sql表的唯一约束作为对另一个sql表的外键引用

how to add a unique constraint of a sql table as foreign key reference to an another sql table in sql server 2005

推荐答案

为了添加 FK 约束(在子表到父表中)，您必须向关系的父表列添加唯一约束.
其余的都是可选的或与 FK 无关:

In order to add FK constraint (in child table to parent table) you have to add unique constraint to parent table columns of relationship.
All the rest is optional or has nothing to do with FK:

• 不需要任何主键
• 子表列不需要唯一性

父表(在这种 FK 关系中)经常被调用(包括被 SSMS)作为主键表，但 PK 不是必须的，父表中的唯一键/约束就足够了(因为 PK 是唯一的，这是特殊情况父表中的唯一约束).

The parent table (in such FK relation) is frequently called (including by SSMS) as Primary Key table but PK is not must, unique key/constraint in parent table is enough (as PK is unique, it is particular case of unique constraint in parent table).

从 Matt 的回答，这让初学者感到困惑，
并将它们重新创建为

Drop TableA and TableB from answer by Matt, which is confusing for beginners,
and recreate them as

CREATE TABLE parentB--TableB 
(
    PK1 INT NOT NULL,
    PK2 INT NOT NULL,
    --I would not have additional non-referenced data in parent table, 
    --rather in child table
    --SomeData VARCHAR(1000),

    --CONSTRAINT PK_TableB PRIMARY KEY CLUSTERED (PK1, PK2)
)

CREATE TABLE childA--TableA 
(
    --PK INT, -- NOT NULL,
    FK1 INT-- NOT NULL,  -- Or NULL, if you''d rather.
    FK2 INT --NOT NULL --,
    , SomeData VARCHAR(1000)
    --CONSTRAINT PK_TableA PRIMARY KEY CLUSTERED (PK),
    --CONSTRAINT FK_TableA_FK1FK2 FOREIGN KEY (FK1, FK2) REFERENCES TableB (PK1, PK2),
    --CONSTRAINT Cons2cols UNIQUE(FK1, FK2)
)

现在，为了添加 FK

Now, in order, to add FK

ALTER TABLE childA 
ADD 
--constraint FK1_childA 
--this is optional, if one needs to add his own custom name
FOREIGN KEY (FK1) REFERENCES parentB(PK1); 

您应该首先在父表列中的相应引用列上创建唯一约束:

you should first create unique constraint on corresponding referenced column in parent table column:

ALTER TABLE parentB ADD 
--CONSTRAINT YourUniqueName --uncomment for adding your own name to constraint
UNIQUE(PK1) 

<小时>

同样适用于 2 列外键约束
(首先，你需要父表中对应的唯一约束):

---

Similarly for 2 columns foreign key constraint
(first, you need corresponding unique constraint in parent table):

ALTER TABLE parentB ADD 
--CONSTRAINT YourUniqueName --for adding your own name to unique constraint
UNIQUE(PK1,PK2)  

ALTER TABLE childA 
ADD 
--constraint yourUniqueName --uncomment for adding your own name to FK constraint
FOREIGN KEY (FK1, FK2) REFERENCES parentB(PK1, PK2); 

这篇关于添加一个sql表的唯一约束作为对另一个sql表的外键引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！