2022-02-04 DDL2

어제 포스팅했던 DDL에 관한 내용을 조금 더 살펴볼 것이다.
오늘 알아볼 것은 제약조건의 PRIMARY KEY, FOREIGN KEY, CHECK 그리고 ALTER이다.

PRIMARY KEY

PRIMATY KEY

• 테이블에서 한 행의 정보를 구분하기위한 고유 식별자의 역할
• NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고있음
  중복도 허용하지 않고, NULL값을 넣는것도 허용하지 않음
• 한 테이블당 한개만 설정 가능
• 컬럼레벨과 테이블레벨에서 설정 가능

CREATE TABLE USER_PK1(
    USER_NO     NUMBER  PRIMARY KEY,    --식별할때 사용하는 컬럼이다.
    USER_ID     VARCHAR2(20) UNIQUE
);

이렇게 USER_NO을 PRIMARY KEY로 설정해주었다. 다른 방식도 보자

CREATE TABLE USER_PK1(
    USER_NO     NUMBER,
    USER_ID     VARCHAR2(20) UNIQUE
    PRIMARY KEY(USER_NO)    -- 테이블 레벨에 선언해줬다.
);

테이블 레벨에 설정해주는 방식이다.

CREATE TABLE USER_PK1(
    USER_NO     NUMBER,
    USER_ID     VARCHAR2(20) UNIQUE
    PRIMARY KEY(USER_NO,USER_ID)    
    -- PRIMARY KEY는 하나만 설정이 가능하지만, 2개를 한꺼번에 설정하는 것은 가능하다.
);

위에서 PRIMARY KEY는 한 테이블당 한 개만 설정 가능하다고 했지만, 이렇게 테이블 레벨에서
설정할 때 2개를 묶어서 설정하는 것은 가능하다.

FOREIGN KEY

FOREIGN KEY

• 참조 무결성을 유지하기 위한 제약조건
• 잠조된 다른 테이블이 제공하는 값만 사용할 수 있도록 제한하는 것
• 참조되는 컬럼과 참조된 컬럼을 통해 테이블간 관계가 형성
• 해당 컬럼은 참조되는 테이블의 컬럼 값 중 하나와 일치하거나 NULL을 가질 수 있음
• 참조되는 테이블의 참조되는 컬럼은 PRIMARY KEY 또는 UNIQUE 제액조건 중에 하나를 가져야함

여기서 참조 무결성이 뭔지 알아보자.

참조 무결성

• 관계 데이터베이스 관계 모델에서 2개의 관련 있던 관계 변수(테이블) 간의 일관성(데이터 무결성)을 말한다. 즉 데이터베이스에서 저장된 값들에 대하여 여러가지 제한을 통하여 데이터에 대한 신뢰를 보장하게 하여 일관성을 유지시켜주는 것이다.

CREATE TABLE SHOP_MEMBER1(
    MEMBER_NO   NUMBER  PRIMARY KEY,
    MEMBER_ID   VARCHAR2(20) NOT NULL UNIQUE,
    MEMBER_NAME VARCHAR2(20)
);

CREATE TABLE SHOPPING_LIST1(
    SHOPPING_NO NUMBER PRIMARY KEY,
    SHOPPING_ID VARCHAR(20) REFERENCES SHOP_MEMBER1(MEMBER_ID) -- 외래키 컬럼레벨 설정 
    SHOPPING_PRODUCT    VARCHAR2(50),
    SHOPPING_DATE       DATE
);

이렇게 사용한다. 먼저 테이블을 하나 만들어주고, 그 테이블의 컬럼을 참조하는 방식이다.
위의 코드는 MEMBER1 테이블의 MEMBER_ID를 참조하고있다.
만약 SHOPPING_ID의 값을 설정해 주었는데, 그 값이 MEMBER_ID에 존재하지 않는다면, 그 값은 집어넣을 수 없다. 이렇게 제약해주는 것 이다.

INSERT INTO SHOP_MEMBER1 VALUES(1,'USER01','유저1');

위와 같은 코드를 작성해서 MEMBER1 테이블에 값을 넣어줬다고하자.
그러면 SHOPPING_ID에 들어갈 수 있는 값은 현재는 'USER01'말고는 없다.

