一对一关系的典型设计是帐号表和帐号扩展信息表,一个帐号记录唯一对应扩展信息表中的一条记录,在没有深入了解一对一关系时,容易设计出以下错误模型
--创建基表
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].[Acc