[Oracle] PRIMARY KEY(기본키) 제약조건

• PRIMARY KEY(기본키) 제약조건
  : 테이블에서 한 행의 정보를 찾기 위해 사용하는 컬럼을 의미한다.
  
  • 테이블에 대한 식별자 역할을 한다.
  • NOT NULL + UNIQUE 제약조건의 의미
  • 한 테이블당 한 개만 설정할 수 있음
  • 컬럼 레벨, 테이블 레벨 둘 다 설정 가능함
  • 한 개 컬럼에 설정할 수도 있고, 여러 개의 컬럼을 묶어서 설정할 수 있음

CREATE TABLE USER_PRIMARYKEY (
  USER_NO NUMBER CONSTRAINT PK_USER_NO PRIMARY KEY, --컬럼 레벨
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50)
);

INSERT INTO USER_PRIMARYKEY
VALUES(1, 'user01', 'pass01', '홍길동', '남',
       '010-1234-5678', 'hong123@kg.or.kr');
       
INSERT INTO USER_PRIMARYKEY
VALUES (1, 'user02', 'pass02', '이순신', '남',
        '010-5678-9012', 'lee123@kh.or.kr');

INSERT INTO USER_PRIMARYKEY
VALUES (NULL, 'user03', 'pass03', '유관순', '여',
        '010-9999-3131', 'yoo123@kh.or.kr');

