728x90
1. 제약조건
- 테이블의 해당 컬럼에 사용자가 원치 않은 데이터가 입력, 수정, 삭제되는 것을 방지하기 위한 조건
<java />
// 조회방법
select * from user_constraints;
| 제약조건 | 설명 | 특징 | |
| unique key | 유일한 값을 가진 열, 열의 조합 지정 중복행 데이터가 들어가는 것을 방지 |
중복X, null값 O ex) 주민번호 |
|
| primary key | 유일하게 테이블의 각 행을 식별 | 중복X, null값 X | not null + unique key |
| not null | null 값을 포함 X | ||
| foreign key | 열과 참조된 테이블의 열 사이의 외래키 관계 적용하고 설정 주테이블의 pk를 보조테이블의 column으로 지정한 키 참조 테이블의 pk에 없다면 등록 못함 |
중복O, null값 O | primary key와 연결 |
| check | 참이어야 하는 조건 지정 혹은 2개를 제외한 다른 값은 못 들어가게 지정 = 정의된 형식만 저장되도록 허용 |
'M' 'F' - 남자, 여자 | |
2022.11.08 - [프로그래밍 언어/SQL] - [SQL] 데이터베이스
1.0.1. 테이블 레벨 제약 조건
- 열 이름 명시 필수
- not null 제약조건 정의 불가
1. 테이블 생성 당시에 제약조건을 거는 경우 - 컬럼 옆에 바로 붙이기
<sql />
create table dept2 (
dept_no number(2) constraint dept2_no_pk primary key,
dept_name varchar2(15) constraint dept2_name_nn not null,
loca number(4) constraint dept2_loca_locaid_fk references locations (location_id), -- loctions와 연결할 때 = fk만들 때
dept_date date default sysdate,
dept_bonus number(10) default 0,
dept_phone varchar2(20) constraint dept2_phone_uk unique,
dept_gender char(1) constraint dept2_gender_ck check( dept_gender in ('M', 'F') )
);
2. 모든 열들의 이름과 타입 명시 후, 맨 아래에 제약조건 설정
<sql />
-- 테이블 레벨 (not null만 열레벨 사용)
-- 아래쪽에 constraint 필수로 넣어야 함
create table dept2 (
dept_no number(2) ,
dept_name varchar2(15) not null,
loca number(4) , -- loctions와 연결할 때 = fk만들 때
dept_date date default sysdate, -- default는 제약조건 x
dept_bonus number(10) default 0,
dept_phone varchar2(20) ,
dept_gender char(1) ,
constraint dept2_no_pk primary key (dept_no /*, dept_name */ ), -- 슈퍼키(두개의 컬럼이 하나의 pk로 묶이는 것)(열레벨에서는 안되고 테이블 레벨에서만 가능)
constraint dept2_loca_locaid_fk foreign key (loca) references locations (location_id),
constraint dept2_phone_uk unique (dept_bonus),
constraint dept2_gender_ck check( dept_gender in ('M', 'F') )
);
1.0.2. 열 레벨 제약 조건
- constraint 생략 가능
- 열 별로 제약조건 걸기
- 무결성 제약조건의 어떤 유형도 정의 가능
1.1. Unique
- 데이터 값이 유일, 열에서 두 개 이상 행이 같은 값 X
- null값 가능
1.2. Primary key
- 데이터 값들이 유일, 중복 X
- 2개 일수는 없으나, 컬럼이 하나로 합쳐져서 한번에 pk값으로 지정되는 건 가능
- null값 불가
1.2.1. super key - primary key
- 프라이머키 관련! - suepr key
<sql />
-- 테이블 레벨 (not null만 열레벨 사용)
create table dept2 (
dept_no number(2) ,
dept_name varchar2(15) not null,
loca number(4) , -- loctions와 연결할 때 = fk만들 때
dept_date date default sysdate, -- default는 제약조건 x
dept_bonus number(10) default 0,
dept_phone varchar2(20) ,
dept_gender char(1) ,
constraint dept2_no_pk primary key (dept_no /*, dept_name */ ), -- 슈퍼키(열레벨에서는 안되고 테이블 레벨에서만 가능)
constraint dept2_loca_locaid_fk foreign key (loca) references locations (location_id),
constraint dept2_phone_uk unique (dept_bonus),
constraint dept2_gender_ck check( dept_gender in ('M', 'F') )
);
1.3. Not null
- null 값 불가
- primary key는 기본적으로 not null;
1.4. Foreign key
- 테이블간의 관계 설정
- null값 가능
- 단, PK에 없는 값은 들어가지 X (참조 무결성)
1.5. Check
- 각 행을 만족해야하는 조건 정의
- where 조건에 들어가는 형식
실습
<sql />
-- 제약조건 추가, 삭제 (수정 불가)
drop table dept2;
create table dept2 (
dept_no number(2) ,
dept_name varchar2(15) ,
loca number(4) ,
dept_date date default sysdate,
dept_bonus number(10) default 0,
dept_phone varchar2(20) ,
dept_gender char(1)
);
-- pk 추가
alter table dept2 add constraints dept_no_pk primary key (dept_no);
-- fk 추가
alter table dept2 add constraints dept_loca_fk foreign key (loca) references locations(location_id);
-- unique 추가
alter table dept2 add constraints dept_phone_uk unique (dept_phone); -- unique는 key 안 붙임
-- check 추가
alter table dept2 add constraints dept_gender_check check (dept_gender in ('M', 'F')); -- 뒤에 조건 붙이기
-- not null - 컬럼 변경문으로
alter table dept2 modify dept_name varchar2(15) not null;
-- 제약조건 삭제 (제약조건명)
alter table dept2 drop constraint dept_loca_fk;
2. 제약조건 관리
- 제약조건의 추가, 삭제 가능 but, 수정 불가 (삭제 후 다시 만들어야 함)
- not null 조건 추가
- not null 조건 추가 - modify절 사용
마우스로 하는 법
2.1. 제약조건 위배
2.1.1. 개체 무결성 위배
- null, 중복이 pk에 들어가지 못 함
2.1.2. 참조 무결성 위배
- 참조하는 테이블의 pk로 존재해야 fk에 들어갈 수 있음
2.1.3. 도메인 무결성 위배
- 컬럼에 정의된 값만 들어갈 수 있음
<sql />
-- 개체 무결성 위배 (null, 중복 pk에 들어가지 못함) / pk
insert into employees (employee_id, last_name, email, hire_date, job_id)
values (100, 'test', 'test', sysdate, 'test'); -- employee_id(pk) 100이 이미 있기 때문에
-- 참조 무결성 위배 (참조하는 테이블의 pk로 존재해야 fk에 들어갈 수 있음) / fk
insert into employees (employee_id, last_name, email, hire_date, job_id, department_id )
values (501, 'test', 'test', sysdate, 'test', 5); -- department_id(fk)는 departmanets테이블의 department_id(pk)에 5값이 없기 때문에
-- 도메인 무결성 위배 (컬럼에 정의된 값만 들어갈 수 있음)
insert into employees (employee_id, last_name, email, hire_date, job_id, salary)
values (501, 'test', 'test', sysdate, 'test', -10); -- salary는 0 이상
2.2. 문제
더보기
<sql />
-- 문제1
create table members(
m_name varchar2(3) not null,
m_num number(1) constraint mem_memnum_pk primary key,
reg_date date constraint mem_regdate_uk unique not null, -- 사실 날짜는 unique 들어가면 안됨
gender varchar2(1) constraint mem_gender_ck check (gender in ('M', 'F')),
loca number(4) constraint mem_loca_loc_locid_fk references locations (location_id)
);
commit;
insert into members values ('AAA', 1, '2018-07-01', 'M', 1800);
insert into members values ('BBB', 2, '2018-07-02', 'F', 1900);
insert into members values ('CCC', 3, '2018-07-03', 'M', 2000);
insert into members values ('DDD', 4, sysdate, 'M', 2000);
-- 문제2
select m_name, m_num, street_address, location_id
from members m
join locations l
on m.loca = l.location_id
order by m_num asc;
3. 뷰 (view)
- 외우지 않아도 되고 이해하기
- 다른 뷰를 기초로 하는 논리적 테이블 = 물리적X, 실제로는 존재하지 않는 가상의 테이블
- view는 제한적인 자료만 보기 위해 사용할 수 있는 가상테이블의 개념
- 필요한 컬럼만 저장해 두면 관리가 용이
- 뷰를 통해서 데이터에 접근하면 원본 데이터는 안전하게 보호 가능
- 정보를 여러 사람이 자주 사용해야 한다면 뷰를 만들어 놓고 뷰를 참조하면 됨
| 특징 | 단순 뷰 | 복합 뷰 |
| 테이블 수 | 하나의 원본 테이블 | 두 개 이상 (조인을 통해 생성된) |
| 함수 포함 | x | o |
| 데이터 그룹 포함 | x | o |
| 뷰를 통한 DML (연산?) | o | x |
<sql />
-- 뷰 정보 확인
select * from user_views;
<sql />
select * from emp_details_view;
-- 단순 뷰
-- 뷰의 컬럼 이름은 함수같은 가상표현식이면 안됩니다.
create or replace view view_emp
as (select employee_id,
first_name || ' ' || last_name as name,
job_id,
salary
from employees
where department_id = 60
);
select * from view_emp;
-- 복합 뷰
-- 여러 테이블을 조인하여 필요한 데이터만 저장하고 빠른 확인을 위해서 사용
create or replace view view_emp_dept_job
as (select
e. employee_id,
first_name || ' ' || last_name as name,
d.department_name,
j.job_title
from employees e
left outer join departments d
on e.department_id = d.department_id
left outer join jobs j
on e.job_id = j.job_id)
order by employee_id;
select * from view_emp_dept_job;
3.1. 권한, 생성, 수정
3.1.1. 1. 권한
- 정보확인을 빨리 하기 위해 권한 확인
3.1.2. 2. 생성
- create view 문장 내에 서브쿼리 작성
- 복합 뷰 생성
- 부서이름: departments 테이블과의 조인을 통해 알고,
직무이름: jobs 테이블과의 조인을 통해 알 수 있음 ↓
3.1.3. 3. 수정
- 열 별칭들은 서브쿼리에서의 열과 동일한 순서로 나열
- 이미 이름이 존재해도, 그 소유자에 대한 이전 버전을 새로운 뷰 구문으로 대체
동일 이름으로 만들면 수정됨
3.1.4. 4. 삭제
- 기본 테이블 기반으로 데이터 손실없이 뷰 삭제
<sql />
-- 뷰의 수정 (동일 이름으로 만들면 수정됨)
create or replace view view_emp_dept_job
as (select
e. employee_id,
first_name || ' ' || last_name as name,
e.salary, -- 추가
d.department_name,
j.job_title
from employees e
left outer join departments d
on e.department_id = d.department_id
left outer join jobs j
on e.job_id = j.job_id)
order by employee_id;
-- 뷰를 이용하면 데이터를 손쉽게 조회가능
-- job_title별 샐러리 평균
select job_title, avg(salary) as 평균 from employees e
left outer join jobs j
on e.job_id = j.job_id
group by job_title
order by avg(salary) desc;
select job_title, avg(salary) as 평균
from (select *
from employees e
left join jobs j on e.job_id = j.job_id)
group by job_title
order by 평균;
select job_title, avg(salary) as 평균
from view_emp_dept_job
group by job_title
order by 평균;
-- 뷰의 삭제
drop view 뷰이름;
3.2. 뷰를 이용한 DML 연산
<sql />
desc view_emp_dept_job;
-- 뷰를 통한 DML은 제한이 많습니다.
-- 가상열 칼럼이 있다면 허용되지 않음
insert into view_emp_dept_job(name, employee_id) values ('xxx', 300);
-- 원본테이들의 null을 허용하지 않는 경우도 안됩니다.
insert into view_emp_dept_job(employee_id, salary) values(100, 10000);
-- join된 뷰의 경우도 허용되지 않습니다.
insert into view_emp_dept_job(employee_id, job_title) values(300, 'xxx');
- 뷰에 대한 DML 연산이 조건을 만족할 때만 수행
- 뷰 값이 수정되면 실제 테이블에도 반영되기 때문에 뷰를 읽기 용도로만 사용
with check option - 읽기만 허용, 조건 컬럼 값을 변경하기 못하게 되는 옵션
with read only - select만 허용하는 옵션
with read only - 읽기 전용 뷰
<sql />
-- 뷰의 옵션
-- with check option - 조건컬럼 제약
create or replace view view_emp_test
as (select employee_id, first_name, department_id
from employees
where department_id in (60, 70, 80)
)
with check option;
select * from view_emp_test;
-- with read only - 읽기전용뷰 (조회만 가능)
create or replace view view_emp_test
as (select employee_id, first_name, department_id
from employees
where department_id in (60, 70, 80)
)
with read only;
3.3. 인라인 뷰(inline view)
- 별칭을 사용하는 서브 쿼리, 스키마 객체가 x
- from 절에 서브쿼리가 온 것 (from절에는 테이블, 뷰가 올 수 있음)
- 뷰도 독립적인 select문으로 from절에 사용하는 서브쿼리도 하나의 뷰로 볼 수 있음 - 이게 인라인뷰
오늘 하루
더보기
기억에 남는 부분
-
-
어려운 부분
-
-
문제 해결 부분
-
-
728x90
'프로그래밍 언어 > SQL-Oracle' 카테고리의 다른 글
| [SQL] 권한, 사용자생성, 역할, 비밀번호 변경 (0) | 2022.11.17 |
|---|---|
| [SQL] 시퀀스(Sequence), 인덱스(index) (0) | 2022.11.17 |
| [SQL] DDL 데이터 정의어, 테이블 CRUD (0) | 2022.11.15 |
| [SQL] DML 데이터조작어(insert, update, delete, merge, ctas), TCL(트랜잭션) (0) | 2022.11.15 |
| [SQL] 서브쿼리(단일행, 다중행, 스칼라), 인라인뷰, rownum (0) | 2022.11.11 |
