데이터베이스 테이블 제약조건

테이블

- 테이블은 실제로 데이터들이 저장되는 곳

- 테이블은 데이터베이스의 기본적인 데이터 저장 단위이다.

엔티티 = 개체

 

테이블 명명법

※ 테이블 생성시 제한사항과 고려할점
- 테이블 이름과 컬럼은 항상 알파벳 문자로 시작해야 하며 A~Z까지의 문자, 0~9까지의 숫자, 그리고 $,#,_(Under Bar)를 사용할 수 있다. (공백 사용 불가능)

 

 

테이블 생성 예제

-- emp2와 dept2테이블을 생성하는 예제이다.
SQL> CREATE TABLE EMP2(
    EMPNO  NUMBER    CONSTRAINT emp_pk_empno PRIMARY KEY,     
--  (컬럼) (데이터타입)  (제약조건)
    ENAME  VARCHAR2(20),
    JOB    VARCHAR2(40),
    MGR    NUMBER,
    HIREDATE  DATE,
    SAL    NUMBER,
    COMM   NUMBER,
    DEPTNO NUMBER);
테이블이 생성되었습니다.

SQL> CREATE TABLE DEPT2(
    DEPTNO  NUMBER  CONSTRAINT dept_pk_deptno  PRIMARY KEY,
    DNAME   VARCHAR2(40),
    LOC     VARCHAR2(50)) ;
테이블이 생성되었습니다.

 

 

테이블 조회

-- USER_TABLES 데이터사전을 조회 하면 유저가 소유한 테이블을 확인 할 수 있다.
SQL>SELECT table_name FROM  USER_TABLES;

-- test유저가 관리하는 테이블 정보
select * from tab -- 간략한 정보 출력
select * from user_tables -- 자세한 정보 출력

TABLE_NAME
------------
BONUS
CRETABLE
DEPT
DUMMY
EMP
EMP2
SALGRADE

 

 

테이블 제약조건(Constraint)

- 제약조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러 가지 규칙을 적용해 놓는거라 생각하면 된다. 간단하게 테이블안에서 테이터의 성격을 정의하는 것이 바로 제약조건 이다.

 

- 부적절하다는 의미는 국어 점수가 100점을 넘으면 안되는데 200점이라는 데이터가 들어오는 경우

- 해당되는 범위를 벗어나는 부적절한 데이터를 방지하기 위해서 규칙을 적용하는 것이 제약조건이다. (데이터의 무결성)

 

- 남자 man male 남 성별적는칸에 이렇게 입력하는 경우 뜻은 전부 남자인데, 규칙이 없다. 조회할 때 이것이 문제가 된다.

남자라는 정보를 조회할 때 어떤 정보를 입력해서 조회할 것인지를 정해야 한다. 데이터에 대한 제약조건을 거는 것은 데이터에 대한 일관성을 만들어준다.

 

- 제약조건을 의미있는 이름을 부여하면 CONSTRAINT를 쉽게 참조해서 오류를 찾을 수 있다.

 

NOT NULL 조건
컬럼을 필수 필드화 시킬 때 사용 한다. (테이블안에 기입할 때 콤마 잘 확인하자)

[제약조건]
: 데이터의 무결성(Data Integrity)을 지키기 위한 설정

1. null(값이 없는 상태) / not null(필수입력)
create table tb1
(
-- [컬럼] [자료형]     [제약조건]
    name varchar2(100) not null, -- 회원가입시에 이름은 필수이기에 not null을 넣어준다.
    addr varchar2(100) null, -- 필수 입력받지 않아도 되면 null
    email varchar2(100), -- 아무런 값도 넣지 않으면 기본적으로 default 값이 null로 들어간다.
)
insert into tb1 values('일길동','서울시 관악구','abc123@google.net');
insert into tb1(name) values('이길동');
insert into tb1 values('삼길동',null,null);
select * from tb1 -- 입력한 데이터 조회

: table 내에서 inline방식만 허용된다
다른 제약조건은 다른 곳에서 제약을 걸 수 있는데, null 값은 내부에서만 이뤄져야한다.

-- error
insert into tb1(addr,email) valuse('서울','a@b.com');

 

UNIQUE 조건
데이터의 유일성을 보장(중복되는 데이터가 존재할수 없다) 되고, 자동으로 인덱스가 생성 된다.

2. unique제약 : 도메인내에 중복값 허용안함(유일한 값)
도메인 : 필드의 집합(해당하는 열로 모인 데이터)
create table tb2
(
    name varchar2(100) not null,
    id varchar2(100) unique not null
)
insert into tb2 values('일길동','one');
insert into tb2 values('원길동','one');
select * from tb2 -- 입력한 데이터 조회

id가 중첩될 시에 나오는 오류

create table tb22
(
    name varchar2(100) not null,
    id varchar2(100)
)

-- 제약조건을 등록
alter table tb22
    add constraint unique_tb22_id unique(id);
--                 (제약조건명)