참조를해서 SHOPPING_LIST의 값을 채웠다고 해보자. 근데 MEMBER테이블에서 'USER01'이 삭제된다고하면, SHOPPING_ID에 들어간 값들은 어떻게되는가?
결론부터 말하자면 기본적으론 애초에 'USER01'데이터를 삭제할 수 없다.
SHOPPING_ID가 참조하고있기 때문이다.
그걸 삭제할 수 있도록 만들어주는 코드들이 있다.

ON DELETE SET NULL / ON DELETE CASCADE

이 2 코드이다. 이거로 어떻게 삭제하냐 하면

CREATE TABLE SHOPPING_LIST2(
    SHOPPING_NO NUMBER PRIMARY KEY,
    SHOPPING_ID VARCHAR(20) REFERENCES SHOP_MEMBER2(MEMBER_ID) ON DELETE SET NULL, 
    -- 외래키 컬럼레벨 설정 // SET NULL 삭제 설정 포함
    SHOPPING_PRODUCT    VARCHAR2(50),
    SHOPPING_DATE       DATE
);

CREATE TABLE SHOPPING_LIST3(
    SHOPPING_NO NUMBER PRIMARY KEY,
    SHOPPING_ID VARCHAR(20) REFERENCES SHOP_MEMBER2(MEMBER_ID) ON DELETE CASCADE, 
    -- 외래키 컬럼레벨 설정 // CASCADE 삭제 설정 포함
    SHOPPING_PRODUCT    VARCHAR2(50),
    SHOPPING_DATE       DATE
);

요로코롬 REFERENCES 하고서 옆에 삭제 설정을 넣어주면 된다.
왜 2개나 있냐하면, 이 2개가 삭제하고 난 결과값을 달리 준다.
ON DELETE SET NULL은 'USER01'을 삭제하면 'USER01'값을 가진 SHOPPING_LIST2 테이블의 'USER01'값만 NULL로 만들어준단다는 뜻이다.

INSERT INTO SHOPPING_LIST2 VALUES(1,'USER01','맥북',SYSDATE);
-> DELETE FROM SHOP_MEMBER2 WHERE MEMBER_ID='USER01';
-> SHOPPING_LIST2 VALUES == (1,NULL,'맥북',SYSDATE);

알기쉽게 정리해봤다. 중간에 들어간 DELETE가 삭제하는 코드이다.
그럼 ON DELETE CASCADE는 어떻게되는가?

INSERT INTO SHOPPING_LIST2 VALUES(1,'USER01','맥북',SYSDATE);
-> DELETE FROM SHOP_MEMBER2 WHERE MEMBER_ID='USER01';
-> SHOPPING_LIST2 VALUES == X;

얘는 그냥 포함되어있던 ROW 자체를 없애버린다.

FOREIGN KEY의 설정 방법의 종류만 알아보고 다음으로 넘어가자.

• 외래키 테이블레벨 설정

CREATE TABLE SHOPPING_LIST11(
    SHOPPING_NO NUMBER PRIMARY KEY,
    SHOPPING_ID VARCHAR2(20),
    FOREIGN KEY (SHOPPING_ID) REFERENCES SHOP_MEMBER1(MEMBER_ID) 
    -- 외래키 테이블레벨 설정
);

• 외래키 컬럼레벨 설정

CREATE TABLE SHOPPING_LIST111(
    SHOPPING_NO NUMBER PRIMARY KEY,
    SHOPPING_ID NUMBER REFERENCES SHOP_MEMBER1(MEMBER_NO), 
    -- 외래키 컬럼레벨 설정
);

• 참조하는 컬럼이 해당 테이블의 PRIMARY KEY인 경우

CREATE TABLE SHOP_MEMBER1(
    MEMBER_NO   NUMBER  PRIMARY KEY, ----- 요거요거
    MEMBER_ID   VARCHAR2(20) NOT NULL UNIQUE,
    MEMBER_NAME VARCHAR2(20)
);

