[2주차] 24.01.17 수요일

SQL

 

-- 테이블 레벨의 제약조건. 두 컬럼을 묶어서 unique 걸기 (composite unique)
CREATE TABLE user_tbl(
   user_id     VARCHAR(50) NOT NULL,
   NAME        VARCHAR(50),
   birthYear   DATE,
   address     VARCHAR(100),
   mobile      CHAR(13),
   height      SMALLINT,
   regdate     DATE,
   CONSTRAINT  mobile_height_un UNIQUE (mobile, height)
);

-- composite primary key
CREATE TABLE user_tbl(
   user_id     VARCHAR(50),
   NAME        VARCHAR(50),
   birthYear   DATE,
   address     VARCHAR(100),
   mobile      CHAR(13),
   height      SMALLINT,
   regdate     DATE,
   CONSTRAINT  user_id_pk PRIMARY KEY (user_id, NAME)
);

SHOW INDEX FROM user_tbl; -- user_tbl의 모든 제약조건을 확인하는 구문

-- 컬럼 레벨에서 제약조건 주는 구문
CREATE TABLE employee_tbl(
	emp_id		VARCHAR(10) PRIMARY KEY,
	emp_name	VARCHAR(10),
	dept_id		VARCHAR(10) REFERENCES department_tbl (dept_id),
	job_id		VARCHAR(10) REFERENCES job_tbl(job_id),
);

-- 테이블 레벨에서 제약조건 걸기
CREATE TABLE employee_tbl(
	emp_id		VARCHAR(10),
	emp_name	VARCHAR(10),
	dept_id		VARCHAR(10),
	job_id		VARCHAR(10),
	CONSTRAINT	employee_tbl_emp_id_pk PRIMARY KEY (emp_id),
	CONSTRAINT	employee_tbl_dept_id_fk FOREIGN KEY (dept_id) REFERENCES department_tbl (dept_id),
	CONSTRAINT	employee_tbl_job_id_fk FOREIGN KEY (job_id) REFERENCES job_tbl (job_id),
);