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일 때만 지정할 수 있습니다.
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
T1 테이블의 자동 증가값을 확인 후 필요에 따라 재설정 한다.(ID 컬럼의 현재 ID값이 ID 컬럼의 최대값 보다 작은 경우 최대값으로 재설정 한다.)
DBCC CHECKIDENT('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