[SQL] MYSQL Workbench 테이블 생성, 제약조건 종류

✍️ Study Note

 

 

 

---

 

1. MYSQL Workbench 테이블생성

(1) 테이블 생성

1. TABLES 오른쪽 마우스 클릭 후, CREATE TABLE 선택
2. TABLE 이름, COLUMN 이름, 데이터타입 작성
3. PK, NN, UQ 체크 - APPLY

 

#예시1

CREATE TABLE `tabledb`.`usertbl` (
  `USERID` CHAR(8) NOT NULL,
  `NAME` VARCHAR(10) NOT NULL,
  `BIRTHYEAR` INT NOT NULL,
  `ADDR` CHAR(2) NOT NULL,
  `MOBILE1` CHAR(3) NULL,
  `MOBILE2` CHAR(8) NULL,
  `HEIGHT` DATE NULL,
  PRIMARY KEY (`USERID`));

 

#예시2

 

CREATE TABLE `tabledb`.`buytbl` (
  `NUM` INT NOT NULL AUTO_INCREMENT,
  `USERID` CHAR(8) NULL,
  `PRODNAME` CHAR(6) NULL,
  `GROUPNAME` CHAR(4) NULL,
  `PRICE` INT NULL,
  `AMOUNT` SMALLINT NULL,
  PRIMARY KEY (`NUM`),
  FOREIGN KEY (USERID) REFERENCE USERTBL(USERID)
 );

 

 

(2) 데이터 간편입력

1. 테이블 오른쪽 마우스 - 'SELECT ROWS - LIMIT 1000' 클릭
2. 하단 RESULT GRID 창의 INSERT NEW ROW 아이콘 클릭
3. 결과 테이블에서 바로 데이터 입력 가능

 

 

 

 

---

 

 

2. 제약조건 

(1) 제약조건 의의

• 데이터 무결성을 지키기 위한 제한된 조건. 
• 해당 조건을 만족 했을 때 데이터가 입력됨

 

(2) 제약조건 종류

① PRIMARY KEY

• 중복 불가, NULL 불가
• 자동 클러스터형 인덱스 생성
• PRIMARY KEY (열이름)
• CONSTRAINT PRIMARY KEY 제약조건이름 (열이름)
• ALTER TABLE 테이블 이름 ADD CONTRAINT 제약조건이름 PRIMARY KEY(열이름)

-- PRIMARY KEY 생성 종류
-- #1 PRIMARY KEY
CREATE TABLE USERTBL
(USERID CHAR(8) NOT NULL PRIMARY KEY,
 USERNAME VARCHAR(10) NOT NULL ,
 BIRTHYEAR INT NOT NULL
 );
 
 -- #2 PRIMARY KEY(열이름)
CREATE TABLE USERTBL
(USERID CHAR(8) NOT NULL,
 USERNAME VARCHAR(10) NOT NULL ,
 BIRTHYEAR INT NOT NULL,
 PRIMARY KEY (USERID) 
 );

 -- #3 CONTRAINT 제약조건이름 PRIMARY KEY (열이름)
CREATE TABLE USERTBL
(USERID CHAR(8) NOT NULL,
 USERNAME VARCHAR(10) NOT NULL ,
 BIRTHYEAR INT NOT NULL,
 CONSTRAINT PK_USERTBL_USERID PRIMARY KEY (USERID) 
 );
 
 -- #4 ALTER TABLE 테이블 이름 ADD CONTRAINT 제약조건이름 PRIMARY KEY(열이름)
 ALTER TABLE USERTBL 
	ADD CONSTRAINT PK_USERTBL_USERID
		PRIMARY KEY (USRID);

 

② FOREIGN KEY 

