2021.07.06 수업일지

- 제약조건 Review

- DML : INSERT UPDATE DELETE의 경우 COMMIT을 해야한다.

- ALTER 테이블의 구조 변경하여 컬럼 추가(ADD) 변경(MIODIFY) 삭제(DROP)를 할 수 있다.

- 제약조건 삭제 ; ALTER TABLE '테이블 명' DROP CONSTRAINTS '제약조건 명';

- 무결성 제약조건 *****

1. PRIMARY KEY : 하나의 특정레코드를 식별하기 위한 제약조건(NOT NULL + UNIQUE, 개체무결성을 보장하기위한 수단 , 자동으로 인덱스화 되어서 검색이 쉬워진다.) ****
2. FOREIGN KEY : 참조 무결성, 자식 테이블의 외래키는 부모테이블의 기본키, UNIQUE COLUMN을 참조한다.
3. NOT NULL : 컬럼값으로 NULL을 허용하지 않는다.
4. UNIQUE : 컬럼값으로 중복을 허용하지 않는다.
5. CHECK :  성별컬럼('남', '여') SAL(1000 ~ 5000) 등의 조건 많이 사용 X
이중검증은 하지 않기 때문이다. 자바에서 충분히 검증이 가능하다.
6. DEFAULT : 제약조건은 아님, 컬럼의 기본값 설정

 

- 제약조건을 가지는 테이블 생성 REVIEW

- 부모 테이블 DEPT2을 삭제후 다시 만들어보자.

DROP TABLE DEPT2;
CREATE TABLE DEPT2(
    DEPTNO NUMBER(2,0), -- PRIMARY KEY -- 컬럼단에 제약조건 설정
    DNAME NVARCHAR2(10), -- 가변문자열, 메모리낭비적음
    -- TEL NCHAR(13) -- 고정문자열, 속도빠름
    LOC NVARCHAR2(10),
    -- 테이블단에 제약조건 설정 / 이름은 가독성 있게
    CONSTRAINTS PK_DEPT2_DEPTNO PRIMARY KEY(DEPTNO)
);

ㄴ 전화번호는 고정된 문자가 13자기(- 포함)이기 때문에 NCHAR와 같은 고정문자열을 지정해주는게 좋다.
ㄴ 제약조건은 테이블단에 넣는게 좋다. 이름은 가독성 있게 지정한다.

※ 근원적인 정보는 메타데이터라고 한다.

INSERT INTO DEPT2 VALUES (1, '총무부','서울');
INSERT INTO DEPT2 VALUES (2, '영업부','서울');
INSERT INTO DEPT2 VALUES (3, '개발부','인천');

ㄴ 값을 입력한다.

UPDATE DEPT2 SET LOC = '서울'
WHERE DEPTNO=3;

ㄴ DEPTNO = 3의 LOC을 '서울'로 변경해보자.

INSERT INTO DEPT2 VALUES (4, '인사부',NULL);

ㄴ 만약에 값을 못받았다면 위와 같이 NULL을 쓰거나 '' 와 같이 써도 된다.

 

- 외래키 설정 ***

- 자식테이블 생성

CREATE TABLE EMP2(
    EMPNO NUMBER(4), -- PK(기본키)
    ENAME NVARCHAR2(10), --가변
    SAL NUMBER, --자리수 상관없이 정수, 실수
    HIREDATE DATE,
    DEPTNO NUMBER(2,0), -- FK(외래키)
    -- 테이블단 제약조건 설정
    CONSTRAINTS PK_E_EMPNO PRIMARY KEY(EMPNO),
    -- 외래키는 참조할 부모테이블(부모컬럼)도 와야한다.
    CONSTRAINTS FK_E_DEPTNO FOREIGN KEY(DEPTNO)
    REFERENCES DEPT2(DEPTNO)
);

 

-- 확인
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME='EMP2' OR TABLE_NAME='DEPT2'; -- 제약조건 확인
SELECT * FROM EMP2; --테이블 및 컬럼 설정 확인

 

-- 외래키 제약 조건 삭제
ALTER TABLE EMP2
DROP CONSTRAINTS FK_E_DEPTNO;

 

-- 외래키 제약 조건 추가
ALTER TABLE EMP2
ADD CONSTRAINTS FK_E_DEPTNO 
FOREIGN KEY(DEPTNO) REFERENCES DEPT2(DEPTNO);

 

※ 참고 옵션 ㄱ

-- 외래키 제약 조건 및 옵션 추가
ALTER TABLE EMP2
ADD CONSTRAINTS FK_E_DEPTNO 
FOREIGN KEY(DEPTNO) REFERENCES DEPT2(DEPTNO) 
ON DELETE CASCADE; -- '부모 레코드 삭제시 자식 레코드도 같이 삭제 하라'
-- ON DELETE SET NULL; -- '부모 레코드 삭제시 자식 레코드를 NULL로 설정하라'

