[MSSQL] 테이블 기본키 PK 변경

1. 현재 제약조건 (키 설정) 확인

SELECT constraint_schema
     , table_name
     , constraint_name
     , column_name
     , ordinal_position
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
 WHERE table_name = '테이블명'

 

2. PK 삭제

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

 

3. PK 생성

 ALTER TABLE [테이블명] ADD CONSTRAINT [새 제약조건명] PRIMARY KEY ([컬럼명])

 

 

4. 예제

SELECT constraint_schema
     , table_name
     , constraint_name
     , column_name
     , ordinal_position
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
 WHERE table_name = 'test'

 ALTER TABLE dbo.test DROP CONSTRAINT pk_test
 ALTER TABLE dbo.test ADD CONSTRAINT [pk_test_projectId_id] PRIMARY KEY CLUSTERED ([projectId] ASC, [id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
                ON [PRIMARY]