데이터 딕셔너리와 제약 조건 - 2024-09-12

수호천사임다·2024년 9월 12일

오라클

목록 보기
28/53

데이터 사전이란 ?

  • 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블
  • 오라클은 데이터 사전을 지속적으로 갱신하여 데이터베이스 구조, 감사, 사용자 권한, 데이터 등의 변경 사항을 반영한다.
  • 사용자는 데이터 사전의 내용을 직접 수정하거나 삭제할 수 없다.
  • 사용자가 데이터 사전을 조회해 보면 시스템이 직접 관리하는 테이블이기 때문에 암호 같은 기호만 보여질 뿐 내용을 알 수는 없다.
  • 데이터 베이스에서 작업이 진행되는 동안 오라클은 데이터 사전을 읽어 객체의 존재 여부와 사용자에게 적합한 액세스 권한이 있는지를 확인한다.
  • 데이터 사전은 데이터베이스 생성 과정 중에 catalog.sql 이라는 스크립트를 수행하는 과정에서 생성되며, 모든 데이터 사전의 정보는 SYSTEM 테이블스페이스에 저장된다.

데이터 사전의 내용

  • 오라클의 사용자 정보
  • 오라클 권한과 롤 정보
  • 데이터베이스 스키마 객체 정보
  • 무결성 제약조건에 관한 정보
  • 데이터베이스의 구조 정보
  • 데이터베이스의 성능 정보
  • 기타 일반적인 DATABASE 정보

SELECT * FROM obj; — tab, tabs로 안 보이는것도 다 보인다.

제약 조건이란?

정확하지 않는 데이터를 저장되지 않기 위해 방지하는 것.

제약 조건이 많이 주면 많이 줄수록 INSERTR, UPDATE 등의 작업을 할때마다 효율이 떨어진다.

자바에서 제약 조건을 걸어주는게 좋다 → DB는 데이터 양이 너무 많기 떄문에 시간이 많이걸림

  • 제약 조건을 사용하여 데이터베이스의 값을 제한하는 규칙인 무결성 제약 조건을 정의 한다.
  • 데이터 무결성 제약 조건(Data integrity Constraint Rule)
    ᆞ 데이터베이스의 일관성을 보장하기 위해 일관된 데이터베이스 상태를 정의하는 규칙들을 묵시적 또는 명시적으로 정의하는 것을 의미한다.
    ᆞ 무결성 : 데이터베이스 내에 있는 데이터의 정확성(유효성) 유지
    ᆞ 제약 조건 : 바람직하지 않은 데이터가 저장 되는 것을 방지 하는 것

데이터 무결성 제약 조건의 특징

  • 테이블 생성 시 무결성 제약조건을 정의하여 프로그래밍 과정을 줄여준다.
  • 데이터베이스 서버에 의해 무결성 제약조건이 관리되어 데이터 오류 발생 가능성을 줄여준다.
  • 테이블에 대해 정의되고 데이터 딕셔너리에 저장 된다.
  • 일시적으로 활성화 또는 비활성화 할 수 있다.
    • 제약 조건을 일시로 끄고 데이터를 집어넣는다. → 대신 제약 조건을 비활성화면 제약 조건 데이터도 들어간다.
    • EX)은행은 은행 점검시간에 전체를 막아두고 검사하는 시간.
  • 무결성 제약 조건 종류
    • NOT NULL - 필수 값 입력

컬럼에 null 값을 허용하지 않는다.

PRIMARY KEY constraint
기본 키 제약 조건은 하나의 컬럼 또는 두 개 이상의 컬럼을 조합하여 여러 행이 동일한 값을 가질 수 없다. 기본키는 하나의 테이블에 하나만 지정할 수 있으며, null 값을 허용하지 않는다.(3개 이상의 컬럼을 조합하면 좀 힘들어진다..)
UNIQUE constraint
UNIQUE 제약 조건은 하나의 컬럼 또는 두 개 이상의 컬럼을 조합하여 여러 행이 동일한 값을 가질 수 없다.
UNIQUE 제약 조건은 하나의 테이블에 두 개 이상 지정 가능하며, null 값을 허용할 수 있다.(만들면 만들수록 인덱스가 만들어지고, 결과적으로 그만큼 메모리가 차지가된다.) 꼭 필요한 경우 사용할 것
FOREIGN KEY constraint
외래 키(FOREIGN KEY) 제약 조건은 한 테이블의 값이 다른 테이블의 값과 일치해야 한다.
CHECK constraint
CHECK 제약 조건은 지정된 조건을 만족하는 값만 가질 수 있다.

