[SQL 기초 - 13장] 오라클 SQL 제약조건, ALTER

 

 

안녕하세요!

​

오늘은 저번에 배웠던 제약 조건에 대해서 좀 더 알아보려고 합니다!

​

제약조건 생성방법 기억 하시나요?

오라클 제약조건이란?

기억이 안나신다면 ↑↑↑↑↑↑↑ 가서 글 마지막부분 보고오세요 ㅎㅎ

제약조건

오늘은 그 두번째인

테이블 생성시 모든 컬럼을 기술하고 나서 제약조건만 별도로 기술

하는 방법부터 시작하겠습니다.​​

위를 보시면 dept_test 라는 테이블을 만들어 주면서

deptno 컬럼을 대상으로 PRIMARY KEY​ 제약 조건을 생성해줬습니다.

pk_dept_test 는 별칭이므로 여러분이 짓고 싶은 이름으로 지어주시면 됩니다.

​

​

위의 쿼리를 실행하고 왼쪽에서 테이블 목록을 보시면 저렇게

dept_test 라는 테이블이 생성되어 있습니다.

(없으신 분들은 새로고침 모양의 아이콘 혹은 F5 를 한 번 눌러주세요.)

그리고 그 테이블을 눌러보면 여러가지 메뉴중에

제약조건이라는 탭이 있습니다.

거기에서 저렇게 PRIMARY KEY 가

내가 정한 별칭으로 생성 되었다는 것을 눈으로 확인 할 수 있습니다.

 

 

그렇다면 눈으로 확인했으니,

이제 생성된 제약조건이 정상적으로 기능을 하는지 알아봐야 겠죠?

deptno 컬럼을 대상으로 제약조건을 걸었기 때문에

deptno 컬럼에는 동일한 데이터값이 들어갈 수 없습니다.

그러면 여기에서 deptno 데이터는 동일하고 다른 컬럼의 데이터만 바꿔서 입력해보겠습니다.

입력은 INSERT 구문을 사용하면 되겠죠?

기억이 안나신다면 ↓↓↓↓↓↓↓↓↓↓↓↓ ^^7

오라클 INSERT 구문 확인하기

​

PK

저는 위와 같이 부서번호에 99, 부서이름에 용선생, 부서지역에 대전을 입력했습니다.

행이 잘 삽입됐네요.

​​

이번에는 부서번호는 동일하고 다른값만 조금씩 바꿔서 넣어보면

위와 같은 오류가 발생하네요.

'무결성 제약 조건에 위배됩니다.'

이를 보고 우리는 PRIMARY KEY 제약조건이 잘 적용이 되었다고 생각할 수 있겠습니다.

NOT NULL

이번에는 NOT NULL 제약조건을 보겠습니다.​

아까 만들었던 테이블에 위의 쿼리로 dname 에 null 값을 넣어봤습니다.

잘 들어가죠?

​

그럼 이 테이블을 지웠다가 다시 생성하면서

dname에 NOT NULL 제약조건을 걸어주고 다시 한 번 저 데이터를 입력 해보겠습니다.

DROP 먼저 하고,

NOT NULL 제약조건을 저렇게 만들어 줍니다.

그리고 마지막으로 아까 넣었던 데이터를 다시 넣어보면 ​

또 오류가 나오네요.

역시 무결성 제약 조건이 위배 된다고 하네요.

NOT NULL 조건도 잘 들어갔네요 ㅎㅎ​

UNIQUE

다음은 UNIQUE 제약조건 입니다.

해당 컬럼의 값이 다른 행에 나오지 않도록(중복되지 않도록)

데이터 무결성을 지켜주는 조건입니다.

​

방법은 동일합니다.

위에서 처럼 DROP TABLE 먼저 해주시고,

새로운 테이블 작성하는 쿼리에서

제약조건 부분만 생성하고 싶은 제약조건을 넣어주면 됩니다.

위처럼 작성하고 실행하면 dname과 loc에

UNIQUE 제약조건이 생성됩니다.

​

그럼 또 확인을 해봐야겠죠?

 

두 INSERT 구문을 작성해서 실행시키면 어떻게 될까요?

입력이 됩니다.

왜냐하면 우리는 dname과 loc 컬럼 조합으로 제약조건을 생성했기 때문입니다.

반드시 dname과 loc 컬럼의 데이터가 모두 동일해야만

데이터가 입력되지 않습니다.

FOREIGN KEY

다음은 FOREIGN KEY 제약조건 입니다.

이 부분은 중요하니 잘 봐두세요!

​

FOREIGN KEY 제약조건은

한 테이블의 컬럼이 값을 참조하는 테이블의 컬럼 값중에

존재하는 값만 입력되도록 제어하는 제약조건입니다.

즉, 이 경우 두개의 테이블간 제약조건 입니다.

​

그럼 제약조건을 생성하기 전에 또 앞에서 했던것처럼

DROP TABLE 을 해주세요.

 

 

