오라클 DBMS 정리 06

-- user_constraints는 딕셔너리
SELECT * FROM user_constraints;

-- column level constraint(컬럼단위 제약사항) : 테이블 생성 시 컬럼 뒤에 제약사항을 설정
-- 컬럼단위 제약사항으로 설정할 땐 컬럼 한개만 PK로 설정 가능
CREATE TABLE 테이블명(
    컬럼명 데이터형(크기) CONSTRAINT 제약사항명 PRIMARY KEY,
    ...
);
 
-- table level constraint(테이블 단위 제약사항) : 테이블 생성 시 컬럼을 모두 정의한 후 설정
-- PK 여러 컬럼으로 구성되어야 하는 경우 사용 또는 컬럼과 제약사항을 분리하여 정의하는 경우 사용
CREATE TABLE 테이블명(
    컬럼명 데이터형(크기),
    ...,
    CONSTRAINT 제약사항명 PRIMARY KEY(컬럼명, 컬럼명, ...),
    ...
);

-- 번호, 이름, 전화번호, 주민번호, 이메일 저장하는 테이블을 생성
-- 주민번호는 null을 허용하지 않으면서 유일해야함(PK)
CREATE TABLE column_primary(
    num NUMBER,
    name VARCHAR2(15),
    phone VARCHAR2(13),
    ssn CHAR(14) PRIMARY KEY,
    email VARCHAR2(50)
);
 
-- 제약사항명을 명시 안하면 오라클에서 자동생성
-- 그러나 나중에 제약사항 위배 시 알기 어려움-> 제약사항명 명시하는게 좋다
SELECT * FROM user_constraints;

CREATE TABLE column_primary(
    num NUMBER,
    name VARCHAR2(15),
    phone VARCHAR2(13),
    ssn CHAR(14) CONSTRAINT pk_column_primary PRIMARY KEY,
    email VARCHAR2(50)
);

-- 추가가 실패되는 경우(SSN이 PK)
-- 숫자, 날짜는 컬럼이 생략되면 NULL이 입력되고
-- 문자열(고정길이, 가변길이)는 컬럼이 생략되는 경우와 
-- ''(empty)로 데이터가 추가되는 경우에 NULL이 입력된다
 
-- 컬럼이 생략되어 INSERT 되는 경우 (ssn 생략)
INSERT INTO column_primary(num, name, phone, email)
VALUES (4, '오영근', '010-2332-2222', 'oh@test.com');
 
-- 컬럼은 정의되어 있으나 ''인 경우
INSERT INTO column_primary(num, name, phone, ssn, email)
VALUES (4, '오영근', '010-2332-2222', '','oh@test.com');
 
-- table level constraint 
-- PK가 여러 컬럼으로 구성되는 경우
-- 아이템명, 아이템 번호, 설명
-- 아이템번호와 아이템명은 같은 수 없다
CREATE TABLE table_multi_primary(
    item VARCHAR2(60),
    item_num CHAR(9),
    item_desc VARCHAR2(4000),
    CONSTRAINT pk_table_multi_primary PRIMARY KEY(item_num, item)
);

-- 레코드가 없을 때 table_multi_primary테이블에서
-- item을 'ITEM_0001'의 형식으로 조회  
SELECT CONCAT('ITEM_',LPAD(NVL(MAX(item_num), 0)+1, 4, 0)) output
FROM   table_multi_primary
 
-- NVL : 조회된 레코드가 null일 때에만 값을 변경해줌, 레코드가 없으면 동작 안함!
-- NVL을 쓰려면 조회되도록 만들어야함, max()를 쓰면 null이됨, 그 다음엔 NVL 사용.

-- 처음 넣는 거니까 item_num(문자열이지만)을 MAX(숫자)로 넣어 추가가능
INSERT INTO table_multi_primary(item_num, item, item_desc)
VALUES ((SELECT CONCAT('ITEM_', LPAD(NVL(MAX(item_num), 0)+1, 4, 0))
         FROM table_multi_primary),'물폭탄', '일정거리 쾅');
 
-- 두번째 레코드부터 문자열(item_num은 CHAR)은 MAX(숫자)로 가져올 수 없기 때문에 SUBSTR로 숫자만 뽑아내서 MAX 처리
INSERT INTO table_multi_primary(item_num, item, item_desc)
VALUES ((SELECT CONCAT('ITEM_', LPAD(MAX(SUBSTR(item_num, INSTR(item_num,'_')+1))+1,4,0))
         FROM table_multi_primary),'물폭탄2', '일정거리 쾅2');
 
