SQL 기초] 제약조건(Constraint) 관리(추가, 삭제, 비활성화, 활성화)

2017.03.06 15:51



컬럼에 대한 제약조건 추가 / 삭제



컬럼에 대해서 제약조건을 추가하거나 제거할 때

(컬럼에 대한 제약조건은 대표적으로 NOT NULL)



ALTER TABLE 테이블명 MODIFY 컬럼조건;



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 SCOTT @ ORACLE > DESC test01;
 이름               널?      유형
 ------------------ -------- -------------------------
 NO                 NOT NULL NUMBER
 NAME                        VARCHAR2(10)
 A                           NUMBER
 B                           NUMBER
 
 
 
 SCOTT @ ORACLE > ALTER TABLE test01 MODIFY b NUMBER NOT NULL;
 
테이블이 변경되었습니다.
 
-- b 컬럼에 not null 제약조건 추가
 
-- not null 제약조건을 추가할 때 이미 입력된 데이터 중에 
-- null값이 있는 경우는 에러 발생.
 
 
 
 SCOTT @ ORACLE > DESC test01;
 이름               널?      유형
 ------------------ -------- -------------------------
 NO                 NOT NULL NUMBER
 NAME                        VARCHAR2(10)
 A                           NUMBER
 B                  NOT NULL NUMBER
 
 
 
 
 SCOTT @ ORACLE > ALTER TABLE test01 MODIFY b NUMBER;
 
테이블이 변경되었습니다.
 
 
-- b열의 not null 제약조건 삭제
 
 
 SCOTT @ ORACLE > DESC test01;
 이름               널?      유형
 ------------------ -------- -------------------------
 NO                 NOT NULL NUMBER
 NAME                        VARCHAR2(10)
 A                           NUMBER
 B                           NUMBER
 
cs




테이블에 대한 제약조건 추가 / 삭제


테이블에 대해서 제약을 추가할 때 


ALTER TABLE 테이블명 ADD 제약조건;




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 SCOTT @ ORACLE > ALTER TABLE test01 ADD CONSTRAINT test01_a_pk PRIMARY KEY(a);
 
테이블이 변경되었습니다.
 
-- PK 추가
 
 
 SCOTT @ ORACLE > SELECT constraint_name, status
  2            FROM ALL_CONSTRAINTS
  3            WHERE TABLE_NAME='TEST01';
 
CONSTRAINT_NAME                                              STATUS
------------------------------------------------------------ ----------------
SYS_C0011305                                                 ENABLED
SYS_C0011378                                                 ENABLED
TEST01_A_PK                                                  ENABLED
 
cs




테이블 레벨의 제약조건을 삭제할 때


ALTER TABLE 테이블명 DROP CONSTRAINT 제약이름;



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 SCOTT @ ORACLE > SELECT constraint_name, status
  2            FROM ALL_CONSTRAINTS
  3            WHERE TABLE_NAME='TEST01';
 
CONSTRAINT_NAME                                              STATUS
------------------------------------------------------------ ----------------
SYS_C0011305                                                 ENABLED
SYS_C0011378                                                 ENABLED
TEST01_A_PK                                                  ENABLED
 
 
 
 SCOTT @ ORACLE > ALTER TABLE test01 drop constraint test01_a_pk;
 
테이블이 변경되었습니다.
 
-- test01_a_pk 제약조건 삭제
 
 
 
 SCOTT @ ORACLE > SELECT constraint_name, status
  2            FROM ALL_CONSTRAINTS
  3            WHERE TABLE_NAME='TEST01';
 
CONSTRAINT_NAME                                              STATUS
------------------------------------------------------------ ----------------
SYS_C0011305                                                 ENABLED
SYS_C0011378                                                 ENABLED
 
cs




제약조건 enable, disable 시키기 (오라클만 해당)



아래 제약조건을 Disable하는 방법은 오라클에만 해당한다.

(mysql에도 비슷하게 일시적으로 제약조건을 disable하는 방법이 있을듯 싶은데 못찾았음)


ALTER TABLE 테이블명 [DISABLE | ENABLE] CONSTRAINT 제약조건 이름;



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 SCOTT @ ORACLE > ALTER TABLE test01 ADD CONSTRAINT test01_b_uk UNIQUE (b);
 
테이블이 변경되었습니다.
 
-- test01 테이블의 b컬럼에 unique 제약조건 설정
 
 
 
 SCOTT @ ORACLE > SELECT constraint_name, status
  2            FROM ALL_CONSTRAINTS
  3            WHERE TABLE_NAME='TEST01';
 
CONSTRAINT_NAME                                              STATUS
------------------------------------------------------------ ----------------
SYS_C0011305                                                 ENABLED
SYS_C0011378                                                 ENABLED
TEST01_B_UK                                                  ENABLED
 
 
 
 SCOTT @ ORACLE > INSERT INTO test01 VALUES(7'아저씨'NULL,2);
INSERT INTO test01 VALUES(7'아저씨'NULL,2)
*
1행에 오류:
ORA-00001: 무결성 제약 조건(SCOTT.TEST01_B_UK)에 위배됩니다
 
-- unique 제약조건 때문에 동일한 값이 이미 있는 경우 에러 발생
 
 
 
 
 SCOTT @ ORACLE > ALTER TABLE test01 DISABLE CONSTRAINT test01_b_uk;
 
테이블이 변경되었습니다.
 
 
-- 제약조건 disable
 
 
 
 SCOTT @ ORACLE > INSERT INTO test01 VALUES(7'아저씨'NULL,2);
 
1 개의 행이 만들어졌습니다.
 
-- unique 제약조건이 비활성화되어 중복 값 데이터 입력 
 
 
 
 SCOTT @ ORACLE > SELECT constraint_name, status
  2            FROM ALL_CONSTRAINTS
  3            WHERE TABLE_NAME='TEST01';
 
CONSTRAINT_NAME                                              STATUS
------------------------------------------------------------ ----------------
SYS_C0011305                                                 ENABLED
SYS_C0011378                                                 ENABLED
TEST01_B_UK                                                  DISABLED
 
cs



비활성화된 제약조건을 다시 활성화시키려면 DISABLE CONSTRAINT 키워드 대신

ENABLE CONSTRAINT 키워드를 사용한다.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 SCOTT @ ORACLE > ALTER TABLE test01 ENABLE CONSTRAINT test01_b_uk;
ALTER TABLE test01 ENABLE CONSTRAINT test01_b_uk
*
1행에 오류:
ORA-02299: 제약 (SCOTT.TEST01_B_UK)을 사용 가능하게 할 수 없음 - 중복 키가 있습니다
 
-- 중복값이 입력된 상태에서는 UNIQUE 제약조건을 활성화시킬 수 없다.
 
 
 SCOTT @ ORACLE > UPDATE test01 SET b=15 WHERE no=7;
 
1 행이 갱신되었습니다.
 
 
 
 
 SCOTT @ ORACLE > ALTER TABLE test01 ENABLE CONSTRAINT test01_b_uk;
 
테이블이 변경되었습니다.
 
 
 
 SCOTT @ ORACLE > SELECT constraint_name, status
  2            FROM ALL_CONSTRAINTS
  3            WHERE TABLE_NAME='TEST01';
 
CONSTRAINT_NAME                                              STATUS
------------------------------------------------------------ ----------------
SYS_C0011305                                                 ENABLED
SYS_C0011378                                                 ENABLED
TEST01_B_UK                                                  ENABLED
 
 
cs




또루아빠 SQL(오라클,mysql) , , , , , ,