CREATE with 제약조건

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;