如何创建带标识列的表

我有一个现有的表，我要吹走，因为我没有创建它与 ID列设置为表的标识列。

使用 SQLServerManagementStudio，我编写了现有表的 "Create To..."脚本，并得到以下结果:

CREATE TABLE [dbo].[History](
[ID] [int] NOT NULL,
[RequestID] [int] NOT NULL,
[EmployeeID] [varchar](50) NOT NULL,
[DateStamp] [datetime] NOT NULL,
CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

我的问题是，我应该如何修改这个 SQL，使得我的结果表中的 ID列被设置为 身份？

391636 次浏览
小开 
[id] [int] IDENTITY(1,1) NOT NULL,

当然，因为您是在 SQLServerManagementStudio 中创建表，所以您可以使用表设计器来设置标识规范。

enter image description here

小开
最佳答案 
CREATE TABLE [dbo].[History](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RequestID] [int] NOT NULL,
[EmployeeID] [varchar](50) NOT NULL,
[DateStamp] [datetime] NOT NULL,
CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
) ON [PRIMARY]
小开

This has already been answered, but I think the simplest syntax is:

CREATE TABLE History (
ID int primary key IDENTITY(1,1) NOT NULL,
. . .

The more complicated constraint index is useful when you actually want to change the options.

顺便说一下，我更喜欢将这样的列命名为 HistoryId，因此它与外键关系中的列名相匹配。

小开

唯一键允许最大2个 NULL 值。解释:

create table teppp
(
id int identity(1,1) primary key,
name varchar(10 )unique,
addresss varchar(10)
)


insert into teppp ( name,addresss) values ('','address1')
insert into teppp ( name,addresss) values ('NULL','address2')
insert into teppp ( addresss) values ('address3')


select * from teppp
null string , address1
NULL,address2
NULL,address3

如果尝试插入与下面相同的值:

insert into teppp ( name,addresss) values ('','address4')
insert into teppp ( name,addresss) values ('NULL','address5')
insert into teppp ( addresss) values ('address6')

每次你都会得到这样的错误:

违反 UNIQUE KEY 约束‘ UQ _ _ teppp _ _ 72E12F1B2E1BDC42’。无法在对象‘ dbo.teppp’中插入重复键。
The statement has been terminated.


提交答案