CREATE TABLE SHOPPING_LIST1111(
    SHOPPING_NO NUMBER PRIMARY KEY,
    SHOPPING_ID NUMBER REFERENCES SHOP_MEMBER1, 
    -- 참조하려는 컬럼이 해당 테이블의 PRIMARY KEY인 경우 컬럼이름 생략 가능
);

CHECK

CHECK

• 해당 컬럼에 입력되거나 수정되는 값을 체크하여, 설정된 값 이외의 값이면 에러 발생
• 비교연산자를 이용하여 조건을 설정하며, 비교값은 리터럴만 사용

예시 바로 긔

CREATE TABLE USER_CHECK(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PW VARCHAR(30),
    USER_NAME   VARCHAR2(20),
    GENDER  CHAR(6) CHECK(GENDER IN ('남자', '여자')), 
    -- CHECK를 사용해서 GENDER값에 들어올 수 있는 걸 '남자', '여자'로만 제한함
    PHONE   CHAR(13)
);

이게 끝이다. 그냥 입력값을 제한하는거다. 뭐 설명할게 없다...

테이블에서 사용하는 기능맛을 조금만 보자.

DROP

DROP : CREATE로 정의된 객체를 삭제할때 사용
CREATE TABLE DEL_TBL1(
    DATA1 NUMBER PRIMARY KEY,
    DATE2 VARCHAR2(20)
);

INSERT INTO DEL_TBL1 VALUES(1,'TEST1');
INSERT INTO DEL_TBL1 VALUES(2,'TEST2');
SELECT * FROM DEL_TBL1;
DROP TABLE DEL_TBL1; 
-- 안쪽에 INSERT해놓았던 정보까지 다 지워버려서 다시 테이블을 생성해줘도 데이터가 복구되지않는다.

DROP은 그냥 테이블을 삭제하는 기능이다. INSERT해준 값은 물론 그냥 테이블도 사라진거다.
근데 이것도 참조하고있으면 삭제가 안된다.

DROP TABLE DEL_TBL1 CASCADE CONSTRAINTS; 
-- CASCADE CONSTRAINTS : 이악물고 삭제하고 싶을때 사용한다. 
-> 참조하고있는 해당 테이블에 연결된 제약조건을 모두 삭제한다.

CASCADE CONSTRAINS를 사용하면 다 삭제가 되긴 되는데, 왠만하면 사용하지말자.

DEFUALT

DEFAULT : 테이블 생성 시 컬럼의 기본값을 설정
CREATE TABLE TEST_TBL(
    DATA1   NUMBER  PRIMARY KEY,
    DATA2   NUMBER  DEFAULT 100,
    DATA3   VARCHAR(20) DEFAULT '기본값'
);

INSERT INTO TEST_TBL VALUES(1,10,'문자열');
SELECT * FROM TEST_TBL;
INSERT INTO TEST_TBL VALUES(2,DEFAULT,DEFAULT);
INSERT INTO TEST_TBL VALUES(3,DEFAULT,'테스트');

INSERT INTO TEST_TBL (DATA1,DATA2,DATA3) VALUES(4,1000,'안녕');
INSERT INTO TEST_TBL (DATA1) VALUES(5); 
-- 테이블명 (컬럼이름)의 형태를 사용할때 다른 값을 안넣어주어도 자동으로 기본값을 넣어준다.

DEFUALT를 넣어줬을때, 그냥 생성되는 기본값을 만드는 기능이다.
마지막에 INSERT해준건 값을 열어보면 (5,100,'기본값')의 값을 가지고있다.

ALTER

ALTER

• DDL의 한 종류로 CREATE로 정의 된 내용을 수정할 때 사용
• 컬럼의 추가/삭제, 제약조건의 추가/삭제, 컬럼의 자료형 변경, 테이블명/컬럼명/제약조건 이름 변경 등이 가능

ALTER의 다양한 기능을 살펴보자.

컬럼 추가 -- ADD

CREATE TABLE ALTER_TEST(
    NAME    VARCHAR2(20),
    AGE     NUMBER,
    ADDR    VARCHAR2(100)
);

ALTER TABLE ALTER_TEST
ADD (NICKNAME VARCHAR2(30));

