CREATE with 제약조건

자격증/SQLD - 취득완료

CREATE with 제약조건

Ch's Lee 2025. 6. 20. 12:15

CREATE시 스키마를 작성해주지 않으면, 로그인한 유저가 주인으로 만들어지죠?

설명:

Oracle, MariaDB, MS SQL Server 등 대부분의 DBMS에서는 CREATE TABLE 등 객체를 생성할 때 **스키마(schema)**를 명시하지 않으면,
현재 로그인한 유저(계정)의 기본 스키마(=계정명) 아래에 객체(테이블, 뷰 등)가 생성됩니다.

제약조건(CONSTRAINT)

PRIMARY KEY : 일반키 선언

UNIQUE : 중복안됨

NOT NULL : NULL 비허용

FOREIGN KEY : 외래키 선언

CHECK : 범위(성별 이면, 남/녀 만 입력해야하는것)

DEFAULT : 기본 값, NULL대신 다른 기본값으로 설정시

차이점 - CREATE

[ORACLE]

[MSSQL]

* -- 테이블 생성은 부모테이블부터 하고, 테이블 삭제는 자식테이블부터 한다.

* -- ON DELETE CASCADE : 자식테이블에 설정하면, 부모테이블 데이터 삭제시, 자식테이블 데이터도 함께 삭제

-> 부모부터 삭제하면, 참조되어있다고 에러 뜹니다. ON DELETE CASCADE 하지만, 이 아이로 제약조건을 뜨면, 강제적으로 부모 테이블 삭제시 자식 테이블도 삭제가 된다.

DROP TABLE DEPT_TBL;
CREATE TABLE DEPT_TBL (
-- deptno number(3) PRIMARY KEY, -- 컬럼레벨
deptno number(3),
deptname varchar2(50) NOT NULL,
loc varchar2(50),
CONSTRAINT dept_tbl_deptno_pk PRIMARY KEY(deptno) -- 테이블레벨
);

-- 실수한 부분 deptno number(3) PRIMARY KEY이렇게 주어도 자동으로 UNIQUE랑 NOT NULL이 적용됨

DROP TABLE EMP_TBL;

CREATE TABLE EMP_TBL (
empno number(3),
ename varchar2(50) NOT NULL,
hire_date DATE DEFAULT sysdate,
salary number(9) CONSTRAINT emp_tbl_salary_min CHECK(salary > 0), -- 컬럼레벨
deptno number(3),
--deptno number(3) CONSTRAINT emp_tbl_deptno_fk REFERENCES dept_tbl(deptno) ON DELETE CASCADE,
email varchar2(100),
CONSTRAINT emp_tbl_empno_pk PRIMARY KEY(empno),
CONSTRAINT emp_tbl_deptno_fk FOREIGN KEY(deptno) REFERENCES dept_tbl(deptno),
CONSTRAINT emp_tbl_email_uk UNIQUE(email)
);

실행 문장

DELETE FROM DEPT_TBL WHERE DEPTNO = 10;

근데,

위 부모테이블 삭제사려니

ON DELETE CASCADE, 를 주면, 부모 삭제시 자식도 같이 삭제된다.

-- 부모 테이블
DROP TABLE DEPT_TBL;
CREATE TABLE DEPT_TBL (
-- deptno number(3) PRIMARY KEY, -- 컬럼레벨
deptno number(3),
deptname varchar2(50) NOT NULL,
loc varchar2(50),
CONSTRAINT dept_tbl_deptno_pk PRIMARY KEY(deptno) -- 테이블레벨
);

-- 자식 테이블
DROP TABLE EMP_TBL;
CREATE TABLE EMP_TBL (
empno number(3),
ename varchar2(50) NOT NULL,
hire_date DATE DEFAULT sysdate,
salary number(9) CONSTRAINT emp_tbl_salary_min CHECK(salary > 0), -- 컬럼레벨
deptno number(3),
--deptno number(3) CONSTRAINT emp_tbl_deptno_fk REFERENCES dept_tbl(deptno) ON DELETE CASCADE,
email varchar2(100),
CONSTRAINT emp_tbl_empno_pk PRIMARY KEY(empno),
CONSTRAINT emp_tbl_deptno_fk FOREIGN KEY(deptno) REFERENCES dept_tbl(deptno)
ON DELETE CASCADE,
CONSTRAINT emp_tbl_email_uk UNIQUE(email)
);

INSERT INTO DEPT_TBL (deptno, deptname, loc) VALUES (10, '총무부', '서울');
INSERT INTO EMP_TBL (empno, ename, hire_date, salary, deptno, email) VALUES (100, '이충현', TO_DATE('2024-06-20', 'YYYY-MM-DD'), 3000, 10, 'lch3067@naver.com');

DELETE FROM DEPT_TBL WHERE DEPTNO = 10;

COMMIT;

다른 케이스(다른 방법으로 시도해보기)

--3-50 모든 고객을 삭제하시오.
--> 1. ALTER로 CUSTOMER 테이블에 ON DELETE CASCADE를 주면된다.

-- 1번 방법으로 삭제해보기
-- 1번 해당 테이블의 제약조건 조회
-- R : 참조 | P : 일반키 | F : 외래키
SELECT *
FROM user_constraints
WHERE table_name = 'ORDERS' AND constraint_type = 'R';

-- 해당 테이블에 제약조건명 알아두기
-- SYS_C007114

-- 기존 제약조건 삭제 하기
ALTER TABLE ORDERS DROP CONSTRAINT SYS_C007114;

-- 다시 제약 조건 추가(연쇄삭제 가능하도록) - ON DELETE CASCADE
ALTER TABLE ORDERS
ADD CONSTRAINT SYS_C007114
FOREIGN KEY (custid)
REFERENCES CUSTOMER(custid)
ON DELETE CASCADE;