17일차 - 컬럼의 제약조건

제약조건(DDL)

■ 컬럼에 데이터를 넣을 때 특정한 조건을 만족시켜야 넣을 수 있다

1) PRIMARY KEY

■ A)  UNIQUE : 컬럼안의 데이터의 중복을 허용하지 않는다

■ B) NOT NULL : 데이터를 입력하지 않고 비워둘 수 없다

■ C) 테이블 종속적이다 한 테이블에 하나의 데이터만 존재한다

★ 예를 들어 이름이 동일한 사람에게 이름을 부르면 둘다 대답할 것이다. 이렇게 되면 두명은 누구를 부른것인지 헷갈린다

두명에게 고유번호를 부여하고 똑같은 번호를 다른 사람에게 주지 않고 변경하지 않는다면, 번호를 부르면 그에 해당하는 사람이 대답을 할 것이다

/* 제약 조건 */
-- 1) PRIMARY KEY (기본키 제약조건)
-- a) 새로운 테이블을 생성할때 조건을 추가한다
use mydb;
create table pk_test (
	first_col int(3) primary key,
	second_col varchar(4)
);
show tables;
desc pk_test;
-- b) 만들어진 테이블을 수정하여 조건을 추가한다
-- ALTER TABLE [테이블의 이름] ADD CONSTRAINT [제약조건 타입] (적용 필드)
-- ALTER TABLE [테이블의 이름] ADD [제약조건 타입] (적용 필드)
alter table employees add constraint primary key (emp_no);
alter table employees add primary key (emp_no);
desc pk_test;

-- 중복 데이터를 넣어 어떤 결과를 출력하는지 확인한다
select * from employees order by emp_no;
insert into employees (
	emp_no,
	first_name,
	family_name,
	email,
	mobile,
	salary 
) values (
	119,
	'항오',
	'조',
	'serach@daum.net',
	'01012341234',
	4500000
);
-- SQL Error [1062] [23000]: (conn=12) Duplicate entry '119' for key 'PRIMARY'

 

 

2) Combination key 

■ PRIMARY KEY 와 제약조건은 동일하다 (중복, 유일, 수정 금지)

★ 앞서 설명했듯이 예를 들어 학생의 이름을 토대로 테이블을 만들었다고 가정하면, 학생의 이름은 기본키가 될 수 없다 왜냐하면 이름은 동명이인이 있을 수 있어, 학번을 부여하거나 아님 이름과 생년월일을 조합하여 기본키를 만들 수 있다

-- 2) 복합키(combination key)
-- 마땅히 기본키를 사용할 컬럼이 없어, 두개의 컬럼을 조합하여 새로운 컬럼으로 기본키로 설정했다
create table pk_to_test(
	first_col int(5),
	second_col varchar(10),
	third_col date,
	primary key (first_col, second_col)
);
desc pk_to_test;

3) NOT NULL

■ NOT NULL 은 제약조건이지만 속성으로 취급한다

■ 빈값을 테이블에 삽입하는것을 허용하지 않는 제약조건이다

-- 3) NOT NULL (NULL 를 허용하지 않는다)
-- NOT NULL 은 제약조건이지만 속성으로 취급한다
-- 테이블 생성 후 NOT NULL 넣기
-- ALTER TABLE [테이블의 이름] MODIFY [컬럼의 이름](크기) NOT NULL;
alter table pk_test modify second_col varchar(4) not null;
desc pk_test;

/* 제약 조건 확인 */
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'pk_to_test';

/* 제약 조건 삭제 */
-- alter table [테이블의 이름] drop [제약 조건 종류]
alter table pk_to_test drop primary key;

4) Foreign Key

★ 연계 참조 무결성 제약 조건 부모 자식 관계인 테이블에서 논리적으로 앞뒤가 맞아야하는 제약조건을 말한다

예를 들어서 자식은 있는데 부모가 없는 논리적으로 앞뒤가 안맞는 상황을 방지해준다

■ 부모 테이블의 기본키를 자식 테이블에서 참조한 키를 말한다 

 

1) 1번 학생이 독서 동아리에 가입
2) 1번 학생이 코딩 동아리에 가입
3) 1번 학생이 댄스 동아리에 가입
-> 학생은 부모 테이블, 동아리는 자식 테이블이라고 하면, 동아리라는 테이블에서 기본키가 없다면 1번 학생이 하나의 동아리만 가입할 수 있다

-- 1) 참조 제약 조건(외래키 제약조건)
-- a) 테이블 생성시 추가하는 방식
create table parent_table(
	user_id varchar(30) primary key,
	user_name varchar(20),
	user_phone varchar(20),
	user_address varchar(100)
);
create table child_table(
	order_id int(10)
	,user_id varchar(30)
	,product_name varchar(30)
	,price int(10)
	,qty int(5)
-- 	,foreign key(user_id) references parent_table(user_id)
);
show tables;
desc child_table;
-- b) 테이블 생성 후 추가하는 방식
-- ALTER TALBE [테이블의 이름] ADD CONSTRAINT [제약조건 종류](적용 필드) REFERENCES [가져올 테이블](가져올 컬럼)
alter table child_table add constraint foreign key(user_id) references parent_table(user_id);
select * from information_schema.TABLE_CONSTRAINTS  where TABLE_NAME = 'child_table';

