테이블에 데이터를 입력할때는 데이터가 고유한 데이터일 수도 있고 빈칸으로 둬선 안되는 데이터도 있다
다른 테이블과 이어지는 데이터도 있는데 이러한 제약조건에 대해 알아보자
외래 키
다른 테이블로 연결 시켜준다
create TABLE NEW_EMP2 (
NO DECIMAL(4) PRIMARY KEY, # PK 는 NN 이면서 UK
NAME VARCHAR(20) NOT NULL,
JUMIN VARCHAR(13) NOT NULL UNIQUE,
LOC_CODE DECIMAL(1),
DEPTNO VARCHAR(6),
CONSTRAINT FK_NEW_EMP2_DEPTNO foreign key (DEPTNO) references DEPT2 (DCODE)
);
CONSTRAINT
제약조건이름(any) foreign key
(컬럼) references
테이블(other) (컬럼)>의 구조를 가진다ADD CONSTRAINT
NEW_테이블_컬럼_UK UNIQUE
(컬럼)>ALTER TABLE NEW_EMP2
ADD CONSTRAINT NEW_EMP2_NAME_UK UNIQUE (NAME);
# NEW_EMP2_NAME_UK - 제약 조건 이름
select * from information_schema.table_constraints;
mysql> select * from information_schema.table_constraints;
+--------------------+--------------------+-----------------+--------------------+-------------------------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+--------------------+-----------------+--------------------+-------------------------------+-----------------+----------+
| def | cal_history | PRIMARY | cal_history | cal_history | PRIMARY KEY | YES |
| def | cal_history | username_2 | cal_history | cal_history | UNIQUE | YES |
| def | cal_history | PRIMARY | cal_history | user | PRIMARY KEY | YES |
| def | cal_history | username | cal_history | user | UNIQUE | YES |
| def | cal_history | username_2 | cal_history | user | UNIQUE | YES |
| def | performance_schema | PRIMARY | performance_schema | global_status | PRIMARY KEY | YES |
| def | performance_schema | PRIMARY | performance_schema | global_variables | PRIMARY KEY | YES |
| def | performance_schema | PRIMARY | performance_schema | persisted_variables | PRIMARY KEY | YES |
| def | performance_schema | PRIMARY | performance_schema | processlist | PRIMARY KEY | YES |
| def | performance_schema | PRIMARY | performance_schema | session_account_connect_attrs | PRIMARY KEY | YES |
| def | performance_schema | PRIMARY | performance_schema | session_status | PRIMARY KEY | YES |
| def | performance_schema | PRIMARY | performance_schema | session_variables | PRIMARY KEY | YES |
+--------------------+--------------------+-----------------+--------------------+-------------------------------+-----------------+----------+
12 rows in set (0.01 sec)
ALTER TABLE [테이블명] DROP CONSTRAINT [제약조건이름];
mysql> alter table cal_history drop constraint username_2;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc cal_history;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| data | varchar(20) | NO | | NULL | |
| created_at | datetime | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
삭제후 username
에 unique 제약조건 사라짐
다른 제약 조건 수정은 ALTER 명령어로 가능함