파트 6-1. 테이블과 제약조건

1. workbench에서 테이블 생성


실제로는 쿼리문으로 만드는게 더 편하게 느껴질 것이다. 하지만 방법정도는 알아두자.

그림1

앞서 배웠듯이 쿼리창에서 쿼리문으로도 테이블을 만들 수 있고 navigator 창에서도 해당 데이터베이스의 tables를 오른쪽 클릭으로 위 그림과 같이 테이블을 만들 수 있다. 그리고 navigator에서 해당 테이블을 오른쪽 클릭 -> selectrows로 바로 조회가 가능하다.


그림2

select로 조회한 뒤에 그림에 표시한 버튼을 클릭하면 insert문을 사용하지 않고 바로 값을 대입할 수 있다. 대입한 뒤에 apply를 누르면 적용된다.


그림3

만약 잘못 입력한 행을 삭제하고 싶다면 행 맨 앞에서 오른쪽 클릭 -> delete row를 클릭하면 된다.


2. sql문으로 테이블 생성


그림4

위와 같은 테이블을 sql문으로 작성해보자.

USE tabledb;

DROP TABLE IF EXISTS buytbl, usertbl;
CREATE TABLE usertbl 
( userID  CHAR(8) NOT NULL PRIMARY KEY, 
  name    VARCHAR(10) NOT NULL, 
  birthYear   INT NOT NULL,  
  addr	  CHAR(2) NOT NULL,
  mobile1	CHAR(3) NULL, 
  mobile2   CHAR(8) NULL, 
  height    SMALLINT NULL, 
  mDate    DATE NULL 
);


DROP TABLE IF EXISTS buytbl;
CREATE TABLE buytbl 
(  num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
   userid  CHAR(8) NOT NULL ,
   prodName CHAR(6) NOT NULL,
   groupName CHAR(4) NULL , 
   price     INT  NOT NULL,
   amount    SMALLINT  NOT NULL 
   , FOREIGN KEY(userid) REFERENCES usertbl(userID)
);

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL  , NULL      , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL  , NULL      , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');

INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL, 30, 2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200,  1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,  5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50,   3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80,  10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책'    , '서적', 15,   5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL   , 30,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL   , 30,   2);
  • 구매테이블의 foriegn key가 회원테이블에 연결되어 있으므로 반드시 구매테이블 먼저 삭제하고 회원테이블을 삭제해야한다.
  • 꼭 넣어야 하는 것은 not null, 안 넣어도 되는 것은 null, null의 경우는 굳이 작성하지 않아도 되지만 작성하는 것을 권장한다.
  • primary key는 당연히 not null이기 때문에 적어주지 않아도 되지만 작성하는 것을 권장한다.
  • auto_increment 자동으로 순번을 정해준다.
  • foreign key (열이름) references 테이블이름(열이름) 으로 fk와 pk를 연결시켜준다.
  • pk와 fk가 연결되어 있는데 아직 pk에 정보 삽입이 안되었는데 fk를 삽입하면 오류가 발생하므로 pk를 먼저 삽입해주고 fk정보를 삽입 해준다.


3. 제약 조건


mysql은 데이터의 무결성을 위해 다음 5가지 제약 조건을 제공한다.

  • primary key 제약조건
  • foreign key 제약조건
  • unique 제약조건
  • check 제약조건
  • default 정의
  • null 값 허용


4. primary key(기본 키 제약조건)

테이블에 존재하는 많은 행의 데이터를 구분할 수 있는 식별자를 기본 키(primary key)라고 부른다. 예를 들어, 학번, 아이디와 같은 데이터들이다.

create table usertbl ( userid char(8) not null primary key);
describe usertbl;

테이블의 정보를 보기 위해서는 describe문을 사용하면 된다. 그림5


키 이름 만들기

필요하다면 primary key를 지정하면서 키의 이름까지 직접 지어줄 수 있다.

-- 형식
constraint primary key 키이름 (열이름)

-- 예시
create table usertbl 
( userid char(8) not null,
  constraint primary key pk_usertbl_userid (userid)
);


이미 만들어진 테이블에 primary key값 주고 이름 만들기

-- 형식
alter table 테이블이름 add constraint 키이름 primary key (열이름)

-- 예시
create table usertbl 
( userid char(8) not null);
alter table usertbl -- usertbl을 변경하자
  add constraint pk_usertbl_userid -- 제약조건을 추가할건데 그 조건 이름은 ... 다
    primary key (userid); -- 추가할 조건은 기본키 제약조건이고 설정할 열은 ..이다.


열을 합쳐서 하나의 기본 키로 설정하기

기본 키는 각 테이블 별로 하나만 존재해야 하지만, 기본 키를 하나의 열로 구성해야만 하는 것은 아니다. 필요에 따라서 두 개 또는 그 이상의 열을 합쳐서 하나의 기본 키로 설정하는 경우도 있다.

그림6

그림과 같이 제품은 여러 개가 있을 수 있으니 제품코드는 같을 수 있고, 제품 일련 번호는 제품의 종류가 다르면 같을 수도 있으니 위 테이블에서는 pk로 지정할 열이 없다. 하지만 제품 코드와 제품 일련 번호를 합친다면 유일한 값이 될 수 있다.

-- 방법 1
create table prodtbl
( prodcode char(3) not null,
  prodid char(4) not null,
  proddate datetime not null,
  prodcur char(10) null
);
alter table prodtbl
  add constraint pk_prodtbl_procode_prodid
    primary key (prodcode, prodid);