insert into tb22 values('일길동','one');
insert into tb22 values('원길동','one');
select * from tb22 -- 입력한 데이터 조회

-- 제약조건부여방법3
create table tb222
(
    name varchar2(100) not null,
    id varchar2(100),
    -- 제약조건작성
    constraint unique_tb222_id unique(id)
)

내가 제약조건명을 걸어놓으면 무슨 오류가 났는지 직관적으로 확인이 가능하다.

 

 

CHECK 조건
컬럼의 값을 어떤 특정 범위로 제한할 수 있다.

3. check제약 : 필드내의 유효성(범위/값) 검증

cf)연산자
   산술 : + - * / mod(피젯수,젯수)
   select 1+1, 3-1, 2*3, 10/3, mod(10,3) from dual
   관계 : > >= < <= =(같냐) !=(같지않냐) <>(같지않냐)
   논리 : and <- A and B
          or  <- A or B
          not <- not true
   기타 : 필드 between A and B <- A <= 필드 <= B(A~B 사이냐?)
          필드 in(A,B,C) <- 필드=A or 필드=B or 필드=C

 

- 점수에 관련된 예시 (점수는 0점~100점 사이에 있어야 한다)

create table tb3
(
   name varchar2(100) not null,
   kor int,
   eng int,
   math int
)

-- 국어점수 제약 등록
alter table tb3
    add constraint ck_tb3_kor check(kor>=0 and kor<=100);
--                 (제약조건명) check(조건)

-- 영어점수 제약 등록
alter table tb3
    add constraint ck_tb3_eng check(eng between 0 and 100);

-- 수학점수 제약 등록
alter table tb3
    add constraint ck_tb3_math check(math between 0 and 100);
    
insert into tb3 values('차선일',100,100,100);
select * from tb3	

-- error kor
-- ORA-02290: 체크 제약조건(TEST.CK_TB3_KOR)이 위배되었습니다
insert into tb3 values('김국어',101,88,99);
-- error eng
-- ORA-02290: 체크 제약조건(TEST.CK_TB3_ENG)이 위배되었습니다
insert into tb3 values('삼영어',100,-88,100);
-- error math
-- ORA-02290: 체크 제약조건(TEST.CK_TB3_MATH)이 위배되었습니다
insert into tb3 values('백수학',100,88,200);

-- 제약 조건 삭제
alter table tb3
   drop constraint ck_tb3_kor;
   
-- 제약 조건 수정
alter table tb3
   add constraint ck_tb3_kor check(kor>=0 and kor<=101);
   
-- 아래 insert문 정상 작동
insert into tb3 values('김국어',101,88,99);

 

- 성별에 대한 제약조건 걸기(남자 or 여자)

-- 성별에 대한 제약설정('남자' 또는 '여자')
create table tb33
(
   name varchar2(100) not null,
   gender varchar2(100)
)

alter table tb33
    add constraint ck_tb33_gender check(gender='남자' or gender='여자');
alter table tb33
    add constraint ck_tb33_gender check(gender in('남자','여자'));
    
insert into tb33 values('일길동','남자');
insert into tb33 values('박길동','man'); -- error

select * from user_constraints -- 들어온 제약조건 확인

 

 

DEFAULT(컬럼 기본값) 지정
데이터를 입력 하지 않아도 지정된 값이 기본으로 입력 된다.

4. default 제약 : 값을 입력하지 않으면 default 값으로 들어간다.
	create table tb4
	(
	   name varchar2(100) not null,
	   gender varchar2(10) default '남자',
	   regdate date default sysdate -- sysdate 현재시간 출력
	)
	
	insert into tb4 values('이미자','여자',sysdate);
	insert into tb4(name) values('박미자');
	select * from tb4

 

PRIMARY KEY 지정

- 기본키는 UNIQUE 와 NOT NULL의 결합과 같다.
- 기본키는 그 데이터 행을 대표하는 컬럼으로서의 역할을 수행하여 다른 테이블에서 외래키들이 참조할 수 있는 키로서의 자격을 가지고 있다. 이를 참조 무결성이라 한다.
- UNIQUE 조건과 마찬가지로 기본키를 정의하면 자동으로 인덱스를 생성하며, 그 이름은 기본 키 제약 조건의 이름과 같다.

- primary key는 중복해서 생성할 수 없다.
- INDEX: 검색 키로서 검색 속도를 향상 시킨다.(UNIQUE,PRIMARY KEY 생성시 자동적으로 생긴다.)

5. primary key(기본키)
1) 레코드를 대표하는 대표키(행을 구분할 용도)
2) unique(index 설정) + not null
   index(해당필드의 검색트리 생성) : 검색성능이 향상
3) 2개 이상의 컬럼을 묶어서 사용할 수 있다.

create table tb5
(
   idx int,
   name varchar2(100) not null
)
-- primary key 등록
alter table tb5
  add constraint pk_tb5_idx primary key(idx);

