在SQL Server中实现一对一或一对关系

CREATE TABLE Person (
    PersonID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

CREATE TABLE Address (
    AddressID INT PRIMARY KEY,
    Street VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    ZipCode VARCHAR(10),
    PersonID INT,
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);

CREATE VIEW PersonAddress AS
SELECT p.PersonID, p.FirstName, p.LastName, a.AddressID, a.Street, a.City, a.State, a.ZipCode
FROM Person p
LEFT JOIN Address a ON p.PersonID = a.PersonID;

CREATE PROCEDURE InsertPersonAndAddress
    @PersonID INT,
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @AddressID INT,
    @Street VARCHAR(100),
    @City VARCHAR(50),
    @State VARCHAR(50),
    @ZipCode VARCHAR(10)
AS
BEGIN
    INSERT INTO Person (PersonID, FirstName, LastName)
    VALUES (@PersonID, @FirstName, @LastName);

    INSERT INTO Address (AddressID, Street, City, State, ZipCode, PersonID)
    VALUES (@AddressID, @Street, @City, @State, @ZipCode, @PersonID);
END;