oracle sql (5)

/*
5일차

제약 조건
- 테이블에 저장할 데이터를 제약하는 특수 규칙
- 데이터의 정확성을 높이기 위해 사용
- DDL에서 설정 : 테이블의 특정열에 지정
- 종류
    1. NOT NULL : NULL을 허용하지 않는다
    2. UNIQUE : 유일한 값을 가져야 한다
    3. PRIMARY KEY : 지정한 컬럼이 유일한 값이면서 NULL을 허용하지 않는다
    4. FOREIGN KEY : 다른 테이블의 컬럼을 참조하여 존재하는 값만 입력할 수 있다
    5. CHECK : 실행 조건을 만족하는 데이터만 입력 가능
    6. DEFAULT : 기본값 지정
    
- 데이터 무결성
    1. 데이터베이스에 저장되는 데이터의 정확성과 일관성을 보장
    2. 제약조건이 데이터의 무결성을 유지하기 위한 장치
    3. 삽입, 수정, 삭제 등의 모든 과정에 유지
    4. 종류
        4-1. 영역무결성(domain integrity) : 컬럼에 저장되는 값의 적정 여부를 확인
        4-2. 개체무결성(entity integrity) : 테이블 데이터를 유일하게 식별할 수 기본키는 반드시 값을 가지고 있어야 하며, NULL이 될 수 없다
        4-3. 참조무결성(referential integrity) : 참조 테이블의 외래키 값은 참조 테이블의 기본키로써 존재해야 하며, NULL이 가능

*/

-- 1. NOT NULL 
-- 테이블 생성 시 설정
drop TABLE t1;
CREATE TABLE t1 (
    login_id VARCHAR2(20) NOT NULL,
    login_pw VARCHAR2(20) NOT NULL,
    tel VARCHAR2(20)
);

INSERT INTO t1(login_id, login_pw, tel)
VALUES ('test', NULL, '010-1111-1111');

INSERT INTO t1(login_id, login_pw, tel)
VALUES ('test', '1234', '010-1111-1111');

UPDATE t1 SET login_pw = NULL WHERE login_id = 'test';

SELECT * FROM t1;

-- 제약 조건 확인
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints;

-- 제약 조건 이름 지정

drop table t2;
drop table t_con;
CREATE TABLE t_con (
    login_id VARCHAR2(20) CONSTRAINT t2_login_id NOT NULL,
    login_pw VARCHAR2(20) CONSTRAINT t2_login_pw NOT NULL,
    tel VARCHAR2(20)
);

SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints;

-- 이미 생성된 테이블에 제약조건 추가
-- 이미 NULL값이 컬럼에 존재하는 경우
INSERT INTO t1(login_id, login_pw, tel)
VALUES ('test', '1234', NULL);

SELECT * FROM t1;

ALTER TABLE t1
MODIFY (tel NOT NULL);

UPDATE t1 SET tel = '010-2222-2222'
WHERE login_id = 'test1';

SELECT * FROM t1;

ALTER TABLE t1
MODIFY (tel NOT NULL);

SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints;

-- 제약 조건 이름을 직접 지정
ALTER TABLE t1
MODIFY (tel constraint t1_tel_con NOT NULL);

-- 생성한 제약 조건의 이름 변경
ALTER TABLE t1
RENAME constraints SYS_C007019 to t1_phone_con;

SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints;

-- 제약 조건 삭제
ALTER TABLE t1
DROP CONSTRAINT t1_phone_con;

SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints;

-- 2. UNIQUE 
DROP TABLE t1;
CREATE TABLE t1(
    login_id VARCHAR2(20) UNIQUE,
    login_pw VARCHAR2(20) NOT NULL,
    tel VARCHAR2(20)
);

DESCRIBE t1;

-- 제약 조건 확인
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'T1';   -- U : UNIQUE(중복 허용 X)

-- 중복 허용 X
INSERT INTO t1 
VALUES ('test1', 'pw1', '1111');
INSERT INTO t1 
VALUES ('test1', 'pw1', '1111');

INSERT INTO t1 
VALUES ('test2', 'pw1', '1111');

SELECT * FROM t1;

-- unique 지정된 컬럼에 NULL 입력
INSERT INTO t1
VALUES (NULL, 'pw1', '1111');

SELECT * FROM t1;

-- 데이터 수정
UPDATE t1 SET login_id = 'test1'
WHERE login_id IS NULL;

UPDATE t1 SET login_id = 'test10'
WHERE login_id IS NULL;

SELECT * FROM t1;

-- 테이블 생성하며 unique 지정
DROP table t1;
CREATE TABLE t1 (
    login_id VARCHAR2(20) CONSTRAINT t1_login_id UNIQUE,
    login_pw VARCHAR2(20) CONSTRAINT t1_login_pw NOT NULL,
    tel VARCHAR2(20)
);

SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'T1'; 

-- 3. PRIMARY KEY : NOT NULL + UNIQUE
CREATE TABLE tp(
    login_id VARCHAR2(20) PRIMARY KEY,
    login_pw VARCHAR2(20) NOT NULL,
    tel VARCHAR2(20)
);

DESC tp;

SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'TP';    -- P : PRIMARY

-- 제약 조건 이름 직접 지정 : NOT NULL방식과 동일

-- 중복 값 입력 X
-- NULL 값 입력 X

-- primary key 지정
DROP TABLE tp;
CREATE TABLE tp(
    login_id VARCHAR2(20),
    login_pw VARCHAR2(20),
    tel VARCHAR2(20),
    PRIMARY KEY(login_id),
    CONSTRAINT constraint_name UNIQUE(login_pw)
);

