如何在 SQL Server 中创建外键？

Question

提问by mmattax

I have never "hand-coded" object creation code for SQL Server and foreign key decleration is seemingly different between SQL Server and Postgres. Here is my sql so far:

我从来没有为 SQL Server 编写过“手工编码”的对象创建代码，而且 SQL Server 和 Postgres 之间的外键声明似乎不同。到目前为止，这是我的 sql：

drop table exams;
drop table question_bank;
drop table anwser_bank;

create table exams
(
    exam_id uniqueidentifier primary key,
    exam_name varchar(50),
);
create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null,
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint question_exam_id foreign key references exams(exam_id)
);
create table anwser_bank
(
    anwser_id           uniqueidentifier primary key,
    anwser_question_id  uniqueidentifier,
    anwser_text         varchar(1024),
    anwser_is_correct   bit
);

When I run the query I get this error:

当我运行查询时，我收到此错误：

Msg 8139, Level 16, State 0, Line 9 Number of referencing columns in foreign key differs from number of referenced columns, table 'question_bank'.

消息 8139，级别 16，状态 0，第 9 行外键中的引用列数与表 'question_bank' 中的引用列数不同。

Can you spot the error?

你能发现错误吗？

Answer 1

采纳答案by John Boker

create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null,
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);

Answer 2

回答by AlexCuse

And if you just want to create the constraint on its own, you can use ALTER TABLE

如果您只想自己创建约束，则可以使用 ALTER TABLE

alter table MyTable
add constraint MyTable_MyColumn_FK FOREIGN KEY ( MyColumn ) references MyOtherTable(PKColumn)

I wouldn't recommend the syntax mentioned by Sara Chipps for inline creation, just because I would rather name my own constraints.

我不会推荐 Sara Chipps 提到的用于内联创建的语法，只是因为我宁愿命名我自己的约束。

Answer 3

回答by Sara Chipps

You can also name your foreign key constraint by using:

您还可以使用以下命令命名外键约束：

CONSTRAINT your_name_here FOREIGN KEY (question_exam_id) REFERENCES EXAMS (exam_id)

Answer 4

回答by Shavais

I like AlexCuse's answer, but something you should pay attention to whenever you add a foreign key constraint is how you want updates to the referenced column in a row of the referenced table to be treated, and especially how you want deletes of rows in the referenced table to be treated.

我喜欢 AlexCuse 的回答，但是每当添加外键约束时都应该注意的是，您希望如何更新要处理的引用表的行中的引用列，尤其是您希望如何删除引用中的行要处理的表。

If a constraint is created like this:

如果像这样创建约束：

alter table MyTable
add constraint MyTable_MyColumn_FK FOREIGN KEY ( MyColumn ) 
references MyOtherTable(PKColumn)

.. then updates or deletes in the referenced table will blow up with an error if there is a corresponding row in the referencing table.

.. 然后，如果引用表中有相应的行，则引用表中的更新或删除将因错误而爆炸。

That might be the behaviour you want, but in my experience, it much more commonly isn't.

这可能是您想要的行为，但根据我的经验，更常见的是不是。

If you instead create it like this:

如果您改为像这样创建它：

alter table MyTable
add constraint MyTable_MyColumn_FK FOREIGN KEY ( MyColumn ) 
references MyOtherTable(PKColumn)
on update cascade 
on delete cascade

..then updates and deletes in the parent table will result in updates and deletes of the corresponding rows in the referencing table.

..then 父表中的更新和删除将导致引用表中相应行的更新和删除。

(I'm not suggesting that the default should be changed, the default errs on the side of caution, which is good. I'm just saying it's something that a person who is creating constaints should always pay attention to.)

（我并不是建议应该更改默认值，出于谨慎的考虑，默认值是错误的，这很好。我只是说创建约束的人应该始终注意这一点。）

This can be done, by the way, when creating a table, like this:

顺便说一下，这可以在创建表时完成，如下所示：

create table ProductCategories (
  Id           int identity primary key,
  ProductId    int references Products(Id)
               on update cascade on delete cascade
  CategoryId   int references Categories(Id) 
               on update cascade on delete cascade
)

Answer 5

回答by Bijimon

create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null constraint fk_exam_id foreign key references exams(exam_id),
    question_text varchar(1024) not null,
    question_point_value decimal
);

--That will work too. Pehaps a bit more intuitive construct?

——那也行。Pehaps 更直观的构造？

Answer 6

回答by Md Ashikul Islam

If you want to create two table's columns into a relationship by using a query try the following:

如果要使用查询将两个表的列创建为关系，请尝试以下操作：

Alter table Foreign_Key_Table_name add constraint 
Foreign_Key_Table_name_Columnname_FK
Foreign Key (Column_name) references 
Another_Table_name(Another_Table_Column_name)

Answer 7

回答by Abhishek Jaiswal

To Create a foreign key on any table

在任何表上创建外键

ALTER TABLE [SCHEMA].[TABLENAME] ADD FOREIGN KEY (COLUMNNAME) REFERENCES [TABLENAME](COLUMNNAME)
EXAMPLE
ALTER TABLE [dbo].[UserMaster] ADD FOREIGN KEY (City_Id) REFERENCES [dbo].[CityMaster](City_Id)