• 하나의 테이블이 다른 테이블에 의존하게 됨.
• 기준 테이블이 'PRIMARY KEY', 'UNIQUE'여야 외래키 관계 성립.
• 참조테이블의 열이름 vs FOREIGN KEY 열이름 달라도 괜찮음.
• FOREIGH KEY (열이름) REFERENCES 참조테이블 (열이름)
• CONSTRAINT 제약조건이름 FOREIGN KEY (열이름) REFERENCE 참조테이블 (열이름)
• ALTER TABLE 테이블이름 ADD CONTRAINT 제약조건이름 FOREIGN KEY(열이름) REFERENCES 기준테이블(열이름)
• ON UPDATE CASCADE, ON DELETE CASCADE : 기준 테이블의 데이터 변경 시, 외래키 테이블도 자동 적용.

-- FOREIGN KEY 생성 종류
-- #1 FOREIGN KEY(열이름) REFERENCES 가져올 테이블(열이름)
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) );
 
 -- #2 CONTRAINT 제약조건이름 FOREIGN KEY(열이름) REFERENCES 가져올 테이블(열이름)
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) );
  
  -- #3 ALTER TABLE 테이블이름 ADD CONTRAINT 제약조건이름 FOREIGN KEY(열이름) REFERENCES 기준테이블(열이름)
ALTER TABLE BUYTBL
ADD CONSTRAINT FK_USERTBL_BUYTBL
    FOREIGN KEY (USRID)
    REFERENCES USERTBL (USERID);

 

 

③ UNIQUE

• 중복되지 않는 유일한 값 입력 필요
• PRIMARY KEY와 다르게 NULL 가능
• CONSTRAINT 제약조건이름 UNIQU (열이름)

-- #1 UNIQUE
CREATE TABLE USERTBL
(USERID CHAR(8) NOT NULL PRIMARY KEY,
 USERNAME VARCHAR(10) NOT NULL ,
 BIRTHYEAR INT NOT NULL,
 EMAIL CHAR(2) NOT NULL UNIQUE);

-- #2 CONSTRAINT 제약조건이름 UNIQUE(열이름)
CREATE TABLE USERTBL
(USERID CHAR(8) NOT NULL PRIMARY KEY,
 USERNAME VARCHAR(10) NOT NULL ,
 BIRTHYEAR INT NOT NULL,
 EMAIL CHAR(2) NOT NULL,
 CONSTRAINT AK_EMAIL UNIQUE (EMAIL));

 

 

④ CHECK

입력되는 데이터를 점검하는 기능 (EX. 마이너스값 입력불가, 1900년 이후 출생년도 등)

 -- #1 
 -- CHECK (조건)
 -- CONSTRATIN 제약조건이름 CHECK (조건)
 CREATE TABLE USERTBL
 (USERID CHAR(8) PRIMARY KEY,
  NAME VARCHAR(10), 
  BIRTHYEAR INT CHECK (BIRTHYEAR >=1900 AND BIRTHYEAR <=2023),
  MOBILE1 CHAR(3) NULL,
  CONSTRAINT CK_NAME CHECK (NAME IS NOT NULL));
  
  -- #2 ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름 CHECK (조건)
  ALTER TABLE USERTBL
  ADD CONSTRAINT CK_MOBILE1
  CHECK (MOBILE1 IN ('010','011','016','017'));

 

 

⑤ DEFAULT

값이 입력되지 않을 때, 자동으로 입력되는 기본 값.

  -- #1 DEFAULT 값
  CREATE TABLE USERTBL2
  (USERID CHAR(8) NOT NULL PRIMARY KEY,
   USERNAME VARCHAR(10) NOT NULL, 
   BIRTHYEAR INT NOT NULL DEFAULT -1,
   HEIGHT SMALLINT NOT NULL DEFAULT 170);
   
   -- #2 ALTER TABLE 테이블이름 ALTER COLUMN 열이름 SET DETAUL 값
   ALTER TABLE USERTBL ALTER COLUMN MOBILE1 SET DEFAULT -1;

 

 

⑥ NULL, NOT NULL

• NULL 허용, 비허용
• 공백, 0과는 다른 값

 

 

(3) 기타 제약조건 명령어

• 테이블의 제약조건 조회

-- #1 
DESCRIBE 테이블

-- #2
SHOW INDEX FROM BUYTBL;

 

• 제약조건 삭제

DROP FOREIGN KEY FK_USERTBL_BUYTBL;