-- 정상출력
insert into tb5 values(1,'일길동');
-- error : ORA-01400: NULL을 ("TEST"."TB5"."IDX") 안에 삽입할 수 없습니다
insert into tb5 values(null,'널길동');
-- error : ORA-00001: 무결성 제약 조건(TEST.PK_TB5_IDX)에 위배됩니다
insert into tb5 values(1,'하나길동');

 

- 시퀀스는 insert가 될 때 마다 값 자체를 추가 해줘서 이렇게 unique로 지정해놓은 값이 중복될 일이 없다.

오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있다. B-tree 인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 낸다.

 

FOREIGN KEY(외래 키)지정

- 기본키를 참조하는 컬럼 또는 컬럼들의 집합이다.
- 외래키를 가지는 컬럼의 데이터 형은 외래키가 참조하는 기본키의 컬럼과 데이터형과 일치해야 한다. 이를 어기면 참조무결성 제약에의해 테이블을 생성할수 없다.
- 외래키에 의해 참조되고 있는 기본 키는 삭제 할 수 없다.
- ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 그 기본키가 삭제 될 때 같이 삭제 된다.

 

 

- 아래의 표와 같이 중복데이터0발생을 방지하기 위해서 테이블분리를 했다.

- 테이블분리를 통해서 학번(PK)을 Parent키로 설정해놓고 (Primary Key) 학번(FK) (Foreign key)를 연결시켜서 관계선을 만들어서, 부모키의 학번을 Foreign key가 참조해서 사용한다.

6. foreign key(외래키)
	: 참조값이 외래테이블의 부모키를 참조한다.
	  참조할 수 있는 부모키의 조건은 (unique)이어야 한다.
	
create table 학생들
(
   학번 int,
   이름 varchar2(100) not null,
   전화 varchar2(100) not null,
   보호자명 varchar2(100) not null,
   보호자직업 varchar2(100) not null,
   주소 varchar2(100) not null
)

alter table 학생들
  add constraint pk_학생들_학번 primary key(학번);

insert into 학생들 values(1,'일길동','111-1111','일아비','자영업','서울');
insert into 학생들 values(2,'이길동','222-2222','이아비','회사원','경기도');

select * from 학생들

create table 성적2
(
   일련번호 int,
   학번 int,
   국어 int default 0,
   영어 int default 0,
   수학 int default 0,
   학년 int,
   학기 int,
   시험종류 varchar2(100)
)

-- 기본키
alter table 성적2
  add constraint pk_성적2_일련번호 primary key(일련번호);

-- 외래키
alter table 성적2
  add constraint fk_성적2_학번 foreign key(학번)
                              references 학생(학번) -- 학생의 학번을 참조해준다.(부모키 설정)
     --on delete casecade : 부모키 삭제시 자식도 삭제
     --on update casecade : 부모키 수정시 자식도 수정

insert into 성적2 values(1,1,100,100,100,1,1,'중간고사');
insert into 성적2 values(2,1,100,100,100,1,1,'기말고사');

-- error : ORA-02291: 무결성 제약조건(TEST.FK_성적2_학번)이 위배되었습니다- 부모 키가 없습니다
insert into 성적2 values(3,3,100,100,100,1,1,'기말고사');

select * from 성적

 

제약 조건의 확인

- USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건 조회.
- USER_CONSTRAINTS　: 유저가 소유한 모든 제약 조건 조회.

-- 사용자 객체를 관리하는 테이블(Dictionary Table)
: user_tables / user_constraints / ...
select * from user_constraints

create table company
(
    c_idx int not null,
    c_name varchar2(100) not null,
    c_ceo_name varchar2(100) not null,
    c_addr varchar2(200),
    c_tel varchar2(100),
    c_type varchar2(100)
)

create table product
(
    p_idx int not null,
    p_model_num varchar2(100) unique not null,
    p_name varchar2(100) not null,
    p_price int,
    c_idx int
)

alter table company
   add constraint pk_c_idx primary key(c_idx);

alter table company
   add constraint ck_c_type check(c_type in('코스피','코스닥'));

alter table product
   add constraint pk_p_idx primary key(p_idx);

alter table product
   add constraint ck_p_price check(p_price between 0 and 100000000);

alter table product
   add constraint fk_c_idx foreign key(c_idx)
                           references company(c_idx);

insert into company values(1,'삼성','이재용','삼성동','010-1234-1234','코스피');
insert into company values(2,'LG','박장환','목동','010-7566-0000','코스피');
insert into company values(3,'SONY','김찬정','판교','010-1111-1234','코스피');

insert into product values(1,'358-189-18189','코털제거기','500000','2');
insert into product values(2,'453-801-45604','트렘펄린','150000','3');
insert into product values(3,'508-150-83695','LG그램','1500000','2');
insert into product values(4,'105-357-93127','김장용배추','35000','1');
insert into product values(5,'185-075-65600','드럼세탁기','1000000','3');
insert into product values(6,'905-365-15043','토레타제조기','300000','1');

select * from company;
select * from product;