-- 삽입 성공
-- 최초 데이터 입력, 두 컬럼의 값이 모두 다른 경우, 두 컬럼 중 하나의 컬럼만 다른 경우도 삽입 성공
 
-- 삽입 실패
-- 두 컬럼이 모두 같은 값을 가진 경우, 
INSERT INTO table_multi_primary(item_num, item, item_desc)
VALUES ('ITEM_0002','물폭탄2', '설명이 달라도 물폭탄2, ITEM_0002란 PK 두개가 같아서 에러가 날꺼야');
 
-- 둘 중 하나라도 null이 추가될 때
INSERT INTO table_multi_primary(item_num, item_desc)
VALUES('ITEM_0003', '앞선 특정 라이더를 조준하여 미사일 발사');

-- FK의 제약사항명은 제약사항명은 FK_컬럼명
-- 다른 테이블에 동일 컬럼명이 있을 경우 FK_테이블명_컬럼명
 
-- column level constraint : FOREIGN KEY란 키워드를 사용 안함
--                          (table level constraint에서 사용)
CREATE TABLE 테이블명(
    컬럼명 데이터형(크기) CONSTRAINT 제약사항명 REFERENCES 부모테이블명(컬럼명),
    ...
);
 
-- table level constraint
CREATE TABLE 테이블명(
    컬럼명 데이터형(크기),
    ...,
    CONSTRAINT 제약사항명 FOREIGN KEY(적용컬럼명) REFERENCES 부모테이블명(컬럼명)
);

-- 컬럼단위 제약사항 : foreign key 키워드 사용안함(테이블 단위 제약사항 때 기입)
-- table_primary 테이블의 id값을 참조하여 데이터를 추가하는 foreign key 설정
CREATE TABLE column_foreign(
    f_id VARCHAR2(20) CONSTRAINT fk_f_id REFERENCES table_primary(id),
    phone VARCHAR2(13),
    address VARCHAR2(400)
);

-- 추가 성공
-- 부모테이블에 존재하는 값으로 추가
 
INSERT INTO column_foreign(f_id, phone, address)
VALUES('kim', '010-1113-2311', 'SM');
 
-- 같은 아이디로 값을 추가할 수 있다. (중복허용)
INSERT INTO column_foreign(f_id, phone, address)
VALUES('kim', '010-1113-2311', 'JYP');
 
-- null 허용
INSERT INTO column_foreign(phone, address)
VALUES('010-3939-9999', '미스틱');
 
-- 부모테이블에 없는 값을 추가하면 error
-- (부모테이블엔 id컬럼엔 'kim', 'test'만 있음)
INSERT INTO column_foreign(f_id, phone, address)
VALUES('kim1', '010-3939-9999', '미스틱');

-- 부모테이블은 참조하는 자식레코드가 존재한다면 삭제되지 않는다
DELETE FROM table_primary
WHERE id='kim';

-- FK 옵션으로 ON DELETE CASCADE를 설정하지 않았을 때에는 
-- 참조하는 자식레코드를 모두 삭제해야만 부모레코드를 삭제할 수 있음
DELETE FROM column_foreign
WHERE f_id='kim' OR f_id='test';
 
-- 이제 error없이 지워짐
DELETE FROM table_primary
WHERE id='kim' OR id='test';

-- 부모레코드를 삭제하면 참조하는 모든 자식레코드를 삭제하고
-- 부모 레코드를 삭제하는 ON DELETE CASCADE 사용
 
CREATE TABLE test_cascade(
    id VARCHAR2(20),
    address VARCHAR2(100),
    CONSTRAINT fk_test_id FOREIGN KEY(id)
      REFERENCES table_primary(id)
      ON DELETE CASCADE
);
 
-- FK 설정 시 ON DELETE CASCADE가 사용되지 않은 테이블에서
-- 참조하는 레코드는 삭제되지 않는다

-- 생성된 INDEX 확인하기
SELECT * FROM user_indexes;

-- column level constraint
-- 제약사항명은 uk_컬럼명, uk_테이블명_컬럼명 
CREATE TABLE 테이블명(
    컬럼명 데이터형(크기) CONSTRAINT 유니크명 UNIQUE
);
 