그리고 위 처럼 deptno 에 PRIMARY KEY 제약조건을 건

테스트 테이블을 생성하고 데이터를 하나 넣어주세요.

​

그리고 나서 REFERENCES 를 써서

dept_test 테이블의 deptno 컬럼을 참조하는

emp_test 테이블을 생성 해줍니다.

​

현재 상황을 정리해보면

1. dept_test 테이블에는 부서번호가 1번인 부서가 존재

2. emp_test 테이블의 deptno 컬럼으로 dept_test.deptno 컬럼을 참조

==> emp_test 테이블의 deptno 컬럼에는 dept_test.deptno 컬럼에

존재하는 값만 입력하는 것이 가능​

 

 

그럼 dept_test 테이블에 존재하지 않는 부서번호로

emp_test 테이블에 입력하려고 하면

이와 같이 오류가 발생하게 됩니다.

​

그리고 위 처럼 작성하지 않고

위와 같은 방법으로 작성할 수 있습니다.

위의 방법대로 작성해보면

이렇게 되겠네요 ㅎㅎ

​

​이런 FK 조건을 생성할 때는 옵션이 있습니다.

FK 조건 생성 옵션

0. DEFAULT - 무결성이 위배되는 경우 에러

1. ON DELETE CASCADE - 부모 데이터를 삭제할 경우 참조하는 자식 데이터를 같이 삭제

2. ON DELETE SET NULL - 부모 데이터를 삭제할 경우 참조하는 자식 데이터의 컬럼을 NULL 로 수정

​

0번 옵션은 말 그대로 무결성이 위배되는 경우 에러가 나는 것이고,

​

1번 옵션은 dept_test 테이블의 1번 부서를 삭제하면

dept_test 테이블의 deptno 를 참조하는 emp_test 테이블의

1번 부서에 소속된 데이터(현재는 'brown')가 삭제됩니다.

​

그리고 2번 옵션은 말 그대로 위의 상황과 같이 1번 부서를 삭제한다고 하면

emp_test 테이블에서 1번 부서에 소속된 데이터가 NULL 로 바뀌는 것입니다.

​

이 옵션들도 참고 해주세요!​

마지막 CHECK 제약조건은

컬럼에 입력되는 값을 검증하는 제약조건 입니다.

​

마찬가지로 DROP TABLE 해주시고

위와 같이 만들어 보겠습니다.

​

그리고 나서 위와 같은 값을 입력해 주려고 하면

sal 값이 0보다 커야하는 CHECK 제약 조건때문에

데이터가 입력되지 않는 것을 볼 수 있으실 겁니다.

그리고 CTAS 문법을 소개해 드릴게요.

테이블 생성과 [제약조건 포함] 을 동시에 하는 구문 입니다.

위와 같이 작성할 수 있으며

테이블의 조회 결과를 토대로 새로운 테이블을 생성할 때 사용할 수 있습니다.​

DDL - ALTER

다음으로 배울 내용은 DDL 중 ALTER 입니다.

ALTER 는 생성된 테이블의 데이터나 제약조건을 변경하는 DDL 입니다.

​

존재하지 않았던 새로운 컬럼을 추가하거나,

존재하는 컬럼 삭제,

존재하는 컬럼 변경

등의 기능을 할 수 있습니다.​

​컬럼 추가

신규 컬럼을 추가해보겠습니다.

문법은 ALTER TABLE 테이블명 ADD (추가하려는컬럼명 컬럼타입); 입니다.

저 쿼리를 실행하면

emp_test 테이블에는 hp 라는 컬럼이 추가 되겠죠?

하지만 컬럼의 순서는 바꿀 수 없으니 생성 전에 고려하고 생성해 주셔야 합니다.

컬럼 수정

그 다음은 컬럼을 수정해보겠습니다.

아까 VARCHAR2 로 설정한 hp 컬럼의 타입을

NUMBER 타입으로 바꿔주었습니다.

다음으로 컬럼의 기본값을 설정해보겠습니다.

 

hp 의 컬럼에 아무것도 입력되지 않는다면

기본값으로 123을 넣는다고 수정을 한 쿼리입니다.

 

다음은 컬럼 명칭 변경입니다.

RENAME COLUMN ~ TO ~ 를 사용하여 컬럼의 명칭을 바꿀 수 있습니다.

컬럼 삭제

마지막으로 컬럼 삭제인데요

위와 같이 두 가지 방법으로 작성할 수 있습니다.

​​

오늘은 뭔가 내용이 좀 길었던것 같네요..

많이 쉰 만큼 많이 업로드 하도록 하겠습니다!

오늘도 끝까지 봐주셔서 감사합니다.

다음에 또 좋은 내용 가져오겠습니다!

 

 

#sql#sqldeveloper#select#from#where#orderby#rownum#groupby#join#outerjoin#제약조건#sql제약조건#primarykey#foreignkey#기본키#외래키#alter#check#unique#CTAS