객체 무결성과 참조 무결성

  • 객체 무결 성
    ᆞPRIMARY KEY, UNIQUE
    ᆞ릴레이션에 저장되는 튜플(tuple)의 유일성을 보장하기 위한 제약조건이다.
  • 참조 무결성
    ᆞFOREIGN KEY
    ᆞ릴레이션 간의 데이터의 일관성을 보장하기 위한 제약조건이다.
    제약 조건 관련 데이터 사전
  • USER_CONSTRAINTS
    사용자의 테이블에 지정된 제약 조건에 대한 정보(제약 조건 이름, 제약 조건 타입 등)
    USER_CONS_COLUMNS
    현재 사용자가 가지고 있는 컬럼의 제약 조건에 대한 정보
  • 제약 조건 타입(constraint_type)
    P:PRIMARY KEY, C:NOT NULL 등, U:UNIQUE, R:참조키 등
    제약 조건 설정
    - 제약 조건은 두가지 방법으로 설정할 수 있다.
    ᆞ첫 번째 방법 : 컬럼 레벨 방식
    테이블을 생성할 때 컬럼명과 자료형 뒤에 제약조건을 명시한다.
    ᆞ두 번째 방법 : 테이블 레벨 방식
    컬럼을 모두 정의하고 마지막에 정의된 컬럼들의 제약 조건을 한 번에 지정한다.
    - 제약 조건은 테이블을 생성할 때 지정하거나 ALTER TABLE를 이용하여 추가 및 제거가 가능하다.
    - 제약 조건 이름은 데이터베이스 객체 명명 규칙으로 지정 가능하다.
    - 제약 조건 이름을 지정하지 않으면 오라클은 SYS_Cnnnnnnn 형식으로 제약 조건 이름을 부여 한다.
    - 모든 제약 조건은 데이터 사전(Data Dictionary)에 저장된다.

테이블 생성과 동시에 제약 조건 설정

  1. 컬럼 레벨 방식의 제약 조건 설정(inline constraint)
  • 테이블을 생성할 때 컬럼명과 자료형 뒤에 제약조건을 명시한다.
  • PRIMARY KE, UNIQUE, FOREIGN KEY 를 지정할 때 두 개 이상의 컬럼을 이용하여 제약 조건을 지정 할수 없다.
형식
CREATE TABLE table_name (
	column datatype [ CONSTRAINT constraint_name ]
	{ [ NOT ] NULL | UNIQUE | PRIMARY KEY | references_clause | CHECK (condition) }
	:
);
  1. 테이블 레벨 방식의 제약 조건 설정(out of line constraint)
  • 컬럼을 모두 정의하고 마지막에 정의된 컬럼들의 제약 조건을 한 번에 지정한다.
  • PRIMARY KEY, UNIQUE, FOREIGN KEY 를 지정할 때 두 개 이상의 컬럼을 이용하여 제약 조건을 지정 할수 있다.
형식
CREATE TABLE table_name (
column datatype [inline_constraint],
:
	[ CONSTRAINT constraint_name ]
		{ PRIMARY KEY (column [, column ]...) | UNIQUE (column [, column ]...)
		| FOREIGN KEY (column [, column ]...) references_clause
		| CHECK (condition)
		}
);

제약 조건 추가

  • 테이블 생성 후 제약 조건을 추가할 필요가 있을 때는 ALTER 명령어를 이용해서 추가한다.
  • 기본형식
제약 조건 추가(NOT NULL 제외)
ALTER TABLE table_name ADD [ CONSTRAINT constraint_name ]
		{ PRIMARY KEY (column [, column ]...) | UNIQUE (column [, column ]...)
		| FOREIGN KEY (column [, column ]...) references_clause
		| CHECK (condition)
} ;