SELECT UC.TABLE_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_NAME,
       UC.CONSTRAINT_TYPE
  FROM USER_CONSTRAINTS UC
  JOIN USER_CONS_COLUMNS UCC ON (UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
 WHERE UC.CONSTRAINT_NAME = 'PK_USER_NO';

SELECT
       UC.TABLE_NAME
     , UCC.COLUMN_NAME
     , UC.CONSTRAINT_NAME
     , UC.CONSTRAINT_TYPE
  FROM USER_CONSTRAINTS UC
  JOIN USER_CONS_COLUMNS UCC ON (UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
 WHERE UC.CONSTRAINT_NAME = 'PK_USER_NO';
 
CREATE TABLE USER_PRIMARYKEY2(
  USER_NO NUMBER,
  USER_ID VARCHAR2(20),
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GERNDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  CONSTRAINT PK_USER_NO2 PRIMARY KEY (USER_NO, USER_ID)  -- 테이블에 제약조건(USER_NO, USER_ID)묶어서 하나에 PRIMARY KEY 제약조건설정
);

INSERT INTO USER_PRIMARYKEY2
VALUES (1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_PRIMARYKEY2
VALUES (1, 'user02', 'pass02', '이순신', '남', '0105678-9012', 'lee123@kh.or.kr');

INSERT INTO USER_PRIMARYKEY2
VALUES (2, 'usero1', 'pass01', '유관순', '여', '010-9999-3131', 'yoo123@kh.or.kr');

SELECT
       *
  FROM USER_PRIMARYKEY2;

INSERT INTO USER_PRIMARYKEY2
VALUES (1, 'user01', 'pass01', '신사임당', '여', '010-9999-9999', 'shin123@kh.or.kr');

SELECT
       UC.TABLE_NAME
     , UCC.COLUMN_NAME
     , UC.CONSTRAINT_NAME
     , UC.CONSTRAINT_TYPE
  FROM USER_CONSTRAINTS UC
  JOIN USER_CONS_COLUMNS UCC ON (UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
 WHERE UC.TABLE_NAME = 'USER_PRIMARYKEY2'; 

-- FOREIGN KEY(외부키 / 외래키) 제약조건
-- 참조(REFERENCES)된 다른 테이블에서 제공하는 값만 사용할 수 있음
-- 참조 무결성을 위배하지 않게 하기 위해 사용
-- FOREIGN KEY 제약조건에 의해서
-- 테이블간의 관계(RELATIONSHIP)가 형성됨
-- 제공되는 값 외에는 NULL을 사용할 수 있음

-- 컬럼레벨일 경우
-- 컬럼명 자료형(크기) [CONSTRAINT 이름] REFERENCES 참조할테이블 [(참조할컬럼)] [삭제룰]

-- 테이블 레벨일 경우
-- [CONSTRAINT 이름] FOREIGN KEY(적용할컬럼명) REFERENCES 참조할테이블명 [(참조할컬럼)] [삭제룰]

-- 참조할 테이블의 참조할 컬럼명이 생략되면
-- PRIMARY KEY로 설정된 컬럼이 자동 참조할 컬럼이 됨

-- 참조될 수 있는 컬럼은 PRIMARY KEY 컬럼과,
-- UNIQUE 지정된 컬럼만 외래키로 사용할 수 있음
CREATE TABLE USER_GRADE (
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO USER_GRADE
VALUE (10, '일반회원');
INSERT INTO USER_GRADE
VALUE (20, '우수회원');
INSERT INTO USER_GRADE
VALUE (30, '특별회원');

COMMIT;

SELECT * FROM USER_GRADE;

CREATE TABLE USER_FOREIGNKEY(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  GRADE_CODE NUMBER,
  CONSTRAINT FK_GRADE_CODE FOREIGN KEY(GRADE_CODE)
  REFERENCES USER_GRADE (GRADE_CODE)
);

INSERT INTO USER_FOREIGNKEY
VALUES(1, 'user01', 'pass01', '홍길동', '남',
       '010-1234-5678', 'hong123@kg.or.kr', 10);

INSERT INTO USER_FOREIGNKEY
VALUES (2, 'user02', 'pass02', '이순신', '남',
        '010-5678-9012', 'lee123@kh.or.kr', 20);

INSERT INTO USER_FOREIGNKEY
VALUES (3, 'user03', 'pass03', '유관순', '여',
        '010-9999-3131', 'yoo123@kh.or.kr', 30);

INSERT INTO USER_FOREIGNKEY
VALUES (4, 'user04', 'pass04', '안중근', '남',
        '010-2222-0000', 'ahn123@kh.or.kr', null);

INSERT INTO USER_FOREIGNKEY
VALUES (5, 'user05', 'pass05', '윤봉길', '남',
        '010-6666-1234', 'yoon123@kh.or.kr', 50);

COMMIT;

SELECT 
       UC.TABLE_NAME
     , UCC.COLUMN_NAME
     , UC.CONSTRAINT_NAME
     , UC.CONSTRAINT_TYPE
  FROM USER_CONSTRAINTS UC
     , USER_CONS_COLUMNS UCC
 WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
   AND UC.CONSTRAINT_NAME = 'FK_GRADE_CODE';
   -- CONSTRAINT_TYPE = R 은 REFERENCES를 의미
   
-- USER_FOREIGNKEY 테이블에서
-- 회원 아이디, 이름, 성별, 연락처, 회원등급명 조회
SELECT
       USER_ID
     , USER_NAME
     , GENDER
     , PHONE
     , GRADE_NAME
  FROM USER_FOREIGNKEY
--  LEFT JOIN USER_GRADE USING (GRADE_CODE);
NATURAL LEFT JOIN USER_GRADE; -- 참조할 컬럼명을 써주지 않아도 외래키를 자동으로 기본키로 설정.
-- OUTER JOIN의 경우 방향을 설정해줘야 하지만 INNER JOIN의 경우 생략해도 무관

DROP TABLE USER_GRADE;

CREATE TABLE USER_FOREIGNKEY2 (
 USER_NO NUMBER PRIMARY KEY,
 USER_ID VARCHAR2(20) UNIQUE,
 USER_PWD VARCHAR2(30) NOT NULL,
 USER_NAME VARCHAR2(30),
 GENDER VARCHAR2(10),
 PHONE VARCHAR2(30),
 EMAIL VARCHAR2(50),
 GRADE_CODE NUMBER,
 CONSTRAINT FK_GRADE_CODE2 FOREIGN KEY (GRADE_CODE)
 REFERENCES USER_GRADE2 (GRADE_CODE) ON DELETE SET NULL
);

-- ON DELETE CASCADE : 부모 키 삭제 시 자식 키를 가진 행도 함께 삭제
CREATE TABLE USER_GRADE3 (
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO USER_GRADE3
VALUE (10, '일반회원');
INSERT INTO USER_GRADE3
VALUE (20, '우수회원');
INSERT INTO USER_GRADE3
VALUE (30, '특별회원');

CREATE TABLE USER_FOREIGNKEY3 (
 USER_NO NUMBER PRIMARY KEY,
 USER_ID VARCHAR2(20) UNIQUE,
 USER_PWD VARCHAR2(30) NOT NULL,
 USER_NAME VARCHAR2(30),
 GENDER VARCHAR2(10),
 PHONE VARCHAR2(30),
 EMAIL VARCHAR2(50),
 GRADE_CODE NUMBER,
 CONSTRAINT FK_GRADE_CODE3 FOREIGN KEY (GRADE_CODE)
 REFERENCES USER_GRADE3 (GRADE_CODE) ON DELETE SET NULL
);

COMMIT;

SELECT * FROM USER_GRADE3;
SELECT * FROM USER_FOREIGNKEY3;

DELETE FROM USER_GRADE3
WHERE GRADE_CODE = 10;

-- 서브쿼리를 이용한 테이블 생성
-- 컬럼명, 데이터 타입, 값이 복사가 되고,
-- 제약조건은 NOT NULL만 복사됨
CREATE TABLE EMPLOYEE_COPY
AS SELECT * FROM EMPLOYEE;

SELECT * FROM EMPLOYEE_COPY;

-- 테이블의 데이터를 제외한 구조만 복사
CREATE TABLE EMPLOYEE_COPY2
AS SELECT *
     FROM EMPLOYEE
    WHERE 1 = 0;

SELECT * FROM EMPLOYEE_COPY2;

CREATE TABLE EMPLOYEE_COPY3
AS SELECT
          EMP_ID
        , EMP_NAME
        , SALARY
        , DEPT_TITLE
        , JOB_NAME
     FROM EMPLOYEE
     LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
     LEFT JOIN JOB USING(JOB_CODE);

SELECT * FROM EMPLOYEE_COPY3;