MSSQL alter table

출처 : http://21cchaos.blog.me/70120010190

 

[ MSDN - ALTER TABLE http://msdn.microsoft.com/ko-kr/library/ms190273.aspx ]

 

 

ALTER TABLE(Transact-SQL) : 

열과 제약 조건을 변경, 추가 또는 삭제하거나 파티션을 재할당하거나 

제약 조건과 트리거를 설정 또는 해제하여 테이블 정의를 수정합니다.

 

테이블 : T1

컬럼 : col1

ex1 : 인터넷 검색.

ex2 : 현재 내가 사용중인것.(부 : SQL SERVER 2005, 주 : SQL SERVER 2008 R2)

 

 

1. 컬럼 추가

 

ALTER TABLE 테이블명 ADD 컬럼명 

ex1> 

ALTER TABLE T1 ADD col1

 

ex2>

ALTER TABLE T1 ADD col1 NVARCHAR(20) NOT NULL

 

ex2> T1 테이블에 col1 칼럼이 없는 경우 col1 컬럼을 추가 한다.

 

DECLARE @table_id int

DECLARE @nCount int

 

 

SET @table_id = object_id('T1') 

SELECT @nCount=COUNT(name) FROM syscolumns WHERE id = @table_id and name='col1'

IF @nCount = 0

ALTER TABLE T1 ADD col1 NVARCHAR(20) NOT NULL;

 

2. 컬럼 수정

 

ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입 제약조건) 

ex1>

ALTER TABLE T1 MODIFY (col1 NVARCHAR(20) NOT NULL)

 

ex2>

ALTER TABLE T1 ALTER COLUMN col1 NVARCHAR(20) NOT NULL

 

 

3. 컬럼 삭제

 

ALTER TABLE 테이블명 DROP 컬럼명

ex1>

ALTER TABLE T1 DROP col1

 

ex2>

ALTER TABLE T1 DROP COLUMN col1

 

 

4. 컬럼 제약조건 추가

 

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건 

ex1> 

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (col1)

 

ex2>

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY col1

ex2>

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED  

(

[col1] ASC

)

 

ex2>

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED 

(

[col1] ASC,

[col2] ASC

)

5. 컬럼 제약조건 제거

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건

 

ex2>

DECLARE @nCount int

 

SELECT @nCount=COUNT(CONSTRAINT_NAME) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 

WHERE TABLE_NAME = 'T1'

IF @nCount > 0

ALTER TABLE T1 DROP CONSTRAINT PK_T1

 

 

 

6. 컬럼명 수정

 

EXEC SP_RENAME 기존컬럼명, 변경할 컬럼명

ex> 

EXEC SP_RENAME 'T1', 'T2'

7. 자동 증가 컬럼에 Insert 하기

테이블에 값을 넣을때 자동 증가 컬럼에 값을 넣으려 할때면 아래와 같은 오류가 발생한다.

IDENTITY_INSERT가 OFF로 설정되면 'XXX' 테이블의 ID 열에 명시적 값을 삽입할 수 없습니다.

이럴 경우 해당 설정을 ON으로 설정 후 값을 넣은뒤 다시 OFF로 설정해 주면 된다.

*멍청한 얘기지만 당연히 INSERT 이외의 UPDATE는 이 옵션과 무관하다.(이미 옵션에_INSERT인것을...)

  자동증가 컬럼은 업데이트를 할 수 없다. 한참을 삽질을 하고 나서야 DELETE - INSERT 작업으로 처리했다.

 

col1 이 자동증가 컬럼인 경우.

SET IDENTITY_INSERT  테이블명 ON 또는 OFF

SET IDENTITY_INSERT  T1 ON

INSERT INTO  T1(col1) VALUES(값)

SET IDENTITY_INSERT  T1 OFF

-- 오류가 발생하는 쿼리 입니다.

SET IDENTITY_INSERT  T1 ON

INSERT INTO  T1 SELECT * FROM T1_BACKUP

SET IDENTITY_INSERT  T1 OFF

테이블 'T1'에 있는 ID 열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON일 때만 지정할 수 있습니다.

INSERT INTO ~ SELECT문으로 입력시 옵션을 설정 하더라도 오류가 발생하는 경우

SELECT문에 * 로 한 경우 일때는 컬럼명을 명시적으로 작성해 주면 정상적으로 입력이 된다.

SET IDENTITY_INSERT  T1 ON

INSERT INTO  T1(col1) SELECT col1 FROM T1_BACKUP

SET IDENTITY_INSERT  T1 OFF

https://msdn.microsoft.com/ko-kr/library/ms176057(v=sql.120).aspx

DBCC CHECKIDENT(Transact-SQL)

msdn.microsoft.com

 T1 테이블의 자동 증가값을 확인 후 필요에 따라 재설정 한다.(ID 컬럼의 현재 ID값이 ID 컬럼의 최대값 보다 작은 경우 최대값으로 재설정 한다.)

DBCC CHECKIDENT('T1')

초기화값으로 T1 테이블의 자동 증가값을 초기화 한다.

DBCC CHECKIDENT('T1',RESEED,초기화값)

T1 테이블의 자동 증가값을 확인한다.(현재 IDENT 확인)

DBCC CHECKIDENT('T1',NORESEED)

8. 컬럼 기본값 설정

ALTER TABLE  T1 ADD  CONSTRAINT DF_T1_col1   DEFAULT ((0)) FOR col1

 

IF EXISTS (SELECT name FROM sys.objects WHERE name = N' DF_T1_col1 ' AND type=N'D' )

ALTER TABLE   T1 DROP CONSTRAINT DF_T1_col1

ELSE

ALTER TABLE   T1  ADD  CONSTRAINT   DF_T1_col1  DEFAULT ((0)) FOR col1

 

[출처] [MS SQL] ALTER TABLE|작성자 BK