[MSSQL] Constraint 때문에 Alter / Drop 실행이 안될때..

Default값을 변경하려고 보니 constraint때문에 변경이 안되고 에러 메세지만 뿌려준다.
그래서 어차피 이제 막 추가했던 컬럼이니 드랍이나 하자 했는데..
이역시 쉽게 되질 않아 방법을 찾아보게 되었다.

[출처 : http://www.cyworld.com/candyshop52/2855015]

출처의 필자에 의하면 constraint만 삭제했다가, alter나 drop후에 다시 생성해주라는건데..
constraint를 삭제해도 alter가 잘 이루어지지 않았다..
결국 그냥 constraint와 컬럼 모두 드랍하는것으로 결정!

ALTER TABLE TB_GEOFENCE_ALERT DROP CONSTRAINT DF_TB_GEOFENCE_ALERT_LAST_IN_OUT_STATUS;
ALTER TABLE TB_GEOFENCE_ALERT DROP COLUMN LAST_IN_OUT_STATUS;

ALTER TABLE TB_GEOFENCE_ALERT ADD LAST_IN_OUT_STATUS CHAR(1)    NOT NULL CONSTRAINT DF_TB_GEOFENCE_ALERT_LAST_IN_OUT_STATUS DEFAULT ('X');

평소에 CONSTRAINT를 명시하고 컬럼을 생성하는 버릇때문에 그냥 저 상태로 돌리기만 하면 드랍후에 깔끔히 신규 컬럼이 생성되지만, constraint를 명시하지 않고 컬럼을 생성한 경우는 출처의 필자 방식을 따르면 된다.

============================참고=====================================
exec sp_helpconstraint  [테이블명]

-- 실행을 하면 먼저 해당 테이블에 설정되어있는 Constraints 가 조회된다

  DECLARE @sysname sysname
 DECLARE @sql varchar(1000)

 IF EXISTS (SELECT name FROM sysobjects
           WHERE type ='D' AND name like '%[테이블명]__[필드명]%')  -- '%mok__c%'
   BEGIN
      SET @sysname = (SELECT name FROM sysobjects
                  WHERE type ='D' AND name like '%[테이블명]__[필드명]%' and xtype = [제약조건타입])

                                                               --name = 'DF__test_mok__c__6C21DDE7'

      set @sql = 'ALTER TABLE [테이블명] DROP CONSTRAINT ' + @sysname
      exec (@sql)  

   END
 GO

-- 제약조건이 삭제 되었으면 기존에 제약조건이 걸려있는 필드들에 대한 변경작업이 가능!! 

-- 제약 조건 삭제 후 다시 alter table 을 이용하여 제약조건을 만들면 된다 

-- 참고로 위의 예제는 Constraint를 명시적으로 NAME을 주지 않고 생성했을 경우에 해당하는 경우이다. 명시적으로 하였을 경우에는 like절을 쓰지 않고 올바른 NAME을 주어야 한다

=====================================================================

Default 변경만을 원할때는 Constraint를 drop하고, 새로 constraint를 add하는 개념으로 해야된다.
(이걸 모르고 아무생각없이 alter column만 하려고 했으니;;; 수정이 안됐었다능;;)

ALTER TABLE TB_USER DROP CONSTRAINT DF_TB_USER_USE_DISPLAY_MAIN;

ALTER TABLE TB_USER ADD CONSTRAINT DF_TB_USER_USE_DISPLAY_MAIN DEFAULT '1' FOR USE_DISPLAY_MAIN;


잘못 생각했던 수정쿼리의 예 : ALTER TABLE TB_USER   ALTER COLUMN USE_DISPLAY_MAIN INT NOT NULL CONSTRAINT DF_TB_USER_USE_DISPLAY_MAIN DEFAULT '1';

[출처] http://blog.naver.com/astris7?Redirect=Log&logNo=70082614156



[참고]

SYSOBJECT 활용 쿼리문 모음

USE [DATABASE]
SELECT 'DELETE FROM ' + [NAME] FROM SYS.SYSOBJECTS WHERE [xtype] = 'U'

xtype 는 Char(2) 데이터형식 이며 개체 유형을 나타낸다.

 

xtype = 'C'    -- CHECK 제약조건

xtype = 'D'    -- 기본값 또는 DEFAULT 제약조건

xtype = 'F'    -- FOREIGN KEY 제약조건

xtype = 'L'    --  로그

xtype = 'FN'    -- 스칼라 함수

xtype = 'IF'    -- 인라인 테이블 함수

xtype = 'P'    -- 저장 프로시저

xtype = 'PK'    -- PRIMARY KEY 제약조건 (K 유형)

xtype = 'RF'    -- 복제 필터 저장 프로시저

xtype = 'S'    -- 시스템 테이블

xtype = 'TF'    -- 테이블 함수

xtype = 'TR'    -- 트리거

xtype = 'U'    -- 사용자 테이블

xtype = 'UQ'    -- UNIQUE 제약조건 (K 유형)

xtype = 'V'    -- 뷰

xtype = 'X'    -- 확장 저장 프로시저

[출처] MSSQL에서 모든 테이블명 및 객체 조회 (스크랩자바)