DESC tp;

SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'TP';

-- 4. FOREIGN KEY 
SELECT owner, constraint_name, constraint_type, table_name, 
r_owner, r_constraint_name FROM user_constraints
WHERE table_name IN ('EMP', 'DEPT');

-- FK가 참조하는 컬럼에 NULL입력
INSERT INTO emp
VALUES (999, '홍길동', 'CLERK', '7788', 
TO_DATE('2023-07-28', 'YYYY-MM-DD'), 12000, NULL, 50);

-- FK 지정
DROP TABLE tf;
CREATE TABLE tf(
    deptno NUMBER(2) CONSTRAINT tf_pk PRIMARY KEY,
    dname VARCHAR2(20),
    loc VARCHAR2(15)
);

DESC tf;

-- emp_fk 지정
DROP TABLE emp_tf;
CREATE TABLE emp_tf(
    empno NUMBER(4) CONSTRAINT emp_tf_pk PRIMARY KEY,
    ename VARCHAR2(20),
    job VARCHAR2(20),
    deptno NUMBER(2) CONSTRAINT emp_tf_fk REFERENCES tf(deptno)
);

DESC emp_tf;

SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'EMP_TF';

-- 5. CHECK 
DROP TABLE tc;
CREATE TABLE tc(
    login_id VARCHAR2(20) CONSTRAINT login_id_pk PRIMARY KEY,
    login_pw VARCHAR2(20) CONSTRAINT login_pw_ck CHECK (length(login_pw) > 8),
    tel VARCHAR2(20)
);

INSERT INTO tc
VALUES ('test', '123', '010-1111');

-- 6. DEFAULT
CREATE TABLE td(
    login_id VARCHAR2(20) CONSTRAINT td_login_pk PRIMARY KEY,
    login_pw VARCHAR2(20) DEFAULT '1234',
    tel VARCHAR2(20)
);

INSERT INTO td (login_id, tel)
VALUES ('test1', '010-1111');

SELECT * FROM td;

/*
-- 제약조건 활성화 / 비활성화
ALTER TABLE 테이블명
DISABLE [NOVALIDATE/ VALIDATE] CONSTRAINT 제약조건 이름;

ALTER TABLE 테이블명
ENABLE [NOVALIDATE/ VALIDATE] CONSTRAINT 제약조건 이름;

*/

/*
사용자관리, 권한관리, 롤관리

- 데이터베이스 스키마 
    1. 데이터베이스에서 데이터간 관계, 구조, 제약조건 등 데이터 저장 및 관리를 하기 위해서 
       데이터베이스 구조의 범위를 스키마를 통해 그룹단위로 분류
    2. 종류
        - 외부스키마 = 사용자 뷰 : 사용자나 응용프로그래머가 각 개인의 입장에서 필요로 하는 데이터베이스의 논리적 구조를 정의
        - 개념스키마 = 전체적인 뷰 : 데이터베이스의 전체적인 논리적 구조
        - 내부스키마 = 저장 스키마 : 물리적 저장장치의 입장에서 본 데이터베이스 구조

- 사용자 : 데이터베이스에 접속하여 데이터를 관리하는 계정

*/

/*
-- scott 계정 생성 : system계정에서 생성
create user scott identified by TIGER;

*/

-- 생성 : create
CREATE USER study IDENTIFIED BY study;

-- 권한부여 : grant
GRANT CREATE SESSION TO study;

-- 정보 조회
SELECT * FROM ALL_USERS
WHERE USERNAME = 'study';

-- 사용자 변경과 삭제
ALTER USER study IDENTIFIED BY study;

DROP USER study;

-- 사용자와 객체 모두 삭제
DROP USER study CASCADE;

/*
권한관리
    1. 시스템권한 : 사용자 생성과 정보 수정, 삭제, 데이터베이스 접근...
        - 권한 부여
        grant [시스템권한] to [사용자이름/롤이름/public] [with admin option];
        
        - 권한 취소
        revoke [시스템권한] from [사용자이름/롤이름/public]
        
    2. 객체권한 : 특정 사용자가 생성한 테이블, 인덱스, 뷰, 시퀀스 등과 관련된 권한
        - 권한 부여
        grant [객체권한/ ALL PRIVILEGES] on [스키마.객체이름] to [사용자이름/롤/public] [with admin option];
        
        - 권한 취소
        revoke [객체권한/ ALL PRIVILEGES] on [스키마.객체이름] from [사용자이름/롤/public] [cascade constrains/force];

*/

/*
롤
- 여러 종류의 권한을 묶어놓은 그룹을 의미
- 여러 권한을 부여하고 취소 -> 관리 효율 용이
- 종류
    1. 사전 정의된 롤
        - CONNECT 롤 : 사용자가 데이터베이스에 접속에 필요한 create session 권한
        - RESOURCE 롤 : 테이블, 시퀀스를 비롯한 여러 객체를 생성할 수 있는 권한
        - DBA 롤 : 데이터베이스를 관리하는 시스템권한 대부분 소유
    2. 사용자 정의 롤 : 필요에 의해 직접 권한 포함시킨 롤
        - 절차
        2-1. create role : 롤 생성
        2-2. grant : 생성한 롤에 권한 부여
        2-3. grant : 권한이 포함된 롤을 특정 사용자에게 부여
        2-4. revoke : 롤 취소

*/