-- table level constraint
CREATE TABLE 테이블명(
    컬럼명 데이터형(크기),
    ...,
    CONSTRAINT 유니크명 UNIQUE(컬럼명),
    ...
);

-- column level constraint
-- unique : 값이 없을 수도 있고 있다면 유일해야 할 때
-- 이름, 이메일, 주소를 저장하는 테이블을 생성
 
CREATE TABLE column_unique(
    name     NUMBER(30),
    email    VARCHAR2(50) CONSTRAINT uk_email UNIQUE,
    address  VARCHAR2(100)
);

-- 추가 성공
-- 최초 입력 시
INSERT INTO column_unique(name, email, address)
VALUES('이재찬', 'lee@test.com', '경기도 수원시 영통구');
 
-- 이메일이 없는 경우 (null이 들어가는 경우)
INSERT INTO column_unique(name, email, address)
VALUES('이재찬', '', '경기도 수원시 영통구');
-- email 컬럼 생략으로 null이 들어가는 경우
INSERT INTO column_unique(name, address)
VALUES('이재찬1','경기도 수원시 팔달구');
 
-- 추가 실패하는 경우
-- 같은 이메일이 입력되었을 때
INSERT INTO column_unique(name, email, address)
VALUES('박영민', 'lee@test.com', '부산시');
 
-- UNIQUE는 같은 값이 입력됐을 경우만 에러 발생

-- table level constraint
-- 번호, 이름, 전화번호, 카드번호를 저장하는 테이블 생성
-- 전화번호와 카드번호는 없을 수도 있고 있다면 유일해할 것
CREATE TABLE table_unique(
    num NUMBER(5),
    name VARCHAR2(30),
    tel VARCHAR2(13),
    card_num VARCHAR2(4),
    CONSTRAINT uk_tel UNIQUE(tel),
    CONSTRAINT uk_card_num UNIQUE(card_num)
);
 
-- 최초입력
INSERT INTO table_unique(num, name, tel, card_num)
VALUES(1, '김정운', '010-8798-8999', '1234');
 
-- 전화번호와 카드번호가 다른 경우
INSERT INTO table_unique(num, name, tel, card_num)
VALUES(2,'이재현','010-1111-2222','1235');
 
-- null입력되는 경우
INSERT INTO table_unique(num, name)
VALUES(3,'이재현운');
 
INSERT INTO table_unique(num, name, tel, card_num)
VALUES(3,'이재현운', '', '');
 
-- 전화번호든 카드번호든 둘 중 하나라도 같다면 에러
INSERT INTo table_unique(num, name, tel, card_num)
VALUES(4, '정택성', '010-1111-3333', '1235');
 
INSERT INTO table_unique(num, name, tel, card_num)
VALUES(4, '정택성', '010-1111-2222', '9999');
SELECT * FROM table_unique;

-- 제약사항명은 C_컬럼명, 이름 잘 줌(생략가능)
-- column level constraint
-- CHECK(컬럼명)에서 컬럼명은 다른 테이블의 컬럼명이 들어가면 안됨!
CREATE TABLE 테이블명(
    컬럼명 데이터형(크기) CONSTRAINT 제약사항명 CHECK(컬럼명 연산자 비교값),
    ...
);
 
 
-- table level constraint
CREATE TABLE 테이블명(
    컬럼명 데이터형(크기),
    ...,
    CONSTRAINT 제약사항명 CHECK(컬럼명 연산자 비교값),
    ...
);

CREATE TABLE 테이블명(
    컬럼명 데이터형(크기) CONSTRAINT 제약사항명 NOT NULL,
    ...
);
 
-- NOT NULL은 테이블 단위 제약사항으로는 설정할 수 없음

-- 일반적으로 컬럼명 맨 뒤에 NULL이 생략된 형태다
    컬럼명 데이터형(크기) NULL 
 
-- 문자열(VARCHAR2, CHAR) 컬럼 크기에 byte를 붙일 수 있다
    컬럼명 데이터형(크기byte)

SELEECT * FROM user_tab_cols;
 
CREATE TABLE 테이블명(
    컬럼명 데이터형(크기) DEFAULT 초기값,
    ...
);

-- 계정 잠그기 : 관리자 계정만 가능
ALTER USER 계정명 ACCOUNT LOCK;
 
-- 잠금 풀기
ALTER USER 계정명 ACCOUNT UNLOCK;
 