NOT NULL 제약 조건 추가

ALTER TABLE table_name MODIFY column NOT NULL;

MODIFY - 수정

제약 조건 제거

  • 제약 조건을 제거할 필요가 있을 때는 ALTER 명령어를 이용해서 제거한다.
제약 조건 제거
ALTER TABLE table_name DROP
	{ PRIMARY KEY
	| CONSTRAINT constraint_name [ CASCADE ]
};

제약 조건 이름 변경

  • 제약 조건이름을 변경할 필요가 있을 때는 ALTER 명령어를 이용해서 변경한다.
제약 조건 제거
ALTER TABLE table_name
RENAME CONSTRAINT old_name TO new_name;

기본키(Primary Key)

  • 후보 키 중에 선택된 주 키로 NULL값을 가질 수 없다.
  • 컬럼이 중복적인 값을 가질 수 없다.(예 : 회원 아이디, 이메일, 학번, 사원번호, 주민번호 등)
  • 하나의 테이블에는 하나의 기본키만 지정 가능하다.
  • 두 개 이상의 컬럼을 이용하여 기본키를 지정할 수 있다.

후보키(Candidate Key)

  • 기본키를 쓸 수 있는 컬럼들
  • 후보키 중에서 NULL을 가지면 안되고(유일성) 검색에서 가장 많이 쓰는 것, 중복 x
  • 릴레이션의 속성 중 튜플을 유일하게 식별하는 속성들의 일부분으로 기본키로 사용 할 수 있는 속성의 모임이다.
  • 유일성, 최소성의 조건을 가진다.

제약조건 및 컬럼 확인

SELECT u1.table_name, column_name, constraint_type, u1.constraint_name, search_condition
        FROM user_constraints u1
        JOIN user_cons_columns u2 ON u1.constraint_name = u2.constraint_name
        WHERE u1.table_name = UPPER('테이블명');

image.png

image.png

R 은 참조키 아버지가 있다는 뜻 - 아버지가 누구인지는 모름

자기 참조 - EMPOLYEES 가 자기 자신을 아버지로 둔다?

부와 자 관계의 모든 테이블 출력

 SELECT fk.owner, fk.constraint_name,
                    pk.table_name parent_table, fk.table_name child_table
        FROM all_constraints fk, all_constraints pk 
        WHERE fk.r_constraint_name = pk.constraint_name AND fk.constraint_type = 'R'
        ORDER BY fk.table_name;

테이블명을 참조하는 모든 테이블 목록 출력(자식 테이블 목록 출력)

    SELECT fk.owner, fk.constraint_name , fk.table_name 
        FROM all_constraints fk, all_constraints pk 
        WHERE fk.r_constraint_name = pk.constraint_name 
                   AND fk.constraint_type = 'R' 
                   AND pk.table_name = UPPER('테이블명')
        ORDER BY fk.table_name;

테이블명이 참조하고 있는 모든 테이블 목록 출력(부모 테이블 목록 출력)

     SELECT table_name FROM user_constraints
        WHERE constraint_name IN (
              SELECT r_constraint_name 
              FROM user_constraints
              WHERE table_name = UPPER('테이블명') AND constraint_type = 'R'
          );

테이블명의 부모 테이블 목록 및 부모 컬럼 목록 출력

부모 2개 이상으로 기본키를 만든 경우 여러번 출력 됨 

SELECT fk.constraint_name, fk.table_name child_table, fc.column_name child_column,
      pk.table_name parent_table, pc.column_name parent_column
      FROM all_constraints fk, all_constraints pk, all_cons_columns fc, all_cons_columns pc
      WHERE fk.r_constraint_name = pk.constraint_name
         AND fk.constraint_name = fc.constraint_name
         AND pk.constraint_name = pc.constraint_name
         AND fk.constraint_type = 'R'
         AND pk.constraint_type = 'P'
         AND fk.table_name = UPPER('테이블명');
profile
수호천사임다
이전 포스트

서브쿼리

다음 포스트

제약조건 확인 - 2024-09-12

0개의 댓글