SQL 다루기(2) : DB 제약 조건과 key

1. DATA TABLE 과 테이블의 종류

1. 관리 정보를 담는 테이블(META DATA) : DATA DICTIONARY

• 데이터 사전(Data Dictionary)이라고도 하며 대부분 읽기전용으로 제공되는 테이블 및 뷰들의 집합으로 데이터베이스 전반에 대한 정보를 제공 한다.
• 전반적인 DB에 대한 정보 조회의 용도로서 사용함?
• DATA DICTIONARY 의 규칙 : DICTIONARY 의 이름을 붙이는데 사용되는 일종의 규칙
  • 접두사 + 키워드 + 복수형

• USER_ : 내가 생성한 것과 관련된 정보 ⇒ 알겠지만 USER_ 이상으로 들어가서 쓰는 경우는 거의 없다.

SQL> DESC USER_TABLES;
 이름                                                  널?      유형
 ----------------------------------------------------- -------- ------------------------------------
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                                VARCHAR2(30)

• ALL_ : 접근 권한이 있는 것과 관련된 정보

SQL> SELECT TABLE_NAME, OWNER
  2  FROM ALL_TABLES;

TABLE_NAME                     OWNER
------------------------------ ------------------------------
DUAL                           SYS
SYSTEM_PRIVILEGE_MAP           SYS

• DBA_ : 관리자가 관리목적상 참고해야하는 정보

## DB 에 등록되어있는 사용자 정보
SQL> SELECT USERNAME, PASSWORD, ACCOUNT_STATUS
  2  FROM DBA_USERS;

## DB에 등록되어있는 사용자 && 패스워드
SQL> SELECT USER, PASSWORD
  2  FROM DBA_USERS;

• V$ : 동적, 성능과 관련된 정보
  • 사용 메모리 정보 등?

 

1. 사용자 정의형 테이블 : 사용자가 생성한 테이블?

SQL> SELECT TABLE_NAME
  2  FROM USER_TABLES;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
COPY_EMP
EMPS
CEMP
COPY_EMP3
TEST_EMP
CUST
CUSTOMER

2. CONNECT ID/PW

• 새로 DB 에 연결하는 명령어 → 주로 다른 사용자로 로그인 할 때 사용됨

SQL> CONNECT SYSTEM/oracle
혹은
SQL> CONN scott/tiger

 

3. DB 무결성과 DB 제약 조건(오늘의 포인트)

1) DB 무결성

• DB 의 특징 중 하나는 무결성! ⇒⇒ 데이터의 무결성은 데이터의 정확성, 일관성, 유효성이 유지되는 것을 말한다. 데이터의 무결성을 유지하는 것은 데이터베이스 관리시스템 (DBMS)의 중요한 기능이며, 주로 데이터에 적용되는 특정한 제약 조건을 두어 데이터의 무결성을 유지한다.

2) DB 무결성 유지를 위한 제약조건 - CONSTRAINT

• 제약 조건은 테이블 생성 시 혹은 생성된 이후에도 추가 가능

제약조건명 내용

NOT NULL	값이 NULL 이 올 수 없음
UNIQUE	말 그대로 UNIQUE 한 값만 올 수 있음 → 중복 불가
PRIMARY KEY	PRIMARY KEY = NOT NULL + UNIQUE , NULL 값은 올 수 없고, 중복 불가 함 && 테이블을 대표하는 컬럼
FOREIGN KEY	'A테이블에서 B 테이블의 기본키를 참조하는 키’ ⇒ A 테이블에서 B 테이블을 참고하는 경우 B 테이블의 PREMARY KEY 에 존재하는 값만 가져올 수 있음을 의미
CHECK	주어진 조건에 해당하는 값만 입력 가능
번외	############### 번외 ##############
REFERENCES	REFERENCE 는 FOREIGN 때 사용되는 제약 조건 중 일부로 FOREIGN KEY 를 설정 할 때 ‘어디서’ 가져올 것인지 지정해줄 수 있다 ⇒ FOREIGN KEY(컬럼명) REFERENCE [참조할 테이블명](참조 컬럼명)

 

테이블 생성시 제약 조건 넣기

• 컬럼명 자료형 [CONSTRAINT 제약조건명] 제약종류 ⇒ [] 부분은 생략가능
• 각 컬럼명 옆에 제약 조건을 넣는게 아니라 맨 밑에 제약조건을 넣는 방법도 존재한다 ⇒ 테이블 레벨 제약조건 이라고 하며, 이때는 CONSTRAINT 생략하지말고 그대로 사용하자

 

 

SQL> CREATE TABLE DEPT1
  2  (DEPTNO NUMBER PRIMARY KEY,// PK 는 중복될 수 없다
  3  DNAME VARCHAR2(20) UNIQUE, // 중복 불가
  4  LOC VARCHAR2(20)
  5  );

#####

