Sql 一对一关系

一对一关系的典型设计是帐号表和帐号扩展信息表，一个帐号记录唯一对应扩展信息表中的一条记录，在没有深入了解一对一关系时，容易设计出以下错误模型

--创建基表
CREATE TABLE [dbo].[Account] --帐号表
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[AccountExID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [FK_Account_AccountEx_AccountExID] FOREIGN KEY ([AccountExID]) REFERENCES [dbo].[AccountEx] ([ID])
GO

CREATE TABLE [dbo].[AccountEx] --帐号扩展信息表
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Sex] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Age] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Remark] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
[AccountID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccountEx] ADD CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccountEx] ADD CONSTRAINT [FK_AccountEx_Account_AccountID] FOREIGN KEY ([AccountID]) REFERENCES [dbo].[Account] ([ID])
GO


--插入数据
DECLARE @CurAccountID INT, @CurAccountExID INT
INSERT INTO dbo.Account(Name, AccountExID) VALUES('andy', NULL)
SET @CurAccountID = SCOPE_IDENTITY()
INSERT INTO dbo.AccountEx(Sex, Age, Remark, AccountID) VALUES('man', '23', 'nothing', @CurAccountID)
SET @CurAccountExID = SCOPE_IDENTITY()
UPDATE dbo.Account SET AccountExID = @CurAccountExID WHERE ID = @CurAccountID

--查询数据
SELECT * FROM dbo.Account
SELECT * FROM dbo.AccountEx

--删除数据
DECLARE @AccountID INT, @AccountExID INT
SET @AccountID = 4
SELECT @AccountExID = ID FROM dbo.AccountEx WHERE AccountID = @AccountID

UPDATE dbo.AccountEx SET AccountID = NULL WHERE AccountID = @AccountID --更新外键表外键字段为NULL
DELETE FROM dbo.Account WHERE ID = @AccountID --删除主键表数据
DELETE FROM dbo.AccountEx WHERE ID = @AccountExID --删除外键表数据

Account表中有一个外键AccountExID关联到AccountEx表，AccountEx表中有一个外键AccountID关联联Account表
以上是对一对一关系的最初理解，在之后的实作中发现，这是一种错误的设计，一对一关系的正确设计应该如下

CREATE TABLE [dbo].[Account] --帐号表
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [PK_dbo.Account] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AccountEx] --帐号信息扩展表
(
[AccountID] [int] NOT NULL,
[Sex] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
[Age] [int] NOT NULL,
[Remark] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccountEx] ADD CONSTRAINT [PK_dbo.AccountEx] PRIMARY KEY CLUSTERED  ([AccountID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AccountID] ON [dbo].[AccountEx] ([AccountID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccountEx] ADD CONSTRAINT [FK_dbo.AccountEx_dbo.Account_AccountID] FOREIGN KEY ([AccountID]) REFERENCES [dbo].[Account] ([ID])
GO


--插入数据
DECLARE @CurAccountID INT
INSERT INTO Account(Name) VALUES('user01')
SET @CurAccountID = SCOPE_IDENTITY()
INSERT INTO AccountEx(AccountID, Sex, Age, Remark) VALUES(@CurAccountID, 'man', 26, 'goodman')

--查询数据
SELECT * FROM dbo.Account
SELECT * FROM dbo.AccountEx

Account表中没有与AccountEx表关联的外键
AccountEx表中AccountID字段首先是一个主键，具有唯一性约束，这个主键不是自增长的，而是由Account数据插入时维护，即AccountID与Accoutn表的ID保持一致
同时它是一个外键，用于关联到Account表