sqlserver建立1对1的正确方法

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表

转自