create table parent_table(
	user_id varchar(30) primary key,
	user_name varchar(20),
	user_phone varchar(20),
	user_address varchar(100)
);
insert into parent_table values (
	'user01'
	,'김철수'
	,'01012341234'
	,'서울 금천구'
);
delete from parent_table where user_id = 'user01';
insert into parent_table values (
	'user02'
	,'홍길동'
	,'01012341234'
	,'서울 금천구'
);
insert into parent_table values (
	'user03'
	,'박영수'
	,'01012341234'
	,'서울 금천구'
);
select * from parent_table;
create table child_table(
	order_id int(10)
	,user_id varchar(30)
	,product_name varchar(30)
	,price int(10)
	,qty int(5)
-- 	,foreign key(user_id) references parent_table(user_id)
);
insert into child_table values(1, 'user01', '의류', 6000, 1);
delete from child_table where user_id = 'user01';
delete from child_table ;
delete from parent_table ;
insert into child_table values(2, 'user02', '의류', 6000, 1);
insert into child_table values(3, 'user03', '의류', 6000, 1);
select * from child_table;
select * from parent_table;
-- 부모한테 없는 값을 외래키에 넣으려고 하면 에러가 나타난다
-- 연계 참조 무결성이 깨졌다
-- 부모테이블의 기본키는 user04 가 없지만, 자식이 그것을 쓰려고 했다 -> 부모없는 자식을 만들려고 함
insert into child_table values(4, 'user04', '의류', 6000, 1);
-- SQL Error [1452] [23000]: (conn=13) Cannot add or update a child row: a foreign key constraint fails
-- (`mydb`.`child_table`, CONSTRAINT 
-- `child_table_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `parent_table` (`user_id`))

-- 부모를 지워보자 
-- 자식이 있는 부모를 없애려고 해서 에러가 발생했다
delete from parent_table where user_id = 'user01';
delete from child_table where user_id = 'user01';
-- 에러 발생

-- 자식을 먼저 부모를 나중에 지워야한다
-- 하나의 부모에 열개의 자식 테이블이 있다면, 지우기 쉽지 않다
-- 이런 경우를 대비해서 부모를 지우려고하면 자식부터 차근차근 단계별로 지워주는
-- 연계 참조 무결성을 해치지 않고, 삭제하려면 자식이 먼저 삭제되고, 이후 부모가 삭제되어야 한다
-- 이 과정을 내부에서 작업해주는 ON DELETE CASCADE 옵션이 있다

-- 테이블 생성할 때 붙여줘야한다 안타깝게도 parent_table과 child_table 은 불가능하다
-- 그래서 새로운 테이블을 만들어서 예제를 실행해보자
create table supplier(
	sup_id int(10) primary key
	,sup_name varchar(50) not null
	,phone varchar(13)  
);
create table products(
	pd_id int(10) primary key
	,sup_id int(10) 
	,price int(10)
	,foreign key (sup_id) references supplier(sup_id) on delete cascade
-- 단계적으로 차근차근 삭제해준다
);
insert into supplier values (1,'김철수','01012341234');
insert into supplier values (2,'안철수','01012341234');
insert into supplier values (3,'박철수','01012341234');
select * from supplier;
insert into products values(1111, 1, 6000);
insert into products values(1112, 2, 6000);
insert into products values(1113, 3, 6000);
select * from products;

delete from supplier where sup_id = 1;
-- 과연 지워질까?

5) Unique

■ 중복을 허용하지 않는 제약 조건이다 테이블에 다수 컬럼에 제약을 걸 수 있다

■ NOT NULL + UNIQUE 가 붙은 컬럼은 후보키 즉 기본키가 될 수 있는 후보가 되는 키이다

-- 3) unique 제약조건
-- ALTER TABLE [테이블의 이름] ADD CONSTRAINT [제약조건 종류](컬럼의 이름)
alter table supplier add constraint unique(sup_name);
desc supplier;
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME ='supplier';

insert into supplier values(4, '박철수', '01012341234');
-- SQL Error [1062] [23000]: (conn=13) Duplicate entry '박철수' for key 'sup_name'

6) Check

■ 조건에 맞지 않는 데이터가 들어오면 에러를 발생시키고, 맞으면 트랜잭션을 진행한다

■ 조건을 수정하려면 테이블를 수정해야한다 그래서 매우 민감한 작업으로 지양하는 것이 좋다

★ 주의사항 1) 테이블에 데이터가 들어있다면, 그 데이터들도 조건에 만족해야한다 그렇지 않다면 에러를 발생시키기 때문이다

★ 주의사항 2) 조건을 수정하려면 테이블를 수정해야한다 자주 수정해야 한다면, 에러를 발생시키기 좋다

★ 주의사항 3) 컬럼에 어떤 조건이 들어가 있는지 확인하기 어렵다

-- 4) check 제약 조건
-- ALTER TABLE [테이블의 이름] ADD CONSTRAINT [제약조건 종류](조건)

-- price 가 5000원 미만인 경우만 받겠다
alter table products add constraint check(price < 5000);
-- SQL Error [4025] [23000]: (conn=13) CONSTRAINT `CONSTRAINT_1` failed for `mydb`.`products`
-- 기존에 있는 값이 그 조건을 만족하지 않으면 조건을 걸 수 없다

-- price 가 6000원 인 경우만 받겠다
alter table products add constraint check(price >= 6000);
insert into products values(1114,2,9000);

-- 찾는 방법은?
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'products';

 

1. 컬럼이란?

테이블의 열을 말하고, 데이터를 저장하기 위한 속성 또는 필드라고 말한다

2. 컬럼과 로우가 뭐죠?

로우는 하나의 데이터를 저장하는 값으로 튜플이라고도 한다

3. DCL이 뭐였죠?

DCL 은 Data Control Lauange) 의 약자로 계정을 생성하고 권한을 부여하고 회수하는 명령문을 담고 있는 언어이다

3-1. DCL 로 뭘 할 수 있었어?

4. DML 은 뭐야?

테이블의 데이터를 삽입,삭제,수정,조회할 수 있는 명령문의 언어이다

5. DML 어떤 어떤 명령문이 있었나요?

6. SELECT 는 뭐야

먼저 조회할 테이블을 지정하고, 그 테이블의 컬럼들을 선택하여 원하는 데이터를 조회할 수 있는 명령어이다

7. WHERE 는 뭐야

컬럼에 특정한 조건을 걸어서 원하는 데이터를 조회하기 위해 사용한다

8. ORDER BY 는 뭐야

조회된 데이터들을 오름차순, 내림차순으로 정렬하여 반환해주는 속성으로 많은 양의 데이터를 먼저 선별한 후 정렬해주는것이 효율적이다

9. 오름차순과 내림차순의 차이는?

숫자는 크기가 작은것부터 큰것까지 순서대로 나열한 것을 오름차순 그 반대가 내림차순이다

10. IN 의 장점

IN 은 WHERE 단점을 극복하기 위해 만들어졌는데, 조건을 걸고 싶은 컬럼이 중복되는 상황이라면 IN 으로 한번에 묶어서 다중 조건을 걸 수 있다

11. LIKE 는?

LIKE 는 문자 데이터를 검색할때 입력받은 문자열과 유사한 레코드들을 찾아주는 연산자이다

12. % 는?

와일드카드는 문자열이 없거나, 아무 문자열이 들어올 수 있는 문자열을 가리킨다

13. GROUP BY 는 ?

특정 열을 기준으로 동일한 데이터들을 하나의 데이터로 그룹 단위 표시하고, 그룹 단위로 함수를 적용하여 결과를 보여준다 만약 아무런 함수를 적용하지 않았다면 동일한 데이터 중 첫번째 값을 표시한다

14. HAVING

GROUP BY 에서 그룹화된 데이터를 보여주기 때문에 WHERE 은 개별 데이터에서만 적용할 수 있기 때문이다 HAVING 은 그룹화 된 데이터에서 조건을 걸어 결과를 보여준다

15. 트랜잭션이란?

데이터베이스에서 더이상 쪼갤 수 없는 업무처리 작업을 의미하고, 도중에 오류가 생기면 시작 전으로 복원시켜야한다 

16. COMMIT

사용자가 현재 작업한 상태를 데이터베이스에 저장한다

17. 트랜잭션의 예시

은행의 출금 업무에 필요한 작업들을 트랜잭션이라고 하고, 출금 도중에 오류가 발생하면 시작 전으로 돌아가야한다

18. 기본키란?

1. 중복을 허용하지 않고

2. NOT NULL

3. 테이블 종속적이다 한테이블에 하나만 존재한다

19. 기본키는 테이블 종속적인데 여기서 종속적이라는 의미는?

한 테이블에 하나의 레코드만 존재한다는 의미이다

20. 복합키란?

테이블 안의 모든 컬럼들이 레코드들이 중복이 될 수 있어 기본키를 설정하기 어려울 때, 두개 이상의 컬럼을 조합하여 새로운 기본키를 만들어내는 것을 말한다. 복합키는 PRIMARY KEY(컬럼 1, 컬럼 2, ..) 으로 만들 수 있다

21. NOT NULL

해당 컬럼은 빈 값을 허용하지 않는 속성을 말한다

22. 외래키란?

부모 테이블의 기본키를 자식 테이블에서 참조하는 컬럼을 지정할 수 있다 외래키는 연계 참조 무결성을 보장할 수 있다 

23. UNIQUE 제약조건이란?

유니크가 지정된 컬럼은 동일한 레코드가 존재할 수 없는 속성이다 만약 동일한 레코드를 삽입하거나 수정하려고하면 에러가 발생한다

24. 연계 참조 무결성 제약조건이란?

부모 자식 관계인 테이블에서 논리적으로 앞 뒤 말이 맞아야하는 제약조건을 말한다

25. 어떤 경우에서 이 조건을 수행하지 못하는가?

자식은 있는데 부모가 없는 경우