SQL Server：无法创建关系 [英] SQL Server: Unable to create relationship

问题描述

我试图创建一个具有一对多关系的表格。但似乎在个人中添加外键不起作用。我正在尝试将个人信息表链接到地址表？这个错误的解决方案是什么？

• 地址表已成功保存
>

• 个人表



无法创建关系FK_Personal_Address。


引用列Personal.ID是标识列的情况下，无法创建级联外键FK_Personal_Address。
不能创建约束。查看以前的错误。

解决方案

Person表中的主键大概是一个标识。这是一个自动递增的整数字段。

您需要在int类型的地址表中创建外键，而不是标识。它将保存与Person记录相对应的整数，但不希望外键自动递增。对于子表（地址）中的每个记录，您将为该外键设置一个特定的值，以指示它属于哪个父记录（Person）。

p>

  INSERT person（firstname，lastname）VALUES（'John'，'Smith'）
  

这将插入新的人员记录，并且字段 personid 将自动填充，因为它是IDENTITY字段。

现在要从John Smith插入一个地址，您需要知道他的 personid 。例如：

   - 例如说，John Smith的personid是55 
 INSERT address（personid，street ，城市）VALUES（55，'High Street'，'London'）
  

所以在 person 表中，personid是自动生成的，但是在地址表中指定了与现有人匹配的值。这是一个外键的全部。

没有关于您的模式的更多信息，很难猜测问题。

I was trying to create a table that has a one to many relationships. but it seems that adding a foreign key in Personal is not working. I am trying to link a Personal Information table to a address table? what is the solution for this error?

• Address table saved successfully
• Personal table

Unable to create relationship 'FK_Personal_Address'.
Cascading foreign key 'FK_Personal_Address' cannot be created where the referencing column 'Personal.ID' is an identity column. Could not create constraint. See previous errors.

解决方案

The primary key in the Person table is presumably an identity. This is an auto-incrementing integer field.

You need to make the foreign key in the address table of type int, not identity. It will hold integers which correspond to Person records, but you don't want the foreign key to auto-increment. For each record in the child table (address) you will set a specific value for the foreign key indicating to which parent record (Person) it belongs.

Example:

INSERT person (firstname, lastname) VALUES ('John', 'Smith')

This will insert the new person record and the field personid will be filled automatically because it is an IDENTITYfield.

Now to insert an address from John Smith you need to know his personid. For example:

-- Say, for example, personid of John Smith is 55
INSERT address (personid, street, city) VALUES (55, 'High Street', 'London')

So in the person table the personid is generated automatically but in the address table you specify the value that matches an existing person. That's the whole point of a foreign key.

Without more information about your schema it's hard to guess the problem.

这篇关于SQL Server：无法创建关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！