-- 형식 1, WHERE로 JOIN했을 시
SELECT b.bCode, bName, bPrice, sDate, s.cNum, cName, qty, bPrice * qty amt
FROM BOOK b, dsale d, sale s, cus c
WHERE b.bCode = d.bCode AND d.sNum = s.sNum AND s.cNum = c.cNum AND cName = '서울서점'
ORDER BY cNum;
-- 형식 2
SELECT b.bCode, bName, bPrice, sDate,
s.cNum, cName, qty, bPrice * qty amt
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE cName = '서울서점'
ORDER BY cNum;
cNum을 보면 숫자가 다른데, 서울서점의 이름인 곳이 중복적으로 있었음을 확인할 수 있다.
SELECT b.bCode 책코드, bName 책이름, SUM(qty) "판매수량의 합", SUM (bPrice * qty) "판매금액 합"
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
ORDER BY b.bCode;
- 판매된 책코드(bCode), 책이름(bName), 판매수량의 합(qty) : 단 판매수량이 80개 이상인 것만 출력
SELECT * FROM (
SELECT b.bCode 책코드, bName 책이름, SUM(qty) "판매수량의 합"
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
ORDER BY b.bCode
) WHERE "판매수량의 합" >= 80;
SELECT b.bCode 책코드, bName 책이름, SUM(qty) "판매수량의 합"
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
HAVING SUM(qty) >= 80;
GROUP BY로 정렬한 것은 HAVING을 써서 해당하는 조건을 만족하는 것만 출력하게 할 수 있다.
- 판매된 책코드(bCode), 책이름(bName) 중복을 배제하고 출력하라.
-- 서브쿼리
SELECT bCode, bName
FROM BOOK
WHERE bCode IN (SELECT bCode FROM dsale);
-- 조인
SELECT b.bCode "판매된 책코드", bName "책이름"
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
HAVING SUM(qty) > 0;
-- DISTINCT
SELECT DISTINCT b.bCode "판매된 책코드", bName "책이름"
FROM book b
JOIN dsale d ON b.bCode = d.bCode;
- 판매된 책 중 판매권수의 합이 가장 큰 책코드, 책이름 출력
SELECT b.bCode 책코드, bName 책이름, SUM(qty) "판매수량의 합",
RANK() OVER(ORDER BY SUM(qty) DESC) 순위
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName;
-- 이 쿼리로 팔린 것들의 순위를 매길 수 있다.
-- 서브쿼리 방법-1
SELECT 책코드, 책이름 FROM (
SELECT b.bCode 책코드, bName 책이름,
RANK() OVER(ORDER BY SUM(qty) DESC) 순위
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
) WHERE 순위 = 1;
-- 방법-2
SELECT b.bCode 책코드, bName 책이름
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
HAVING SUM(qty) = (
SELECT MAX( SUM(qty))
FROM book b1
JOIN dsale d1 ON b1.bCode = d1.bCode
GROUP BY b1.bCode, bName
);
SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate,
s.cNum, cName, qty, bPrice * qty amt
FROM book b
JOIN pub p ON b.pNum = p.pNum
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(sDate, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY');
JOIN 이 끝나는 곳까지를 테이블이라고 생각하면 편하다. 이 후에 WHERE을 붙여 조건을 주면 그 조건에 해당하는 값들만 출력된다.
SELECT s.cNum, cName, TO_CHAR(sDate, 'YYYY') 년도, SUM(bPrice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
GROUP BY s.cNum, cName, TO_CHAR(sDate, 'YYYY')
ORDER BY cNum, 년도;
-- 고객의 작년과 올해 판매 현황
-- 고객의 이번달 판매금액(고객코드, 고객명, 금액) / 소비자는 고객(회원)일 수 있고 비회원일 수 있음 여기서는 고객만!
-- 고객의 전달 판매금액(고객코드, 고객명, YYYYMM, 금액)
SELECT s.cNum, cName, TO_CHAR(sDate, 'YYYY') 년도, SUM(bPrice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(sDate, 'YYYY') <= 1
GROUP BY s.cNum, cName, TO_CHAR(sDate, 'YYYY')
ORDER BY cNum, 년도;
-- 고객의 이번달 판매금액(고객코드, 고객명, 금액)
SELECT s.cNum, cName, TO_CHAR(sDate, 'YYYYMM'), SUM(bPrice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(sDate, 'YYYYMM') = TO_CHAR(SYSDATE, 'YYYYMM')
GROUP BY s.cNum, cName, TO_CHAR(sDate, 'YYYYMM')
ORDER BY cNum;
-- 고객의 전달 판매금액(고객코드, 고객명, YYYYMM, 금액)
SELECT s.cNum, cName, TO_CHAR(sDate, 'YYYYMM') 날짜, SUM(bPrice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') = TO_CHAR(sDate, 'YYYYMM')
GROUP BY s.cNum, cName, TO_CHAR(sDate, 'YYYYMM')
ORDER BY cNum;
SELECT s.cNum, cName, TO_CHAR(sDate, 'YYYYMM') 날짜, SUM(bPrice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(SYSDATE - INTERVAL '1' MONTH, 'YYYYMM') = TO_CHAR(sDate, 'YYYYMM')
GROUP BY s.cNum, cName, TO_CHAR(sDate, 'YYYYMM')
ORDER BY cNum;
ADD_MONTHS('날짜', 더하고 싶은 숫자) 를 하면 알아서 1달을 빼준다. 여기서 YYYYMM을 뽑아서 바로 빼지 않는 이유는 202101에서 1달을 빼면 202100이 되기 때문에 알아서 날짜연산을 해주는 ADD_MONTHS를 이용해야한다. (혹은 INTERVAL '숫자' 단위 를 이용한다)
-- 방법 1 : WHERE 로 JOIN한다.
SELECT bCode, bName, book.pNum, pName, bPrice
FROM book, pub
WHERE book.pNum = pub.pNum;
-- 방법 2 : JOIN 을 이용한다.
SELECT bCode, bName, b.pNum, pName, bPrice
FROM book b
JOIN pub p ON b.pNum = p.pNum;
테이블 book은 b로 별명을 주었기 때문에 book테이블의 pNum은 b.pNum 으로 표현가능하다. 두 테이블에 같은 값을 가지고 있는 pNum을 이용해 EQUI JOIN을 했기 때문에 중복되는 pNum을 SELECT에서 가져오려면 컬럼명 앞에 테이블명을 주어야한다. 안쓰면 에러(ORA-00918 : 컬럼의 정의가 애매하다. )가 발생한다. 주로 이런 상황에서는 먼저있는 테이블을 쓰기 때문에 b.pNum으로 주었다. p.pNum으로 줘도 상관은 없다.
WHERE절을 이용해서도 JOIN이 가능하지만 JOIN까지를 한 테이블로 생각하고 WHERE로 조건을 주는 것이 나는 더 편하기 때문에 방법 2를 주로 사용할 예정이다. (아니면 WHERE 절에 조인도 하고 조건도 줘야되서 조금 헷갈린다. 특히 LEFT OUTER JOIN 나 RIGHT OUTER JOIN사용 시 엄청 헷갈림)
-- 방법 1
SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate,
s.cNum, cName, qty, bPrice * qty amt
FROM BOOK b, pub p, dsale d, sale s, cus c
WHERE b.pNum = p.pNum AND b.bCode = d.bCode AND d.sNum = s.sNum AND s.cNum = c.cNum;
-- 방법 2
SELECT b.bCode, pName, bPrice, b.pNum, pName, sDate,
d.sNum, s.cNum, cName, qty, bPrice * qty amt
FROM book b
JOIN pub p ON b.pNum = p.pNum
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum;
-- 방법 3
SELECT bCode, pName, bPrice, pNum, pName, sDate,
cNum, cName, qty, bPrice * qty amt
FROM book b
JOIN pub USING(pNum)
JOIN dsale USING(bCode)
JOIN sale USING(sNum)
JOIN cus USING(cNum);
-- 컬럼명이 다를 수 있어서 방법2의 EQUI JOIN을 더 많이사용한다.
-- 판매된 책코드(bCode), 책이름(bName), 판매수량(qty) 를 출력하라.
SELECT b.bCode, bName, qty
FROM book b
JOIN dsale d ON b.bCode = d.bCode
ORDER BY b.bCode;
판매수량은 dsale 테이블이 가지고 있으므로 판매된 책코드와 책이름을 알아보기 위해서는 book과 dsale테이블을 JOIN해준다.
SELECT hak, ban,
DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분,
name, com, excel, word, com+excel+word tot,
ROUND((com+excel+word) / 3, 1) ave,
RANK() OVER(PARTITION BY hak, gubun, ban ORDER BY com+excel+word DESC) 학급석차,
RANK() OVER(PARTITION BY hak, gubun ORDER BY com+excel+word DESC) 학년석차
FROM score s
LEFT OUTER JOIN injeok i ON s.hakbeon = i.hakbeon
ORDER BY hak, gubun, ban;
-- LEFT OUTER JOIN을 활용하면, 시험을 치지 않은 학생들도 같이나옴.
-- 이 자료에서는 시험을 안친 학생이 없어서 상관없음.
-- FOREIGN KEY 의 제약 조건으로 인해 injeok이 없으면 score 자료도 없게 제약조건을 둔 것
-- 하지만 시험을 치지 않은 학생들을 배제하고 나오게 하려면 EQUI JOIN을 쓴다.
SELECT hak, ban,
DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분,
name, com, excel, word, com+excel+word tot,
ROUND((com+excel+word) / 3, 1) ave,
RANK() OVER(PARTITION BY hak, gubun, ban ORDER BY com+excel+word DESC) 학급석차,
RANK() OVER(PARTITION BY hak, gubun ORDER BY com+excel+word DESC) 학년석차
FROM score s
JOIN injeok i ON s.hakbeon = i.hakbeon
ORDER BY hak, gubun, ban;
-- 여기서 학번이 기본키이기 때문에 꼭 학번이 존재한다.
SELECT hak, ban,
DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분,
name, com, excel, word, com+excel+word tot,
ROUND((com+excel+word) / 3, 1) ave,
RANK() OVER(PARTITION BY hak, gubun, ban ORDER BY com+excel+word DESC) 학급석차,
RANK() OVER(PARTITION BY hak, gubun ORDER BY com+excel+word DESC) 학년석차
FROM score s
RIGHT OUTER JOIN injeok i ON s.hakbeon = i.hakbeon
ORDER BY hak, gubun, ban;
문제 2
score 테이블과 injeok 테이블을 조인하여 다음의 컬럼을 출력한다.
-- 출력할 컬럼
학년, 반, 구분, 이름, 총점, 평균, 판정
-- 처리조건
구분 : gubun 1-> 1학기중간, 2 -> 1학기기말, 3 -> 2학기중간, 4 -> 2학기기말 로 표시
판정
세 과목 모두 40점 이상이고, 평균 60점 이상 : 합격
세 과목 중 한과목이라도 40점 미만이고, 평균 60점 이상이면 : 과락
그렇지 않으면 불합격으로 처리한다.
SELECT hak 학년, ban 반, DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분,
name 이름, com+excel+word 총점,
ROUND((com+excel+word) / 3, 1) 평균,
(CASE
WHEN com >= 40 AND excel >= 40 AND word >= 40 AND (com+excel+word) / 3 >= 60 THEN '합격'
WHEN (com+excel+word)/3 >= 60 THEN '과락'
ELSE '불합격'
END) 판정
FROM score s
JOIN injeok i ON s.hakbeon = i.hakbeon;
-- 모든 데이터 사전 테이블 정보 확인
SELECT COUNT(*) FROM DICTIONARY;
SELECT * FROM DICTIONARY;
-- 현재 사용자의 모든 객체 정보
SELECT * FROM user_objects;
-- 테이블 정보 확인
SELECT * FROM user_tables;
SELECT * FROM tabs;
SELECT * FROM tab;
CREATE TABLE member1 (
id VARCHAR2(30) PRIMARY KEY,
pwd VARCHAR2(100) NOT NULL,
name VARCHAR2(30) NOT NULL
);
-- 관계 -> 1:1(식별관계, 기본키이면서 참조키)
CREATE TABLE member2 (
id VARCHAR2(30),
birth DATE,
tel VARCHAR2(30),
CONSTRAINT pk_member2_id PRIMARY KEY(id),
CONSTRAINT fk_member2_id FOREIGN KEY(id)
REFERENCES member1(id)
);
1:1의 식별관계일 때, member1 테이블에 id 데이터가 꼭 있어야member2테이블에서는 그 id를 참조키로 하여금 birth와 tel을 식별할 수 있다. 또 member2의 기본키도 id이다.
기본키이면서 참조키일 때 식별관계라고 한다.
비식별관계는 단순히 참조만 할 때 비식별관계라고한다.
예시
-- 관계 -> 1:다(비식별관계, 단순한 참조키)
CREATE TABLE guest (
num NUMBER PRIMARY KEY,
id VARCHAR2(30) NOT NULL,
content VARCHAR2(4000) NOT NULL,
reg_date DATE DEFAULT SYSDATE,
FOREIGN KEY(id)
REFERENCES member1(id)
);
guest 테이블에서는 member1의 id컬럼은 단순한 참조키이다.
1:다, 식별관계
-- 두개의 컬럼을 기본키로 설정. 참조키 2개 식별관계
-- 관계 member1 : guestLikst => 1:다, guest : guestLike => 1:다
CREATE TABLE guestLike ( -- LIKE 예약어이기 때문에 단독으로 이름으로 줄 수 없음.
num NUMBER,
id VARCHAR2(30),
PRIMARY KEY(num, id),
FOREIGN KEY(num)
REFERENCES guest(num),
FOREIGN KEY(id)
REFERENCES member1(id)
);
1:다, 비식별관계
-- 동일한 컬럼을 두 번 참조 : 예 -> 쪽지 테이블
-- 1:다, 비식별
CREATE TABLE note (
num NUMBER PRIMARY KEY,
sendId VARCHAR2(30) NOT NULL,
receiveID VARCHAR2(30) NOT NULL,
content VARCHAR2(4000) NOT NULL,
FOREIGN KEY(sendId)
REFERENCES member1(id),
FOREIGN KEY(receiveId)
REFERENCES member1(id)
);
SELECT fk.owner, fk.constraint_name,
pk.table_name parent_table, fk.table_name child_table
FROM all_constraints fk, all_constraints pk
WHERE fk.r_constraint_name = pk.constraint_name AND fk.constraint_type = 'R'
ORDER BY fk.table_name;
2) 『테이블명』을 참조하는 모든 테이블 목록 출력(자식 테이블 목록 출력)
SELECT fk.owner, fk.constraint_name , fk.table_name
FROM all_constraints fk, all_constraints pk
WHERE fk.r_constraint_name = pk.constraint_name
AND fk.constraint_type = 'R'
AND pk.table_name = UPPER('테이블명')
ORDER BY fk.table_name;
3) 『테이블명』이 참조하고 있는 모든 테이블 목록 출력(부모 테이블 목록 출력)
SELECT table_name FROM user_constraints
WHERE constraint_name IN (
SELECT r_constraint_name
FROM user_constraints
WHERE table_name = UPPER('테이블명') AND constraint_type = 'R'
);
4) 『테이블명』의 부모 테이블 목록 및 부모 컬럼 목록 출력
-- 부모 2개 이상으로 기본키를 만든 경우 여러번 출력 됨
SELECT fk.constraint_name, fk.table_name child_table, fc.column_name child_column,
pk.table_name parent_table, pc.column_name parent_column
FROM all_constraints fk, all_constraints pk, all_cons_columns fc, all_cons_columns pc
WHERE fk.r_constraint_name = pk.constraint_name
AND fk.constraint_name = fc.constraint_name
AND pk.constraint_name = pc.constraint_name
AND fk.constraint_type = 'R'
AND pk.constraint_type = 'P'
AND fk.table_name = UPPER('테이블명');
접은 글에 있는 쿼리를 통해 확인 할 수 있다.
테이블 삭제는 테이블 생성한 반대로 삭제하면 된다.
-- 삭제
DROP TABLE note PURGE;
DROP TABLE guestLike PURGE;
DROP TABLE guest PURGE;
DROP TABLE member2 PURGE;
DROP TABLE member1 PURGE;
참조키에 ON DELETE CASCADE 옵션을 넣었을 때
CREATE TABLE test1 (
code VARCHAR2(50) PRIMARY KEY,
subject VARCHAR2(100) NOT NULL
);
CREATE TABLE test2 (
num NUMBER PRIMARY KEY,
code VARCHAR2(50) NOT NULL,
qty NUMBER(10) NOT NULL,
FOREIGN KEY(code)
REFERENCES test1 (code) ON DELETE CASCADE
);
-- 데이터 추가
INSERT INTO test1 (code, subject) VALUES('a', 'a');
INSERT INTO test1 (code, subject) VALUES('b', 'b');
INSERT INTO test1 (code, subject) VALUES('c', 'c');
INSERT INTO test2 (num, code, qty) VALUES (1, 'a', 10);
INSERT INTO test2 (num, code, qty) VALUES (2, 'b', 10);
INSERT INTO test2 (num, code, qty) VALUES (3, 'a', 15);
INSERT INTO test2 (num, code, qty) VALUES (4, 'c', 10);
INSERT INTO test2 (num, code, qty) VALUES (5, 'a', 20);
SELECT * FROM test1;
SELECT * FROM test2;
DELETE FROM test1 WHERE code = 'a';
-- ON DELETE CASCADE 옵션으로 test1 테이블과 test2 테이블 모두 삭제됨
SELECT * FROM test1;
SELECT * FROM test2;
데이터 추가까지 완료한 모습.
ON DELETE CASCADE가 참조키에 있는 경우, 자식 테이블에서 참조하는 값을 부모테이블에서 지우게 되면 해당하는 값을 가진 모든 데이터는 부모, 자식 테이블에서 삭제된다.
이 ERD에 해당하는 테이블을 만들어보자.
CREATE TABLE dept_test (
dept_id VARCHAR2(30),
dept_name VARCHAR2(50) NOT NULL,
manager_id VARCHAR2(30) NOT NULL,
PRIMARY KEY (dept_id )
);
CREATE TABLE emp_test (
emp_id VARCHAR2(30),
name VARCHAR2(50) NOT NULL,
email VARCHAR2(50) NOT NULL,
dept_id VARCHAR2(30) NOT NULL,
PRIMARY KEY ( emp_id ),
CONSTRAINT fk_empTest_deptId
FOREIGN KEY (dept_id) REFERENCES dept_test ( dept_id)
);
ALTER TABLE dept_test ADD CONSTRAINT fk_deptTest_managerId FOREIGN KEY (manager_id)
REFERENCES emp_test( emp_id );
두 테이블이 서로의 컬럼을 참조키로 가지고 있어서 테이블 생성 후 ALTER TABLE로 FOREIGN KEY를 주었다.
이런 테이블을 삭제할 때는
DROP TABLE emp_test CASCADE CONSTRAINTS PURGE;
DROP TABLE dept_test CASCADE CONSTRAINTS PURGE;
이렇게 CASCADE CONSTRAINTS 를 넣고 삭제해야한다.
참조키를 비활성화/활성화 시키는 방법을 알아보자.
실습을 위한 예시 생성
CREATE TABLE dept_test (
dept_id VARCHAR2(30) PRIMARY KEY,
dept_name VARCHAR2(50) NOT NULL,
manager_id VARCHAR2(30) NOT NULL
);
CREATE TABLE emp_test (
emp_id VARCHAR2(30) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
email VARCHAR2(50) NOT NULL,
dept_id VARCHAR2(50) NOT NULL
);
-- 참조키 제약 조건
ALTER TABLE dept_test
ADD CONSTRAINT fk_deptTest_managerId FOREIGN KEY (manager_id) REFERENCES emp_test(emp_id);
ALTER TABLE emp_test
ADD CONSTRAINT fk_empTest_deptId FOREIGN KEY (dept_id) REFERENCES dept_test(dept_id);
SELECT * FROM tab;
-- 데이터 추가
INSERT INTO dept_test(dept_id, dept_name, manager_id) VALUES ('a1', '영업부', '1001');
-- 에러 ORA-02291 : 참조키가 존재하지 않음
INSERT INTO emp_test(emp_id, name, email, dept_id) VALUES ('1001','김영업', 'kim@a.com', 'a1');
-- 에러 ORA-02291 : 참조키가 존재하지 않음
서로의 컬럼을 참조키로 가지고 있기 때문에 이 경우에는 다른 테이블의 참조키를 비활성화 하고 데이터를 추가해야한다.
-- 참조키 비활성화 시키기 (삭제는 안하고 잠시 사용하지 못하게 막음)
ALTER TABLE dept_test DISABLE CONSTRAINT fk_deptTest_managerId CASCADE;
ALTER TABLE emp_test DISABLE CONSTRAINT fk_empTest_deptId CASCADE;
-- CASCADE : 부모 테이블과 자식 테이블의 설정이 되어 있을 때
-- 부모 테이블의 제약 조건을 비활성화하면 이를 참조하는 자식 테이블의 제약조건까지 비활성화
CASCADE ? 부모 테이블과 자식 테이블 설정이 되어있을 때, 부모 테이블의 제약 조건을 비활성화하면 이를 참조하는 자식 테이블의 제약조건까지 비활성화된다.
비활성화 확인해보자.
-- 비활성화 확인
SELECT * FROM USER_CONSTRAINTS WHERE table_name = UPPER('dept_test');
SELECT * FROM USER_CONSTRAINTS WHERE table_name = UPPER('emp_test');
-- status 컬럼
참조키를 비활성화 하면 참조키 제약 조건 위반 데이터를 추가할 수 있다.
INSERT INTO dept_test(dept_id, dept_name, manager_id) VALUES ('a1', '영업부', '1001');
INSERT INTO dept_test(dept_id, dept_name, manager_id) VALUES ('b1', '개발부', '2001');
INSERT INTO dept_test(dept_id, dept_name, manager_id) VALUES ('c1', '총무부', '3001');
SELECT * FROM dept_test;
INSERT INTO emp_test(emp_id, name, email, dept_id) VALUES ('1001','김영업', 'kim@a.com', 'a1');
INSERT INTO emp_test(emp_id, name, email, dept_id) VALUES ('2001','나개발', 'na@a.com', 'b1');
SELECT * FROM emp_test;
이 후 다시 참조키를 활성화 하면 에러가 발생하는데, 제약조건 위반 데이터가 존재하기 때문이다.
dept_test 테이블에는 manager_id로 3001이 있는데 이는 emp_test의 테이블의 emp_id를 참조하는 키 이기 때문이다. 따라서 emp_test 테이블에 emp_id가 3001인 자료가 있어야한다.
INSERT INTO emp_test(emp_id, name, email, dept_id) VALUES ('3001','홍총무', 'hong@a.com', 'c1');
ALTER TABLE dept_test ENABLE CONSTRAINT fk_deptTest_managerId ;
ALTER TABLE emp_test ENABLE CONSTRAINT fk_empTest_deptId ;
emp_id가 3001인 자료를 추가하고 나서 제약조건을 활성화 시키는 명령어를 입력하자,
한 테이블의 PRIMARY KEY나 UNIQUE 컬럼을 다른 테이블에 추가하여 두 테이블 간 연결을 설정한다.
부모 테이블이 먼저 생성된 후 자식 테이블(FOREIGN KEY를 포함하는 테이블)이 생성되어야 한다.
예제를 통해 알아보자.
-- std : 부모 테이블
CREATE TABLE std (
hak VARCHAR2(30) PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
email VARCHAR2(50) NOT NULL,
tel VARCHAR2(30) NOT NULL
);
-- std_score : 자식 테이블
-- 부모의 PRIMARY KEY 또는 UNIQUE 만 참조 가능
CREATE TABLE std_score(
num NUMBER PRIMARY KEY,
hak VARCHAR2(30) NOT NULL,
gubun NUMBER(1) NOT NULL,
java NUMBER(3) NOT NULL,
oracle NUMBER(3) NOT NULL,
CONSTRAINT fk_stdscore_hak FOREIGN KEY ( hak )
REFERENCES std(hak)
);
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'STD';
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'STD_SCORE';
-- P : 기본키, R : 참조키
SELECT * FROM USER_CONS_COLUMNS WHERE table_name = 'STD';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name = 'STD_SCORE';
CREATE TABLE 문에서 FOREIGN KEY를 추가할 때는 FOREIGN KEY (컬럼) REFERENCES 참조하는테이블(참조하는컬럼) 을 통해 추가한다.
기본키는 CONSTRAINT TYPE 이 P로 되어있고 참조키는 R로 되어있다.
데이터를 추가해보자.
INSERT INTO std_score(num, hak, gubun, java, oracle) VALUES (1, '11', 1, 100, 90);
-- 에러 ORA-02291 : 부모 테이블에 존재하지 않는 hak은 추가 불가
INSERT INTO std(hak, name, email, tel) VALUES ('11', 'aa', 'aa', '11');
INSERT INTO std(hak, name, email, tel) VALUES ('22', 'bb', 'bb', '22');
INSERT INTO std(hak, name, email, tel) VALUES ('33', 'cc', 'cc', '33');
INSERT INTO std_score(num, hak, gubun, java, oracle) VALUES (1, '11', 1, 100, 90);
INSERT INTO std_score(num, hak, gubun, java, oracle) VALUES (2, '11', 2, 90, 90);
INSERT INTO std_score(num, hak, gubun, java, oracle) VALUES (3, '33', 1, 90, 90);
COMMIT;
SELECT * FROM std;
SELECT * FROM std_score;
부모테이블에 아직 아무데이터도 있지 않은 상태이다. 이 상태에서 자식테이블에 값을 추가하면 에러(ORA-02291)가 발생한다.
참조하는 키가 먼저 부모테이블에 존재해야 자식테이블에 값도 추가할 수 있다.
먼저 부모 테이블에 데이터를 입력하고 자식테이블에도 입력을 하면 잘 들어가는 것을 확인할 수 있다.
입력된 데이터의 값을 수정하거나 삭제해보자.
DELETE FROM std WHERE hak = '11';
-- 에러 ORA-02292 : 참조하는 레코드가 있음.
UPDATE std SET hak = '13' WHERE hak = '11';
-- 에러 ORA-02292 : 참조하는 레코드가 있음
UPDATE std SET hak = '23' WHERE hak = '22';
-- 가능 : 참조하는 레코드가 없음
SELECT * FROM std;
DELETE FROM std WHERE hak = '23';
-- 가능
부모테이블에 있는 PRIMARY KEY인 HAK을 바꿔보려 했으나 이 값을 참조키로 자식테이블에서 데이터를 추가했기 때문에 오류가 발생한다.(ORA-02292) 하지만 HAK 22의 경우 이 값으로 하여금 자식 테이블이 가지고 있는 값이 아무 것도 없기 때문에 23으로 변경할 수 있었다. 또 삭제도 가능했다.
자식이 부모테이블에있는 컬럼을 참조키로 가지고 있는 경우, 부모 테이블은 여태까지 했던 방식으로는 불가하다.
DROP TABLE std PURGE;
-- 에러 ORA-02449 : 참조하는 테이블이 있으면 삭제 불가
-- 모든 자식 테이블을 먼저 삭제하고 삭제해야 함
에러 (ORA - 02449)가 발생한다.
강제로 삭제하는 방법이 존재한다. 참조키를 삭제해 부와 자의 관계를 없앤다.
-- 강제로 삭제. 참조키가 삭제 됨(부와 자의 관계가 없어짐)
DROP TABLE std CASCADE CONSTRAINTS PURGE;
SELECT * FROM tab;
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'STD_SCORE';
-- R(참조키)이 제거됨
DROP TABLE std_score PURGE;
명령어
DROPTABLE std CASCADE CONSTRAINTS PURGE;
통해서 부모테이블을 강제로 삭제했다. 그 후 테이블 제약조건을 확인해보니 원래 HAK은 CONSTRAINT_TYPE 이 FOREIGN KEY 였기 때문에 F 였으나 이제는 NOT NULL의 CHECK조건을 가지고 있으므로 C로 바뀌어있는 것을 확인할 수 있다. 참조키를 가지고 있지 않기 때문에 평소 했던 방법으로 테이블을 삭제할 수 있다.
DROPTABLE std_score PURGE;
참조하는 컬럼과 참조당하는 컬럼은 타입과 크기가 같아야한다. 하지만 컬럼명은 다를 수 있다.
예시를 통해 알아보자.
-- std : 부모 테이블
CREATE TABLE std (
hak VARCHAR2(30) PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
email VARCHAR2(50) NOT NULL,
tel VARCHAR2(30) NOT NULL
);
-- std_score : 자식 테이블
CREATE TABLE std_score(
num NUMBER PRIMARY KEY,
shak VARCHAR2(30),
gubun NUMBER(1) NOT NULL,
java NUMBER(3) NOT NULL,
oracle NUMBER(3) NOT NULL,
FOREIGN KEY ( shak )
REFERENCES std(hak)
);
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'STD';
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'STD_SCORE';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name = 'STD';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name = 'STD_SCORE';
테이블에 데이터를 넣어보자.
INSERT INTO std_score(num, shak, gubun, java, oracle) VALUES (1, NULL , 1, 100, 90);
-- 가능. 부모에 존재하지 않아도 참조키가 NULL이 가능하므로 NULL은 추가 가능
INSERT INTO std_score(num, shak, gubun, java, oracle) VALUES (2, '11', 1, 80, 90);
-- 에러 ORA-02291 : 부모의 hak에 11이 없음.
자식테이블에 외래키에 NOT NULL 조건을 붙이지 않았으므로 NULL이 가능하다.
하지만 자식테이블의 참조키에 NULL 이외의 값으로는 데이터를 추가할 수 없다. 부모테이블에 값이 있어야 추가가 가능하다.
CREATE TABLE test1(
num NUMBER PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
subject VARCHAR2(500) NOT NULL,
content VARCHAR2(4000) NOT NULL,
reg_date DATE DEFAULT SYSDATE,
hitCount NUMBER DEFAULT 0 NOT NULL
);
reg_date와 hitCount는 값을 입력하지 않으면 각각 SYSDATE 와 0을 입력한다.
데이터를 입력해서 확인해보자.
INSERT INTO test1(num, name, subject, content) VALUES(1, 'a', 'a', 'a');
INSERT INTO test1(num, name, subject, content) VALUES(2, 'b', 'b', 'b');
SELECT * FROM test1;
INSERT INTO test1(num, name, subject, content, reg_date)
VALUES(3, 'c', 'c', 'c', TO_DATE('2000-10-10', 'YYYY-MM-DD'));
SELECT * FROM test1;
INSERT INTO test1(num, name, subject, content, reg_date, hitCount)
VALUES(4, 'd', 'd', 'd', SYSDATE, 10);
SELECT * FROM test1;
결과를 보면 입력시 reg_date 와 hitCount를 넣지 않았더니DEFALUT로 설정한 SYSDATE 와 0이 들어간 것을 확인 할 수 있다.
reg_date와 hitCount를 직접 입력했을 때는 입력한 값이 들어감을 확인 할 수 있다.
※ DEFALUT가 있어도 행을 추가할 때 값이 들어가지 않을 수도 있기 때문에 자바로 코딩할 때 DEFALUT가 없다고 생각하고 값을 넣어주자!
DEFALUT의 값을 확인 방법
명령어를 이용해서 확인할 수 있다.
SELECT * FROM cols WHERE table_name = 'TEST1';
SELECT * FROM user_tab_columns WHERE table_name = 'TEST1';
SELECT * FROM cols WHERE table_name = 'TEST1'; -- DEFAULT 확인 가능
SELECT * FROM user_tab_columns WHERE table_name = 'TEST1'; -- DEFAULT 확인 가능
DATA_DEFAULT 컬럼에 보면 SYSDATE 와 0이 들어가있는 것을 확인 할 수 있다.
이미 존재하는 테이블 칼럼에 DEFAULT를 추가하려면 ALTER TABEL 명령을 이용한다.
ALTER TABLE test1 MODIFY 컬럼명 DEFAULT 값;
DEFAULT 제거하는 방법
ALTER TABLE 테이블명 MODIFY 컬럼 DEFAULT NULL;
ALTER TABLE 테이블명 MODIFY 컬럼 DEFAULT NULL; 을 통해서 DEFAULT를 제거한다. DELETE를 사용하지 않는다.
CHECK 제약조건에 대해 알아보자.
테이블 생성을 통한 예시로 알아보자.
CREATE TABLE test2(
num NUMBER PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
com NUMBER(3) NOT NULL,
excel NUMBER(3) CHECK( excel BETWEEN 0 AND 100),
CONSTRAINT ch_test2_com CHECK ( COM BETWEEN 0 AND 100)
);
SELECT * FROM cols WHERE table_name = 'TEST2';
SELECT * FROM user_tab_columns WHERE table_name = 'TEST2';
excel을 입력할 때 0~100사이여야 입력이 가능하다. com의 경우 테이블레벨에서 CHECK제약을 주었다. 이렇게 CHECK 제약조건을 줄 수 있다. 하지만 이렇게 CHECK 제약을 주는 것보다 후에는 자바에서 제약을 주는 것이 속도면에서 효율적이다.
테이블에 값을 입력해보자.
INSERT INTO test2(num, name, com, excel) VALUES (1, 'a', 100, 100);
INSERT INTO test2(num, name, com, excel) VALUES (2, 'b', 120, 100);
-- 에러 ORA-02290 : CHECK 제약 위반
com과 excel 모두 0~100까지의 값만을 넣을 수 있기 때문에 ORA-02290 : CHECK제약조건위반으로 값이 입력되지 않는다.
테이블명 : test3num NUMBER PRIMARY KEYname VARCHAR2(50) NOT NULLgender VARCHAR2(10) NOT NULLsdate DATE NOT NULLedate DATE NOT NULL
-- gender 컬럼은 '남', '여'만 등록될 수 있도록 설정한다.-- 다음의 CHECK 제약을 추가한다 : sdate<= edate
CREATE TABLE test3 (
num NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
gender VARCHAR2(10) NOT NULL CHECK(gender IN('남', '여')),
-- '남', '여'만 등록될 수 있도록 설정
-- gender VARCHAR2(10) CHECK(gender IN('남', '여')) DEFAULT '여' 이거는 안된다.
-- DEFAULT와 CHECK를 동시에 설정할 경우는 DEFAULT를 먼저
-- gender VARCHAR2(10) DEFAULT '여' CHECK(gender IN('남', '여')) 로 해야함.
sdate DATE NOT NULL,
edate DATE NOT NULL
);
ALTER TABLE test3 ADD CHECK ( sdate<= edate );
-- 다른 컬럼을 이용하여 CHECK 제약을 만드는 경우에는 테이블 레벨 또는
-- ALTER TABLE로 만든다.
gender VARCHAR2(10) NOT NULL CHECK(gender = '남' OR gender = '여') 를 gender IN('남', '여') 로 줄 수 있다.
다른 컬럼을 이용하여 CHECK 제약을 만드는 경우에는 테이블 레벨 또는 ALTER TABLE을 통해 제약조건을 만든다.
