MSSQL Identity 제거 하기
MSSQL Identity 제거 하려면 어렵습니다. Identity 속성인 컬럼은 제거할 수 없습니다. Identity 컬럼 속성을 제거 하려면 컬럼 임시로 만들고 컬럼을 복사후, 제거해서 같은 이름으로 지정하는 방법의 우회적인 방법이 있습니다. Identity 컬럼 속성을 제거 할 방법은 EM에서 Identity 체크를 해제해서 속성 제거를 할 수 있습니다. 하지만 패키지 프로그램의 경우 쿼리를 배포해야 하기 때문에 EM에서 할 수는 없기 때문에 쿼리를 작성해서 배포해야 합니다.
Identity 컬럼의 속성을 보면
sp_help 로 테이블의 LABORATORY_MATERIAL_ID 컬럼의 속성은 identity 속성으로 로우줄이 추가 될 수록 1씩 증가되게 되어 있습니다. 게시판이나 index을 증가하려는 목적이면 편리하지만 PK로 쓰기위해서는 identity 속성을 제거해야 편합니다.
MSSQL Identity 제거하는 방법
You will have to temporaily remove the IDENTITY attribute. This is the way that Enterprise Manager does it:
1. Copies all the data to a temporary table with the same structure, but without an IDENTITY attribute.
2. Deletes the old table.
3. Renames the temporary table to the original table name.
You can then do your update.
Then reverse the steps above.
Identity 컬럼속성을 제거하는 방법은 임시컬럼을 생성하고 임시컬럼에 Identity 속성을 가진 컬럼의 내용을 복사합니다. 복사후 PK을 제거후 (Identity 컬럼을 삭제시 제약조건때문에 삭제가 안될 수 있으므로) Identity 컬럼속성을 가진 컬럼을 삭제합니다. SP_ERNAME 로 임시컬럼의 명칭을 변경합니다. PK을 새로 지정해줍니다.
/* 테이블의 컬럼이 존재하는 지 확인후 실행합니다. */
IF NOT EXISTS (SELECT * FROM DBO.SYSCOLUMNS WHERE ID = OBJECT_ID(N'[DBO].[TB_LABORATORY_MATERIAL]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1 AND NAME ='COST')
BEGIN
/* 컬럼추가 */
ALTER TABLE TB_LABORATORY_MATERIAL ADD COST [NUMERIC] (9,0), APPLY_DATE [CHAR] (8) DEFAULT '' NOT NULL
/* 임시 컬럼 추가 */
ALTER TABLE TB_LABORATORY_MATERIAL ADD TEMP_COL1 INT DEFAULT (0) NOT NULL
/* 컬럼데이타 백업 */
update TB_LABORATORY_MATERIAL
set TEMP_COL1 = LABORATORY_MATERIAL_ID
/* PK 삭제 */
ALTER TABLE TB_LABORATORY_MATERIAL
DROP CONSTRAINT PK_TB_LABORATORY_MATERIAL
/* 기존컬럼 삭제 */
ALTER TABLE [dbo].TB_LABORATORY_MATERIAL DROP COLUMN LABORATORY_MATERIAL_ID
/* 컬럼이름 변경 */
EXEC SP_RENAME 'TB_LABORATORY_MATERIAL.TEMP_COL1', 'LABORATORY_MATERIAL_ID', 'COLUMN'
/* PK 변경 */
ALTER TABLE TB_LABORATORY_MATERIAL ADD CONSTRAINT PK_TB_LABORATORY_MATERIAL
PRIMARY KEY (LABORATORY_MATERIAL_ID, APPLY_DATE)
END
GO
실행후 경고문을 볼 수 있습니다.
경고문:
Caution: Changing any part of an object name could break scripts and stored procedures.
The COLUMN was renamed to 'LABORATORY_MATERIAL_ID'.