Answer 8

回答by Vitor Silva

Like you, I don't usually create foreign keys by hand, but if for some reason I need the script to do so I usually create it using ms sql server management studio and before saving then changes, I select Table Designer | Generate Change Script

像你一样，我通常不手动创建外键，但如果出于某种原因我需要脚本来这样做，我通常使用 ms sql server management studio 创建它，然后在保存更改之前，我选择表设计器 | 生成更改脚本

Answer 9

回答by elkhayari abderrazzak

This script is about creating tables with foreign key and I added referential integrity constraint sql-server.

这个脚本是关于用外键创建表，我添加了参照完整性约束sql-server。

create table exams
(  
    exam_id int primary key,
    exam_name varchar(50),
);

create table question_bank 
(
    question_id int primary key,
    question_exam_id int not null,
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint question_exam_id_fk
       foreign key references exams(exam_id)
               ON DELETE CASCADE
);

Answer 10

回答by Stefan Steiger

Necromancing.
Actually, doing this correctly is a little bit trickier.

死灵法术。
实际上，正确地执行此操作有点棘手。

You first need to check if the primary-key exists for the column you want to set your foreign key to reference to.

您首先需要检查要设置外键引用的列的主键是否存在。

In this example, a foreign key on table T_ZO_SYS_Language_Forms is created, referencing dbo.T_SYS_Language_Forms.LANG_UID

在这个例子中，表 T_ZO_SYS_Language_Forms 上的一个外键被创建，引用 dbo.T_SYS_Language_Forms.LANG_UID

-- First, chech if the table exists...
IF 0 < (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'T_SYS_Language_Forms'
)
BEGIN
    -- Check for NULL values in the primary-key column
    IF 0 = (SELECT COUNT(*) FROM T_SYS_Language_Forms WHERE LANG_UID IS NULL)
    BEGIN
        ALTER TABLE T_SYS_Language_Forms ALTER COLUMN LANG_UID uniqueidentifier NOT NULL 

        -- No, don't drop, FK references might already exist...
        -- Drop PK if exists 
        -- ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT pk_constraint_name 
        --DECLARE @pkDropCommand nvarchar(1000) 
        --SET @pkDropCommand = N'ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT ' + QUOTENAME((SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
        --WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 
        --AND TABLE_SCHEMA = 'dbo' 
        --AND TABLE_NAME = 'T_SYS_Language_Forms' 
        ----AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms' 
        --))
        ---- PRINT @pkDropCommand 
        --EXECUTE(@pkDropCommand) 

        -- Instead do
        -- EXEC sp_rename 'dbo.T_SYS_Language_Forms.PK_T_SYS_Language_Forms1234565', 'PK_T_SYS_Language_Forms';


        -- Check if they keys are unique (it is very possible they might not be) 
        IF 1 >= (SELECT TOP 1 COUNT(*) AS cnt FROM T_SYS_Language_Forms GROUP BY LANG_UID ORDER BY cnt DESC)
        BEGIN

            -- If no Primary key for this table
            IF 0 =  
            (
                SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 
                AND TABLE_SCHEMA = 'dbo' 
                AND TABLE_NAME = 'T_SYS_Language_Forms' 
                -- AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms' 
            )
                ALTER TABLE T_SYS_Language_Forms ADD CONSTRAINT PK_T_SYS_Language_Forms PRIMARY KEY CLUSTERED (LANG_UID ASC)
            ;

            -- Adding foreign key
            IF 0 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms') 
                ALTER TABLE T_ZO_SYS_Language_Forms WITH NOCHECK ADD CONSTRAINT FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms FOREIGN KEY(ZOLANG_LANG_UID) REFERENCES T_SYS_Language_Forms(LANG_UID); 
        END -- End uniqueness check
        ELSE
            PRINT 'FSCK, this column has duplicate keys, and can thus not be changed to primary key...' 
    END -- End NULL check
    ELSE
        PRINT 'FSCK, need to figure out how to update NULL value(s)...' 
END

如何在 SQL Server 中创建外键？

提问by mmattax

采纳答案by John Boker

回答by AlexCuse

回答by Sara Chipps

回答by Shavais

回答by Bijimon

回答by Md Ashikul Islam

回答by Abhishek Jaiswal

回答by Vitor Silva

回答by elkhayari abderrazzak

回答by Stefan Steiger

相关推荐

最近更新

标签

如何在 SQL Server 中创建外键？

提问by mmattax

采纳答案by John Boker

回答by AlexCuse

回答by Sara Chipps

回答by Shavais

回答by Bijimon

回答by Md Ashikul Islam

回答by Abhishek Jaiswal

回答by Vitor Silva

回答by elkhayari abderrazzak

回答by Stefan Steiger

相关推荐

SQL 如何一次性从 PostgreSQL 表中删除所有 NOT NULL 约束

SQL 在 Oracle 中进行多行插入的最佳方法？

SQL 在唯一列中允许空值

Microsoft T-SQL 到 Oracle SQL 的翻译

相关推荐

最近更新

标签