SQL> CREATE TABLE EMP1
  2  (EMPNO NUMBER(4) CONSTRAINT EMP1_EMPNO_PK PRIMARY KEY, // EMP1 에서 EMPNO 는 PK
  3  ENAME VARCHAR2(20) NOT NULL, // ENAME 은 NULL 값이 올 수 없음
  4  SAL NUMBER(7,2),
  5  DEPTNO NUMBER(2)
  6  ,
  7  CONSTRAINT EMP1_SAL_CK CHECK( SAL BETWEEN 500 AND 5000), // SAL 에서 500 ~ 5000 사이값만
  8  CONSTRAINT EMP1_DEPTNO_FK FOREIGN KEY(DEPTNO) // DEPTNO 를 FOREIGN KEY 로 참고함
  9  REFERENCES DEPT1(DEPTNO)
 10  );

 

• 제약 조건 테스트
  • 존재하지 않는 FOREIGN KEY 는 참조 불가능하다
  • 이미 FOREIGN KEY 의 값을 참조하는 테이블과 컬럼이 있는 경우 해당 FOREIGN KEY 값은 삭제 불가능하다

SQL> INSERT INTO DEPT1 VALUES (
  2  2, 'IT', 'SEOUL'
  3  );

1 개의 행이 만들어졌습니다.

### FOREIGN KEY 참조 불가 에러 ###

SQL> INSERT INTO EMP1 VALUES( 1111, 'HONG', 3000, 3);
INSERT INTO EMP1 VALUES( 1111, 'HONG', 3000, 3)
*
1행에 오류:
ORA-02291: 무결성 제약조건(SCOTT.EMP1_DEPTNO_FK)이 위배되었습니다- 부모 키가 없습니다

#####
SQL> INSERT INTO EMP1 VALUES( 1111, 'HONG', 3000, 1);

1 개의 행이 만들어졌습니다.

SQL> SELECT * FROM EMP1;

     EMPNO ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
      1111 HONG                       3000          1\\

#####

SQL> INSERT INTO EMP1 VALUES(2222, 'LEE', 300, 1);
INSERT INTO EMP1 VALUES(2222, 'LEE', 300, 1)
*
1행에 오류:
ORA-02290: 체크 제약조건(SCOTT.EMP1_SAL_CK)이 위배되었습니다 => 최저 500 이상

#####

SQL> DELETE DEPT1 WHERE DEPTNO =1;
DELETE DEPT1 WHERE DEPTNO =1
*
1행에 오류:
ORA-02292: 무결성 제약조건(SCOTT.EMP1_DEPTNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
=> DEPTNO =1 을 참조하는 값이 있기 때문에 삭제 불가

테이블 생성 후 제약 조건 추가

• ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건명] 제약종류

SQL> ALTER TABLE DEPT1
  2  ADD CONSTRAINT DEPT1_LOC_UK UNIQUE(LOC);

테이블이 변경되었습니다.

####

SQL> UPDATE DEPT1
  2  SET LOC = 'B'
  3  WHERE DEPTNO = 2;
UPDATE DEPT1
*
1행에 오류:
ORA-00001: 무결성 제약 조건(SCOTT.DEPT1_LOC_UK)에 위배됩니다

제약 조건 확인하기

• 제약조건은 기본적으로 USER_CONSTRAINTS 를 통해 조회 가능하다.
• 이후 다시 SELECT 문을 통해서 테이블명을 기준으로 불러와서 조건을 확인 가능하다

SQL> DESC USER_CONSTRAINTS
 이름                                                  널?      유형
 ----------------------------------------------------- -------- ------------------------------------
 CONSTRAINT_NAME                                       NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                                VARCHAR2(1)
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                               LONG

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION
  2  FROM USER_CONSTRAINTS
  3  WHERE TABLE_NAME IN ( 'EMP1', 'DEPT1');

CONSTRAINT_NAME                C TABLE_NAME                     SEARCH_CONDITION
------------------------------ - ------------------------------ --------------------
SYS_C0011117                   P DEPT1
SYS_C0011118                   U DEPT1
DEPT1_LOC_UK                   U DEPT1
SYS_C0011119                   C EMP1                           "ENAME" IS NOT NULL
EMP1_SAL_CK                    C EMP1                            SAL BETWEEN 500 AND 5000
EMP1_EMPNO_PK                  P EMP1
EMP1_DEPTNO_FK                 R EMP1

 

4. DB 테이블 이름 규칙

• 테이블 내용과 이어지도록 하는 의미가 있도록 지정!! <= 사실 가장 중요
• A-Z, a-z, 0-9, _ , $, #
• 1 ~ 30
• 첫 글자 영문자 가능한 대문자
• 공백 X
• 예약어 사용 불가
• 기본적인 SQL 에 사용되는 단어들(SELECT, FROM , WHERE 등)은 사용 불가