SQL Server使用外键创建表

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表？

2 个解决方案

#1

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.

如果是这种情况，请考虑M：N与其他PersonAddress表的关系。

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?

否则，如果不是这种情况，我会问自己“如果没有这个人，你是否更有可能没有地址或地址的人？”目标是确定你是否应该使用Person表存储AddressID或者使用Address表存储PersonID ？

#2

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 SavePerson Stored proc.

我会这样做是因为，当你添加一个具有指定StreetAddress，City和Zip的人时，你可以在SavePerson存储过程中执行此操作。

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 clustered index 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中的地址组，或者在一个城市中的所有地址，请将此设为聚簇索引...

#1