GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dept]
(
deptno VARCHAR(2) NOT NULL ,
dname VARCHAR(10) NOT NULL ,
loc VARCHAR(10) NOT NULL ,
CONSTRAINT PK_dept PRIMARY KEY CLUSTERED ( deptno ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70 ) ON [FG_EP]
)
ON [FG_EP]
GO
CREATE TABLE emp
(
empno CHAR(4) NOT NULL ,
ename VARCHAR(10) NOT NULL ,
job VARCHAR(10) NOT NULL ,
mgr CHAR(4) NOT NULL ,
hiredate DATETIME NOT NULL ,
sal SMALLMONEY NOT NULL ,
comm SMALLMONEY NOT NULL ,
deptno VARCHAR(2) NOT NULL ,
CONSTRAINT PK_emp PRIMARY KEY CLUSTERED ( empno ASC ) ,
CONSTRAINT CK_sal CHECK ( sal >0 )
)
ON [FG_EP]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE emp WITH CHECK ADD CONSTRAINT FK_deptno FOREIGN KEY(deptno)
REFERENCES [dept] (deptno)
GO
ALTER TABLE emp CHECK CONSTRAINT FK_deptno
GO
还可以使用函数和触发器等方式变相实现自增列...
我举个触发器的例子：
--环境
create
table
test_5
(
id
int
primary
key
not
null,
value
int
)
--保存最大序列值的表
create
table
Sequence
(
rn
int
)
insert
Sequence
select
0
go
create
trigger
tr_test_5
on
test_5
Instead
of
insert
as
begin
declare
@n
int
update
Sequence
set
rn=rn+@@rowcount,@n=rn
insert
test_5
select
@n+row_number()over(order
by
getdate()),value
from
inserted
end
go
insert
test_5(value)
select
1
union
select
2
union
select
3
select
*
from
test_5
/*
id
value
-----------
-----------
1
1
2
2
3
3*/
第二个问题：
给你个例子：
--创建测试表
CREATE
TABLE
t1(ID
int
IDENTITY,A
int)
GO
--插入记录
INSERT
t1
VALUES(1)
GO
--1.
将IDENTITY(标识)列变为普通列
ALTER
TABLE
t1
ADD
ID_temp
int
GO
UPDATE
t1
SET
ID_temp=ID
ALTER
TABLE
t1
DROP
COLUMN
ID
EXEC
sp_rename
N't1.ID_temp',N'ID',N'COLUMN'
INSERT
t1
VALUES(100,9)
GO
--2.
将普通列变为标识列
CREATE
TABLE
t1_temp(ID
int,A
int
IDENTITY)
SET
IDENTITY_INSERT
t1_temp
ON
INSERT
t1_temp(ID,A)
SELECT
*
FROM
t1
SET
IDENTITY_INSERT
t1_temp
OFF
DROP
TABLE
T1
GO
EXEC
sp_rename
N't1_temp',N't1'
INSERT
t1
VALUES(109999)
GO
--显示处理结果
SELECT
*
FROM
t1
/*--结果:
ID
A
-----------------
-----------
1
1
100
9
109999
10
--*/