-- 방법 2
create table prodtbl
( prodcode char(3) not null,
  prodid char(4) not null,
  proddate datetime not null,
  prodcur char(10) null,
  constraint pk_prodtbl_procode_prodid
    primary key (prodcode, prodid)
);

-- 테이블 정보 확인
show index from prodtbl

그림7


5. 외래 키 제약 조건(foreign key)


외래 키 제약 조건은 두 테이블 사이의 관계를 선언함으로써 데이터의 무결성을 보장해주는 역할을 한다. 외래 키 관계를 설정하면 하나의 테이블이 다른 테이블에 의존하게 된다. 외래 키 테이블이 참조하는 기준 테이블의 열은 반드시 primary key이거나 unique 제약 조건이 설정되어 있어야 한다.

-- 형식
foreign key () references 기준테이블이름(기준열)

-- 예시
create table usertbl
( userid char(8) not null primary key,
name varchar(10) not null,
birthyear int not null
);
create table buytbl
(num int auto_increment not null primary key
userid char(8) not null,
prodname char(6) not null,
foreign key (userid) references usertbl(userid)
);


외래 키 키이름 만들기

-- 선언과 동시에 키이름 부여 방법
create table buytbl
(num int auto_increment not null primary key
userid char(8) not null,
prodname char(6) not null,
-- 외래 키이름 부여
constraint fk_usertbl_buytbl foreign key (userid) references usertbl(userid)
);

-- 만들고 이후에 키이름 부여 방법
create table buytbl
(num int auto_increment not null primary key
userid char(8) not null,
prodname char(6) not null,
);
alter table buytbl -- buytbl을 수정한다
  add constraint fk_usertbl_buytbl -- 제약 조건을 더하고 그 이름은 ... 이다
  foreign key (userid) references usertbl(userid); -- 외래 키 제약조건

-- 외래 키 제약 조건 확인
show index from buytbl;


외래 키 제거, 업데이트 옵션

외래 키 옵션 중에 on delete cascade와 on update cascade가 있는데 이는 기준 테이블의 데이터가 변경되었을 때 외래 키 테이블도 자동으로 적용되도록 설정해 준다. 예를 들어, on update cascade를 이용하면 구매 테이블의 기준 테이블인 회원 테이블에서 김법수의 아이디 kbs가 kim으로 변경되었다면 연결되있는 구매 테이블의 foreign key인 userid도 kim으로 변경되게 된다.

alter table buytbl
  drop foreign key fk_usertbl_buytbl ; -- 외래 키 이름을 이용해서 외래 키 제거

alter table buytbl
  add constraint fk_usertbl_buytbl 
  foreign key (userid) references usertbl(userid)
  on update cascade; -- 기준 테이블의 업데이트에 따라 자동으로 외래 키 테이블도 정보 수정
  on delete cascade; -- 기준 테이블 제거시 자동으로 외래 키 테이블 정보도 제거


6. unique 제약 조건


중복되지 않는 유일한 값을 입력해야 하는 조건이다. primary key와 거의 비슷하며 차이점은 unique는 null값을 허용한다는 점이다. 예를 들면 회원 테이블에서 주로 email 주소를 unique로 설정하는 경우가 많다.

-- 기본 예시
create table usertbl( email char(30) null unique);

-- 다른 조건들과 마찬가지로 이름을 만들고 나중에 작성할 수도 있다.
-- 형식
constraint 만들이름 unique (열이름) 

-- 예시
create table usertbl
(
  email char(30) null,
  constraint ak_email unique (email)
)


7. check 제약 조건


check 제약 조건은 입력되는 데이터를 점검하는 기능을 한다. 예를 들면 height 값에 마이너스 값이 없게 한다든지, 출생년도가 1900년도 이후이고 현재 시점 이전이어야 한다든지 등의 조건을 지정한다.

create table usertbl 
(
  -- 후에 insert 할 때 해당 범위 내에 없다면 오류가 발생
  birthyear int check(birthyear >=1900 and birthyear <=2023),
  name varchar(10),
  -- 다른 제약조건과 마찬가지로 후에 이름 만들고 설정
  constraint ck_name check (name is not null) -- name이 null이면 오류 발생
  mobile1 char(3)
);
-- 만들고 난 후에 설정
alter table usertbl
  -- add constraint 만들이름 check(조건)
  add constraint ck_mobile1 check (mobile1 in ('010','011','016','019'));


8. default 정의


dafault는 값을 입력하지 않았을 때, 자동으로 입력되는 기본 값을 정의하는 방법이다. 예를 들어, 출생연도를 입력하지 않으면 -1을 입력하고, 주소를 특별히 입력하지 않았다먼 서울이 되도록 정의할 수 있다.
대부분의 제약조건이 따로 걸 경우 add constraint로 시작하는데 default의 경우 alter column으로 시작한다.

create table usertbl
(
  birthyear int not null default -1,
  height smallint null dafault 170,
  addr char(2) not null
);
alter table usertbl
  -- alter column 열이름 set default 고정값
  alter column addr set default '서울';

-- default값 insert에서 활용
insert into usertbl values(default,default,default);


cf) null값 허용
계속 사용했었는데 간단히 집고 넘어가자. null값을 허용하려면 null, 허용하지 않으려면 not null을 사용하면 된다. primary key가 설정된 열에는 null값이 있을 수 없으므로 생략하면 자동으로 not null이 인식된다.
null 값은 아무 것도 없다는 의미로 공백이나 0과 같은 값과는 다르다는 점을 주의하자.



본 포스팅은 ‘이것이 MySQL이다’를 읽고 공부한 내용을 바탕으로 작성하였습니다.


© 2021. By Backtony