SQL Server 使用外键创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15937590/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Create Table with Foreign Key
提问by Kairan
I want to validate the proper handling of Foreign keys in table. Here are my two tables being created below. It is possible that a person may not have an address listed so I want it to be null. Otherwise I would like to reference a primary key from the address table and store it in the Person table as a foreign key. It is also possible that we may have an address object without a person.
我想验证表中外键的正确处理。这是我在下面创建的两个表。一个人可能没有列出地址，所以我希望它为空。否则，我想从地址表中引用一个主键，并将其作为外键存储在 Person 表中。我们也可能有一个没有人的地址对象。
Table for a Person:
一个人的表：
CREATE TABLE Person
(
PersonID int IDENTITY PRIMARY KEY,
FName varchar(50) NULL,
MI char(1) NULL,
LName varchar(50) NULL,
AddressID int FOREIGN KEY REFERENCES Address(AddressID) NULL,
)
Table for Address:
地址表：
CREATE TABLE Address
(
AddressID int IDENTITY PRIMARY KEY,
Street varchar(60) NULL,
City varchar(50) NULL,
State varchar(2) NULL,
Zip varchar(10)NULL,
Intersection1 varchar(60) NULL,
Intersection2 varchar(60) NULL,
)
Also Q2 I have never worked with triggers but I am assuming the way to handle an insert would be to use a stored procedure to insert the address first, get the primary key, then pass it to a stored procedure to insert into Person table?
另外 Q2 我从未使用过触发器，但我假设处理插入的方法是使用存储过程首先插入地址，获取主键，然后将其传递给存储过程以插入到 Person 表中？
采纳答案by Nath_Math
Question for you: is it possible that more than one person lives on the same address? Also, is it possible that one person lives on more than one address?
给你的问题：是否有可能不止一个人住在同一个地址？另外，是否有可能一个人住在多个地址？
If this is the case consider M:N relationship with additional PersonAddress table.
如果是这种情况，请考虑与附加 PersonAddress 表的 M:N 关系。
Otherwise if this is not the case I'd ask myself “is it more likely that you'll have person without an address or address without the person?” Goal is to determine if you should store AddressID with Person table or PersonID with Address table?
否则，如果情况并非如此，我会问自己“您是否更有可能拥有没有地址的人或没有该人的地址？” 目标是确定您应该将 AddressID 与 Person 表一起存储还是将 PersonID 与 Address 表一起存储？
回答by Charles Bretana
I would change Address like this:
我会像这样更改地址：
CREATE TABLE Address
(
AddressID int IDENTITY,
Street varchar(60) NULL,
City varchar(50) NULL,
State varchar(2) NULL,
Zip varchar(10)NULL,
Intersection1 varchar(60) NULL,
Intersection2 varchar(60) NULL,
)
Alter Table Address Add Constraint
PK_Addresses Primary Key Clustered
(City Asc, Zip Asc, Street asc)
Create Unique NonClustered Index IX_Address_Key
On Address{AddressId)
I would do this because, then, when you add a person with a specified StreetAddress, City and Zip,
You can do this in a SavePersonStored proc.
我会这样做是因为，当您添加具有指定 StreetAddress、City 和 Zip 的人员时，您可以在SavePersonStored proc 中执行此操作。
If Exists (Select * From Address
Where Street = @Street
And City = @city
And Zip = @zip)
Begin
Select @AddressId = AddressId
From Address
Where Street = @Street
And City = @city
And Zip = @zip
End
Else Begin
Insert Address(Street, City, State, Zip)
Values(@street, @city, @state, @zip)
Set @AddressId = Scope_Identity()
End
And then use the value of the t-sql variable @AddressId in your Insert into the Person table. You would still use the AddressId for joins in other SQL statements, but you have a Primary Key on City, Zip and Street address that will prevent you from inserting duplicate addresses in the Address table. Make this the clusteredindex in case you may need to retrieve or process groups of addresses that are all in one zip, or all in one city...
然后在您的 Insert 到 Person 表中使用 t-sql 变量 @AddressId 的值。您仍将在其他 SQL 语句中使用 AddressId 进行连接，但您在 City、Zip 和 Street 地址上有一个主键，可以防止您在 Address 表中插入重复的地址。将此作为聚集索引，以防您可能需要检索或处理全部在一个 zip 中或全部在一个城市中的地址组...
