/********************************************************************************************
-- Title : [2k5] PK, UQ 및 CL, NC 설정 예 스크립트
-- Reference : hanbiitbook.co.kr
-- Key word : primary key unique clustered nonclustered
********************************************************************************************/
USE tempDB;
go
CREATE TABLE tbl1
( a INT NOT NULL,
b INT,
c INT
);
go
ALTER TABLE tbl1
ADD CONSTRAINT pk_tbl1 PRIMARY KEY NONCLUSTERED (a)
CREATE TABLE tbl2
( a INT PRIMARY KEY,
b INT UNIQUE,
c INT UNIQUE,
d INT
);
go
EXEC sp_helpindex tbl2;
go
CREATE TABLE tbl3
( a INT PRIMARY KEY NONCLUSTERED,
b INT UNIQUE,
c INT UNIQUE,
d INT
);
go
EXEC sp_helpindex tbl3;
go
CREATE TABLE tbl4
( a INT PRIMARY KEY NONCLUSTERED,
b INT UNIQUE CLUSTERED,
c INT UNIQUE,
d INT
);
go
EXEC sp_helpindex tbl4;
go
CREATE TABLE tbl5
( a INT PRIMARY KEY NONCLUSTERED,
b INT UNIQUE CLUSTERED,
c INT UNIQUE CLUSTERED,
d INT
);
go
CREATE TABLE tbl5
( a INT PRIMARY KEY ,
b INT UNIQUE CLUSTERED ,
c INT UNIQUE ,
d INT
);
go
EXEC sp_helpindex tbl5;
go
CREATE TABLE userTbl
( userID nchar(8) NOT NULL PRIMARY KEY,
name nvarchar(10) ,
birthYear int ,
addr nchar(4)
);
go
INSERT INTO userTbl VALUES(N'PJS', N'박지성', 1983, N'서울');
INSERT INTO userTbl VALUES(N'PJY', N'박주영', 1986, N'경기');
INSERT INTO userTbl VALUES(N'JJJ', N'조재진', 1986, N'충북');
INSERT INTO userTbl VALUES(N'LCS', N'이천수', 1983, N'인천');
INSERT INTO userTbl VALUES(N'AJH', N'안정환', 1979, N'강원');
go
SELECT * FROM userTbl;
go
CREATE TABLE clusterTbl
( userID NCHAR(3) NOT NULL
, name NCHAR(3)
);
go
ALTER TABLE clusterTbl
ADD CONSTRAINT PK_clusterTbl_userID
PRIMARY KEY (userID);
go
CREATE TABLE nonclusterTbl
( userID NCHAR(3) NOT NULL
, name NCHAR(3)
);
go
ALTER TABLE nonclusterTbl
ADD CONSTRAINT UK_nonclusterTbl_userID
UNIQUE (userID);
go
CREATE UNIQUE INDEX uk_tbl5
ON tbl5(d);
go