-- 비밀번호 변경 : 모든 계정 가능
ALTER USER 계정명 IDENTIFIERED BY 변경할비번;
 
 
-- 테이블작업 : 모든 게정 가능 -------------------------------------
-- 테이블명 변경
ALTER TABLE 테이블명 RENAME TO 변경할테이블명;
 
-- 컬럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN 이전컬럼명 TO 변경할컬럼명;
 
-- 컬럼 추가 : 추가된 컬럼은 가장 마지막에 붙는다
ALTER TABLE 테이블명 ADD 추가할컬럼명 데이터형(크기) 제약사항;
 
-- 컬럼의 데이터형 변경 : 레코드가 존재하면 동일데이터형에서 크기만 변경
-- 레코드가 존재하지 않으면 데이터형 자체를 변경
ALTER TABLE 테이블명 MODIFY 변경할컬럼명 데이터형(크기) 제약사항;
 
-- 컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 삭제할컬럼명;
 
-- 제약사항 설정 (table level constraint만 가능)
-- NOT NULL은 column level로만 조건을 줄 수 있기 때문에 MODIFY 사용
ALTER TABLE 테이블명 ADD CONSTRAINT 제약사항명 제약사항종류(적용컬럼명);
 
-- 제약사항 삭제
ALTER TABLE 테이블명 DROP CONSTRAINT 제약사항명;
 
-- 제약사항의 상태 변경(ENABLE, DISABLE)
-- 비활성화(입력되는 레코드의 검증X)에서 활성화가 될 때는 레코드의 입력상태에 따라 다르다
-- 제약사항 위배되는 레코드 존재시 비활성화, 없으면 활성화 
ALTER TABLE 테이블명 ENABLE  CONSTRAINT 제약사항명;
                    DISABLE

-- 계정 잠그기 : 관리자만 가능
ALTER USER scott ACCOUNT LOCK;
 
-- 계정 열기
ALTER USER scott ACCOUNT UNLOCK;
 
-- 비번 변경
ALTER USER scott IDENTIFIED BY 1111;

-- 테이블 생성
CREATE TABLE test_atler(
    num NUMBER,
    neam VARCHAR2(30),
    addr NUMBER
);
 
-- 테이블명 변경
ALTER TABLE test_atler RENAME TO test_alter;
 
-- 컬럼명 변경
ALTER TABLE test_alter RENAME COLUMN neam TO name;
 
 
-- 컬럼의 데이터형 변경 : 레코드가 없으므로 데이터형까지 변경 가능
-- addr의 NUMBER를 VARCHAR2(100), NOT NULL제약사항으로 변경
ALTER TABLE test_alter MODIFY addr VARCHAR2(100) NOT NULL;
 
-- addr 값을갖는 레코드 추가, 다시 addr을 숫자형으로 변경해보자
INSERT INTO test_alter(num, name, addr)
VALUES(1, '테스트', '서울시 강남구');
 
ALTER TABLE test_alter MODIFY addr NUMBER;
 
-- 데이터형을 변경할 때는 레코드가 비어있어야 함

-- 레코드가 존재할때는 동일 데이터형에서 크기만 변경 가능(축소X)
-- '서울시 강남구'는 19바이트
ALTER TABLE test_alter MODIFY addr VARCHAR(18);

-- 컬럼 추가 :추가되는 컬럼은 가장 마지막에 추가됨   
-- 레코드가 존재하지 않으면 모든 제약사항을 부여할 수 있음
-- 레코드가 존재하면 상황에 따라 부여 못하는 제약사항이 생길 수 있음
ALTER TABLE test_alter ADD id VARCHAR2(15);
 
-- 컬럼 삭제 : 안에 값이 있는 레코드도 삭제됨
ALTER TABLE test_alter DROP COLUMN addr;
 
-- 제약사항 추가
ALTER TABLE test_alter ADD CONSTRAINT pk_test_alter PRIMARY KEY(id);
-- 이미 id가 NULL인 레코드가 존재해서 안됨
-- 레코드의 구성이 제약사항을 만족하고 있는지부터 판단하고 제약사항을 추가해야 함

-- 제약사항 비활성화 : 제약조건이 동작하지 않음
ALTER TABLE test_alter DISABLE CONSTRAINT pk_test_alter;
 
-- 제약사항 활성화 : 레코드의 구성에 따라 다름
ALTER TABLE test_alter ENABLE constraint pk_test_alter
 
-- 제약사항 삭제
ALTER TABLE test_alter DROP CONSTRAINT pk_test_alter;