ㄴ 붙이는 경우를 잘 생각해야 한다. 아무때나 붙이면 안된다.

 

-- EMP2 TABLE INSERT INTO DATA
DESC EMP2; -- 구조 확인
INSERT INTO EMP2(EMPNO, ENAME, SAL, HIREDATE, DEPTNO)
VALUES(1111, 'KIM',2000,'2020-07-01', 1);
INSERT INTO EMP2(EMPNO, ENAME, SAL, HIREDATE, DEPTNO)
VALUES(2222, 'LEE',3000,SYSDATE, 2);
INSERT INTO EMP2(EMPNO, ENAME, SAL, HIREDATE, DEPTNO)
VALUES(3333, 'PARK',3000,SYSDATE,NULL); -- 외래키에는 NULL값을 허용한다.
INSERT INTO EMP2(EMPNO, ENAME, SAL, HIREDATE, DEPTNO)
VALUES(4444, 'CHA',3000,SYSDATE, 2); -- 외래키는 중복값을 허용한다. 기본키의 값만 가지면 된다.
INSERT INTO EMP2(EMPNO, ENAME, SAL)
VALUES(5555, 'CHOI',4000); -- 컬럼을 지정하지 않는다면 해당 데이터는 NULL이 된다.
INSERT INTO EMP2 -- 생략해도 되지만 컬럼 순서를 반드시 파악해야한다.
VALUES(6666, 'IM',5000,SYSDATE, 3);

 

-- 부모테이블 데이터 삭제
DELETE FROM DEPT2 WHERE DEPTNO=2;

명령의 1,413 행에서 시작하는 중 오류 발생 - DELETE FROM DEPT2 WHERE DEPTNO=2 오류 보고 - ORA-02292: integrity constraint (ICIA.FK_E_DEPTNO) violated - child record found

ㄴ 부모 PK_DEPT2_DEPTNO 없는 자식 FK_EMP2_DEPTNO 이 되어버리기 때문에 삭제할 수 없다.

 

-- 자식 테이블 데이터 삭제 후 --> 부모 테이블 데이터 삭제
DELETE FROM EMP2 WHERE DEPTNO=2;
DELETE FROM DEPT2 WHERE DEPTNO=2;

2개 행 이(가) 삭제되었습니다.


1 행 이(가) 삭제되었습니다.

 

-- 확인했으면 롤백하도록 하자.
ROLLBACK;

 

-- 자식 테이블 데이터 삭제 또는 수정 후 --> 부모 테이블 데이터 삭제
-- 부서번호 2번이 3번으로 변경되었을때
UPDATE EMP2 SET DEPTNO = 3
WHERE DEPTNO = 2;
DELETE FROM DEPT2 WHERE DEPTNO=2;

 

- UNIQUE (컬럼값으로 중복을 허용하지 않는다) 설정 **

- 테이블 삭제 후 테이블 생성

DROP TABLE DEPT3;
CREATE TABLE DEPT3(
    DEPTNO NUMBER, -- PK
    DNAME NVARCHAR2(10),
    LOC NVARCHAR2(10),
    -- 테이블 단 설정
    CONSTRAINTS PK_D3_DEPTNO PRIMARY KEY(DEPTNO),
    CONSTRAINTS UQ_D3_DNAME UNIQUE(DNAME)
);

 

- 제약조건 확인

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME='DEPT3';

DESC DEPT3;

 

- 데이터 입력

INSERT INTO DEPT3 VALUES (1, '총무부','서울');
INSERT INTO DEPT3 VALUES (2, '영업부','서울');
INSERT INTO DEPT3 VALUES (3, '개발부','인천');
INSERT INTO DEPT3 VALUES (4, '인사부','인천');

ㄴ 데이터 입력하면서 제약조건 설정한 컬럼을 확인해보자.

 

- CHECK :  프런트 단에서 이미 검증하기 때문에 많이 사용하지 않는다.

- 테이블 및 컬럼 생성

CREATE TABLE EMP3(
    EMPNO NUMBER,
    ENAME NVARCHAR2(10),
    SAL NUMBER(7,2),
    GENDER NCHAR(2),
    -- 테이블단 설정
    CONSTRAINTS PK_E3_EMPNO PRIMARY KEY(EMPNO),
    CONSTRAINTS CK_E3_SAL CHECK(SAL BETWEEN 500 AND 5000),
    CONSTRAINTS CK_E3_GEN CHECK(GENDER IN ('남자','여자'))
);

 

- 컬럼 데이터 입력

