KOSA FullStack 교육/DB

KOSA fullstack 교육(테이블 제약조건, 트랜잭션)

로미로미로 2025. 5. 27. 16:54

테이블 데이타 타입

문자형 char(10) 5글자 입력 시 나머지 5칸은 공백 공간으로 채워진다 - 고정길이
varchar(10) 5글자 입력시 나머지 공간은 채워지지 않고 5칸으로 이루어진다 - 가변길이
숫자형 int  
decimal(6,2) 전체 숫자 6개, 소수점 2자리
날짜형 date 년, 월, 일
datetime 년, 월, 일, 시, 분, 초
timestamp 날짜 + 시간, 시스템 변경 시 자동으로 update

 

SQL 하위 언어

  • DML(데이타 조작 언어) - INSERT, DELETE, UPDATE, SELECT
  • DDL(데이타 정의 언어) - CREATE, DROP, ALTER, TRUNCATE
  • DCL(데이타 제어 언어) - COMMIT, ROLLBACK, SAVEPOINT, GRANT, REVOKE

 

AUTO_INCREMENT

- 데이타 입력 시 디비 테이블에서 자동으로 생성되는 값을 지정하는 것

  1. A,1000 데이타가 디비로 입력되는 시점에 디비에서 자동으로 생성
  2. 이 값은 무조건 PRIMARY KEY 제약 조건으로 지정 (PK 값은 오직 하나만 가능하다)
  3. AUTO_INCREMENT가 설정된 테이블에 INSERT할 때는, 컬럼을 명시적으로 지정. 그렇지 않으면 어떤 값이 자동 생성되는 값인지 혼동이 생길 수 있음

Table을 생성해보자 

create table custom (
	num int auto_increment,-- 여기에 PK 지정하는 것을 컬럼레벨 지정이라 한다. 
    name varchar(10) not null,
    address varchar(10) not null,
    birthday date, 
    primary key(num));
-- 이렇게 아래에 PK 지정하는 것을 테이블레벨 지정이라 한다.

 

 

 

삭제 연산

autocommit 실습과 삭제 연산 실습을 같이 해보았다. 

console 창을 두 개 띄우고,

각각 set autocommit=0; 을 해주자. 

autocommit 상태를 확인하려면 select @@autocommit;

 

이제 자동으로 commit이 되지 않고, 수동으로 commit을 해주어야 한다. 

한 쪽에서만 delete를 해주고 다른 쪽에서 select를 해보자.

 

commit을 해주지 않았기 때문에, 한 쪽에서는 삭제되지 않은 것을 볼 수 있다.

 

이제 truncate 연산을 해보자. 이건 모든 데이터를 삭제하는 명령어이다.

위와같이 truncate는 바로 삭제가 안되고, 다른 쪽에서의 commit을 기다린다. 

다른 쪽에서 commit을 해주니, truncate이 수행된다. 

이것이 트랜젝션의 원리이다. 

truncate은 자동 commit이 되고, 다시 돌아갈 수 없다. 

drop table도 마찬가지이지만 테이블 자체를 삭제한다는 점에서 차이점이 있다.

 

 

정리해보자면,

  • DELETE: 조건을 주고 삭제할 때 사용 - 자동 커밋 안됨
  • TRUNCATE: 모든 데이타를 삭제할 때 사용. 성능이 DELETE보다 좋음. 모든 데이타는 삭제하지만 테이블의 구조는 남긴다. 대용량 데이타를 삭제할 때 반드시 truncate 사용! (DROP TABLE + CREATE TABLE)  - 자동 커밋됨
  • DROP: 테이블의 구조도  다 없앰  - 자동 커밋됨

 

CTAS ( CREATE TABLE AS SELECT )

기존 테이블을 이용해서 새로운 테이블 생성, 기존 테이블의 데이타도 가져올 수 있다.
단 기존 테이블의 제약조건은 적용 안된다 !!

 

CREATE TABLE emp1
AS (SELECT * FROM emp);

CREATE TABLE emp2
AS (SELECT empno, ename, sal FROM emp);

SELECT * FROM emp1;
SELECT * FROM emp2;

 

제약 조건 추가하기

1. 기본키 제약 조건

2. 외래키 제약 조건

-- 1. PK 추가
ALTER TABLE employee ADD CONSTRAINT employee_empno_pk PRIMARY KEY(empno);
ALTER TABLE department ADD CONSTRAINT deptarment_deptno_pk PRIMARY KEY(deptno);

여기서 '테이블명_기본키컬럼_pk' 는 제약 조건의 이름이다

 

department 테이블에서 20번 부서를 삭제해보자 

DELETE FROM department WHERE deptno = 20;
select * from department; -- 20번 부서가 삭제된 것 확인가능
select * from employee; -- 20번 부서가 아직 존재 -> 그러므로 외래키 추가 필요

 

다시 돌려놓고 이제 외래키 제약 조건을 추가해보자 !!

ALTER TABLE employee ADD CONSTRAINT employee_deptno_FK FOREIGN KEY(deptno) 
REFERENCES department(deptno);

-> 이때 department의 기본키가 deptno인 상태여야 외래키 추가가 가능하다.

 

이제 20번 부서를 department에서 삭제해보자

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`kosa`.`employee`, CONSTRAINT `employee_deptno_FK` FOREIGN KEY (`DEPTNO`) REFERENCES `department` (`DEPTNO`))

 

부모(department)에서 delete 혹은 update가 불가능하다는 에러가 뜬다. 

 

이를 해결하기 위해 다시 외래키 제약 조건을 주자(ON DELETE CASCADE 추가)

ALTER TABLE employee ADD CONSTRAINT employee_deptno_FK FOREIGN KEY(deptno)
REFERENCES department(deptno) ON DELETE CASCADE;

 

DELETE FROM department WHERE deptno = 20;

 

이렇게 하고, employee 테이블과 department 테이블을 확인해보면 부서20이 삭제된 것을 확인할 수 있다.

 

만약 ON DELETE SET NULL 을 주면, 삭제가 되는 게 아닌 해당 외래키 컬럼을 NULL로 만든다.

 

트랜잭션

 

트랜잭션 실습을 해보자. 우선 콘솔 두 개를 mysql에 접속해서 띄운다.

 

mysql -u root -p 

set autocommit=false;

둘 다 직접 커밋을 할 것이기 때문에 autocommit 을 false로 바꾼다.

 

 

왼쪽: update문 실행

오른쪽: 확인하면 update 실행 안 된 상태

즉, commit을 하지 않았기 때문에 rock이 된 상태

그 후, 오른쪽에서는 sal을 1200으로 바꾸고, 

왼쪽에서 commit;을 해준다면

 

왼쪽은 1800이 나오고, 오른쪽은 아직 1200이 봉인되어있는 상태이기에 1200이 나온다.

오른쪽에서 commit을 해줘서 rock을 풀고,

왼쪽도 commit을 해준다면

최종으로 1200이 되는것을 볼 수 있다.