ALTER TABLE ALTER_TEST
ADD (TEST_COL VARCHAR2(20) DEFAULT '뚱랑이');

컬럼 추가를 하려면 ADD해주면 된다. 이러면 ALTER_TEST 테이블은
NAME/AGE/ADDR/NICKNAME/TEST_COL 의 칼럼을 가지게된다.
기본적으로 ADD해준 칼럼의 값은 모두 NULL값이다.
DEFAULT값을 설정해준다면 NULL 대신에 DEFAULT값이 들어간다.

컬럼 수정 -- MODIFY

ALTER TABLE ALTER_TEST
MODIFY NICKNAME VARCHAR2(100)
MODIFY TEST_COL VARCHAR(50);

위에서 부터 계속 이어서 가보자. 위의 코드를 입력하면 데이터 크기가 수정된다.

컬럼 삭제 -- DROP

ALTER TABLE ALTER_TEST
DROP COLUMN TEST_COL; 
-- 삭제하려는 컬럼에 제약조건이 있으면 삭제 불가
-- DROP COLUMN 컬럼명 CASCADE COMSTRAINTS -> 해당컬럼에 설정되어있는 제약 모두 지우고 삭제

이러면 아까 추가해줬던 TEST_COL 컬럼이 삭제된다.

제약조건 추가 -- ADD CONSTRAINT / CONSRTRAINT

-- ALTER_TEST 테이블의 NAME컬럼 PRIMARY KEY, AGE UNIQUE, ADDR NOT NULL
ALTER TABLE ALTER_TEST
ADD CONSTRAINT NAME_PK PRIMARY KEY(NAME)  -- 컬럼 이름 -> 제약조건 -> (제약조건 들어갈 컬럼) 순으로 입력
ADD CONSTRAINT AGE_UQ UNIQUE (AGE);
SELECT * FROM ALTER_TEST;

ALTER TABLE ALTER_TEST
MODIFY ADDR CONSTRAINT ADDR_NN NOT NULL; -- NOT NULL은 MODIFY를 통해서 추가해야한다

이미 들어가있는 컬럼에 제약조건을 추가해주는 방법이다. 제약조건을 추가해주는 컬럼의 이름을 따로 정해주고, 제약조건을 정한 후 그 조건이 들어가는 컬럼의 순서대로 입력하면 된다.
NOT NULL 제약조건을 넣고싶다면 MODIFY를 사용해야한다.

제약조건 삭제 -- DROP CONSTRAINT

ALTER TABLE ALTER_TEST
DROP CONSTRAINT NAME_PK     -- 제약조건 삭제 DROP CONSTRAINT 제약조건 이름
DROP CONSTRAINT NAME_UQ
MODIFY ADDR NULL;           -- NOT NULL인 경우 MODIGY로 처리

아까 설정해주었던 그 제약조건 컬럼의 이름을 넣어야한다. 이거 직접 찾아봐야한다.

제약조건 이름 변경 -- RENAME

ALTER TABLE ALTER_TEST
RENAME CONSTRAINT NAME_UQ TO CHANGE_UQ; 
-- RENAME CONSTRAINT 기존 제약조건 이름 TO 바꿀 제약조건 이름

RENAME을 사용하여 제액조건 컬럼의 이름을 변경할 수 있다.

컬럼이름 변경 RENAME COLUMN

ALTER TABLE ALTER_TEST
RENAME COLUMN NICKNAME TO NICK_NAME;

물론 제약조건 컬럼의 이름 말고, 그냥 컬럼의 이름을 바꿔주는 기능도 있다.
RENAME COLUMN을 사용해 NICKNAME 컬럼을 NICK_NAME으로 바꿔주었다.

테이블 이름 변경 RENAME TO

ALTER TABLE ALTER_TEST
RENAME TO CHANGE_TABLE_TEST;

SELECT * FROM CHANGE_TABLE_TEST;

RENAME TO를 이용하여, 테이블의 이름을 CHANGE_TABLE_TEST로 바꾸었다.
이제 검색해줄때 CHANGE_TABLE_TEST로 찾아야한다.

이렇게 오늘의 포스팅을 마치겠다.
안뇽!