INSERT INTO EMP3(EMPNO, ENAME, SAL, GENDER)
VALUES(1111, 'LEE', 500, '남자');
INSERT INTO EMP3(EMPNO, ENAME, SAL, GENDER)
VALUES(2222, 'PARK', 5000, '여자');
INSERT INTO EMP3(EMPNO, ENAME, SAL, GENDER)
VALUES(3333, 'CHOI', 3000, '남자');
INSERT INTO EMP3(EMPNO, ENAME, SAL, GENDER)
VALUES(4444, 'KIM', 4000, '완자');

ㄴ 데이터 입력하면서 제약조건 설정한 컬럼을 확인해보자.

 

- NOT NULL : 컬럼값으로 NULL을 허용하지 않는다.

- NOT NULL은 유일하게 컬럼단에만 설정이 가능하다. 테이블단에 불가능하다.
- 테이블과 컬럼을 생성해보자.

CREATE TABLE DEPT4(
    DEPTNO NUMBER(4),
    DNAME NVARCHAR2(20) NOT NULL, --무조건 부서 이름을 넣게 해보자.
    LOC NVARCHAR2(20) DEFAULT '인천' NOT NULL, -- 무조건 지역을 넣어 보자.
    -- 디폴트와 NOT NULL의 순서를 기억하도록 하자.
    -- 값이 안들어가면 '인천'을 넣는다.
    -- DEFAULT는 제약조건은 아니지만 컬럼단에만 설정할 수 있다.
    CONSTRAINTS PK_D4_DEPTNO PRIMARY KEY(DEPTNO)
);

 

- 컬럼에 데이터를 넣자.

INSERT INTO DEPT4 VALUES(1,'총무부','서울');
INSERT INTO DEPT4 VALUES(2,'영업부',DEFAULT);

ㄴ 데이터 입력하면서 제약조건 설정한 컬럼을 확인해보자.

 

- NULL 허용해보자.

ALTER TABLE DEPT4
MODIFY DNAME NULL;

 

- NOT NULL 설정

ALTER TABLE DEPT4
MODIFY DNAME NOT NULL;

 

- DEFAULT 값 변경

ALTER TABLE DEPT4
MODIFY LOC DEFAULT '서울';

 

- 조합키(슈퍼키)

- 조합키(슈퍼키)
- 컬럼추가 없이 이름과 전화번호를 합쳐서 기본키를 설정할 수 있을까?
- 컬럼을 두개 세개 묶어서 기본키를 설정할 수 있을까?
- 기본키는 하나이어야만 하지만 외래키는 몇개도 상관없다.

- 멤버라는 이름의 회원테이블을 만들어 보자.

CREATE TABLE MEMBER(
    MNAME NVARCHAR2(10),
    TEL NCHAR(13),
    GNEDER NCHAR(2) NOT NULL,
    ADDRESS NVARCHAR2(50) NOT NULL,
    CONSTRAINT PK_MNAME_TEL_MEMBER PRIMARY KEY(MNAME, TEL) -- 조합키
);

 

- 카트라는 이름의 회원 구매 정보를 만들어 보자.

 

CREATE TABLE CART(
    NO NUMBER, --PK
    PRODUCTNO NUMBER NOT NULL, --FK1
    -- M_ID NVARCHAR2(10) -- FK
    -- MEMBER에서 기본키를 사용한게 아니라 조합키를 사용했기 때문에 
    M_NAME NVARCHAR2(10) NOT NULL, --FK2
    M_TEL NCHAR(13) NOT NULL, --FK2
    CONSTRAINT PK_NUM_CART PRIMARY KEY(NO),
    CONSTRAINT FK_NAME_TEL FOREIGN KEY(M_NAME,M_TEL)
    REFERENCES MEMBER(MNAME, TEL)
);

 

- 외래 조합키를 확인해보자.

INSERT INTO CART VALUES(1,1,'KIM','111-2222');
INSERT INTO CART VALUES(2,2,'KIM','444-4444');

 

- 회원, 주문, 상품 테이블 생성 및 컬럼 데이터 입력(PK, FK, UNIQUE, NOT NULL 입력)

EX)

 

코드)

DROP TABLE CART;
DROP TABLE MEMBER;
CREATE TABLE MEMBER(
    M_ID NVARCHAR2(10), --PK
    M_NAME NVARCHAR2(10) NOT NULL,
    M_TEL NVARCHAR2(13) NOT NULL,
    GENDER NVARCHAR2(2) NOT NULL,
    ADDRESS NVARCHAR2(50) NOT NULL,
    CONSTRAINT PK_M_ID_SHOPM PRIMARY KEY(M_ID),
    CONSTRAINT UQ_M_TEL_SHOPM UNIQUE(M_TEL)
);
ALTER TABLE MEMBER
MODIFY GENDER DEFAULT '남자';

DESC MEMBER;
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'MEMBER';

