Mssql 제약조건 변경 - mssql jeyagjogeon byeongyeong

* 컬럼삭제
ALTER TABLE 테이블명
DROP COLUMN 컬럼명

예제) JUSO3 제약조건 조회
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM        
         USER_CONSTRAINTS;

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

예제) JUSO4의 제약조건 제거
SQL> ALTER TABLE JUSO4
  2  DROP CONSTRAINT SYS_C003015;

예제) JUSO4의 테이블 삭제
SQL> DROP TABLE JUSO4;

예제) JUSO1의 데이터 삭제
SQL> DELETE FROM JUSO1;

---------------------------------------------------------------------------
# 제약 조건 추가하기
예제) JUSO1에 JOB  VARCHAR2(10) 컬럼 추가
SQL> ALTER TABLE JUSO1
         ADD JOB VARCHAR2(10);

예제) JOB 컬럼의 이름을 JNO로 변경하고 데이터 타입과  크기를 NUMBER(5) 으로 변경 하거나
JOB컬럼을 삭제후 JNO NUMBER(5)를 추가
SQL> ALTER TABLE JUSO1
         MODIFY JOB NUMBER(5);

SQL> ALTER TABLE JUSO1
         RENAME COLUMN JOB TO JNO;

예제) JOB TABLE 생성
JNO     NUMBER(5)             -PK
JOB     VARCHAR2(20)

SQL> CREATE TABLE JOB (
          JNO NUMBER(5),
          JOB VARCHAR2(20),
          CONSTRAINT JOB_JNO_PK PRIMARY KEY(JNO)
           );

# 제약 조건 추가하기
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명

예제) JUSO1의 NO에 PK제약조건 추가
SQL>  ALTER TABLE JUSO1
          ADD CONSTRAINT JUSO1_NO_PK PRIMARY KEY(NO)

예제) JUSO1 의 AGE에 CK제약조건(AGE>19)추가
SQL> ALTER TABLE JUSO1
         ADD CONSTRAINT JUSO1_AGE_CK CHECK(AGE>=19)

예제) JUSO1 의 SSN에 UK 제약조건추가
SQL> ALTER TABLE JUSO1
         ADD CONSTRAINT JUSO1_SSN_UNIQUE UNIQUE(SSN);

예제) JUSO1 의 NAME에 NN 제약조건 추가(여기서 NOT NULL은 추가개념이 아니라 변경의 개념으로 써야한다.)

SQL> ALTER TABLE JUSO1
         MODIFY(NAME VARCHAR2(10) CONSTRAINT JUSO1_NAME_NN NOT NULL)
---------------------------------------------------------------------------------
# FOREIGN KEY 생성하기
예제) JUSO1 테이블의 JNO에 FK제약조건을 추가(JOB 테이블의 PK의 JNO를 참조)
SQL> ALTER TABLE JUSO1
         ADD CONSTRAINT JUSO1_JNO_FK FOREIGN KEY(JNO)
         REFERENCES JOB(JNO)

예제) JOB TABLE에 데이터를 입력하라
1 학생
2 선생님
3 연예인
4 화가
5 기타

SQL> INSERT INTO JOB
         VALUES(1,'학생')
SQL> INSERT INTO JOB
         VALUES(2,'선생님');
SQL> INSERT INTO JOB
         VALUES(3,'연예인');
SQL> INSERT INTO JOB
         VALUES(4,'화가');
SQL> INSERT INTO JOB
         VALUES(5,'기타');

예제) JUSO1 TABLE에 데이터를 입력하라
11,'둘리','서울',20,'801123',1
12,'도우너','서울',21,'801124',3
13,'고길동','서울',22,'801125',3
14,'마이콜','서울',23,'801126',7

SQL> INSERT INTO JUSO1
         VALUES(11,'둘리','서울',20,'801123',1);
SQL> INSERT INTO JUSO1
         VALUES(12,'도우너','서울',21,'801124',3);
SQL> INSERT INTO JUSO1
         VALUES(13,'고길동','서울',22,'801125',3);

예제) 마이콜의 JNO을 5로 수정해라
SQL> UPDATE JUSO1
         SET JNO=5
         WHERE JNO IS NULL

예제) JOB 테이블에 화가의 JNO를 6로 변경하세요
SQL> UPDATE JOB
         SET JNO=6
         WHERE JOB='화가';

예제) JOB테이블의 기타의 JNO를 5에서 7로 변경하세요
SQL> UPDATE JOB
         SET JNO=7
         WHERE JOB='기타';
-> 무셜성 제약조건에 위배

예제) JUSO1 테이블에서 JNO가 5인 레코드 삭제
SQL> DELETE JUSO1
         WHERE JNO=5;

예제) JOB 테이블에서 JNO가 6인 레코드 삭제
SQL> DELETE JOB
         WHERE JNO=6;

예제) JOB테이블에서 JNO가 5인레코드 삭제
SQL> DELETE JOB
         WHERE JNO=5;

예제) JOB테이블에서 JNO가 3인 레코드 삭제
SQL> DELETE JOB
         WHERE JNO=3;
-> FOREIGN KEY 연결에 의해 삭제를 할 수 없다.

# PK와 FK의 연결 끊기(PK의 제약조건을 삭제 해주면 댄다)

예제) JOB테이블의 JNO커럼의 PK제약조건 삭제(CASCADE)
SQL> ALTER TABLE JOB
         DROP CONSTRAINT JOB_JNO_PK CASCADE;
-> 강제로 PK와 FK의 관계를 CASCADE를 이용하여 끊어준다.

*이제는 다음의 예제가 수행 가능하다
예제) JOB테이블에서 JNO가 3인 레코드 삭제
SQL> DELETE JOB
         WHERE JNO=3;

------------------------------------------------------------------------------
예제) A, B 테이블을 생성
CREATE TABLE A(
      ANO NUMBER PRIMARY KEY,
      BNO NUMBER,
                        CONSTRAINT A_BNO_FK FOREIGN(BNO)
                                         REFERENCES B(BNO)
                                         ON DELETE CASCADE
);

CREATE TABLE B(
      BNO NUMBER PRIMARY KEY,
      BIGO VARCHAR2(10)
);

* 데이터 입력
SQL> INSERT INTO B
         VALUES (1,'ABC');

SQL> INSERT INTO A
         VALUES(100,1);

예제) 부모테이블에서 BNO=1 레코드 삭제
SQL> DELETE FROM B
         WHERE BNO=1;
-> 원래는 여기서 오류가 나야하나 테이블 생성시 FORIEGN KEY 에 ON DELETE CASCADE 옵션이 붙어서 삭제가 가능하다. 즉, PK키가 있는  테이블의 키값이 있는 레코드를 삭제할 경우 FK키가 있는 테이블의 연결된 내용도 같이 삭제된다. 한마디로 말하자면 레코드를 지울경우 ON DELETE CASCADE 옵션에 의해서 그 연결된 레코드도 지워진다.