INSERT INTO MEMBER(M_ID,M_NAME,M_TEL,GENDER,ADDRESS)
VALUES('AAA','이주성','111-222',DEFAULT,'인천시 미추홀구');
INSERT INTO MEMBER(M_ID,M_NAME,M_TEL,GENDER,ADDRESS)
VALUES('BBB','이효정','222-333','여자','인천시 부평구');
INSERT INTO MEMBER(M_ID,M_NAME,M_TEL,GENDER,ADDRESS)
VALUES('CCC','이방원','333-444',DEFAULT,'인천시 강화군');
SELECT * FROM MEMBER;

DROP TABLE PRODUCT;
CREATE TABLE PRODUCT(
    P_CODE NCHAR(1), --PK
    P_NAME NVARCHAR2(10),
    P_VAL NUMBER DEFAULT 1000, --디폴트
    P_STOCK NUMBER DEFAULT 1, --디폴트
    CONSTRAINTS PK_PC_PRODUCT PRIMARY KEY(P_CODE)
);

DESC PRODUCT;
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'PRODUCT';

INSERT INTO PRODUCT(P_CODE,P_NAME,P_VAL,P_STOCK)
VALUES('A','냉장고',1000000,50);
INSERT INTO PRODUCT(P_CODE,P_NAME,P_VAL,P_STOCK)
VALUES('B','TV',2000000,100);
INSERT INTO PRODUCT(P_CODE,P_NAME,P_VAL,P_STOCK)
VALUES('C','에어컨',DEFAULT,DEFAULT);

UPDATE PRODUCT SET P_VAL = 1000000
WHERE P_CODE = 'C';

UPDATE PRODUCT SET P_STOCK = 10
WHERE P_CODE = 'C';

SELECT * FROM PRODUCT;

DROP TABLE CART;
CREATE TABLE CART(
    CM_ID NVARCHAR2(10), --PK, FK
    CP_CODE NCHAR(1), --PK, FK
    C_QTY NUMBER(4) DEFAULT 1 NOT NULL,
    C_DAY DATE DEFAULT SYSDATE NOT NULL, --PK
    CONSTRAINT PK_CMID_CPCODE_CDAY_CART PRIMARY KEY(CM_ID,CP_CODE,C_DAY),
    CONSTRAINT FK_MID_CART FOREIGN KEY(CM_ID)
    REFERENCES MEMBER(M_ID),
    CONSTRAINT FK_PCODE_CART FOREIGN KEY(CP_CODE)
    REFERENCES PRODUCT(P_CODE)    
);

INSERT INTO CART(CM_ID,CP_CODE,C_QTY,C_DAY)
VALUES('AAA','A',DEFAULT,DEFAULT);
INSERT INTO CART(CM_ID,CP_CODE,C_QTY,C_DAY)
VALUES('AAA','B',2,DEFAULT);
INSERT INTO CART(CM_ID,CP_CODE,C_QTY,C_DAY)
VALUES('BBB','B',DEFAULT,DEFAULT);

SELECT * FROM MEMBER;
SELECT * FROM PRODUCT;
SELECT * FROM CART;

 

- 해당 테이블을 이용한 JOIN문을 만들어보자

-- Q. 주문한 사용자의 모든 ID, 이름, 전화와 상품코드를 검색하시오.
-- JOIN으로

SELECT M.M_ID 회원아이디,M.M_NAME 회원이름,M.M_TEL 회원전화번호,C.CP_CODE 상품코드
FROM MEMBER M INNER JOIN CART C
ON M.M_ID=C.CM_ID;

 

-- Q. 주문한 상품의 코드, 상품명, 가격, 회원아이디
-- JOIN으로

SELECT P.P_CODE 상품코드,P.P_NAME 상품명,P.P_VAL 상품가격,C.CM_ID 아이디
FROM PRODUCT P INNER JOIN CART C
ON P.P_CODE = C.CP_CODE;

 

-- Q. 주문한 사용자의 아이디, 이름, 상품명, 가격, 수량
-- 3개 JOIN

SELECT M_ID 사용자, M_NAME 이름, P_NAME 상품명, P_VAL 가격, C_QTY 수량
FROM CART C INNER JOIN MEMBER M
ON C.CM_ID = M.M_ID
INNER JOIN PRODUCT P
ON C.CP_CODE = P.P_CODE;

 

- 식별 / 비식별

부모의 PK를 자식에서 외래키로 쓰면서 PK로 동시에 쓰는 것을 식별이라고 한다.

https://velog.io/@jch9537/DATABASE-%EC%8B%9D%EB%B3%84%EA%B3%BC-%EB%B9%84%EC%8B%9D%EB%B3%84-%EA%B4%80%EA%B3%84

[DATABASE] 식별과 비식별 관계

ㄴ 읽어보기