MySQL : 내가 생성한 제약조건 확인 및 추가/삭제하기 제약조건 확인하기 select * frominformation_schema.table_constraints; 제약조건 삭제하기 ALTER TABLE [테이블명] DROP CONSTRAINT [제약조건이름]; 만약 위의 구문으로 Error code 1064: You have an error in your SQL syntax; 가 나왔다면, 외래키 제약조건일 확률이 높다 ALTER TABLE [테이블명] DROP FOREIGN KEY [제약조건이름]; 제약조건 추가하기 외래키 : ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건이름] FOREIGN KEY(컬럼명) REFERENCES [부모테이블명](PK컬럼명) [ON DELETE CASCADE / ON UPDATE CASCADE]; 기본키 : ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건이름] PRIMARY KEY(컬럼명); NOT NULL 제약조건 추가 ALTER TABLE [테이블명] MODIFY [컬럼명] [데이터타입] CONSTRAINT [제약조건이름] NOT NULL; 제약조건 이름 파악하기 테이블의 제약조건을 확인 해 주는 기능은 information_schema 데이터베이스에 있는 table_constraints이다. 따라서 mysql의 사용자는 각자 자신만의 데이터베이스를 사용중이므로, 해당 기능을 사용하고 싶다면 information_schema.table_constraints 를 꼭 입력해야 한다. mysql> desc information_schema.table_constraints; +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | CONSTRAINT_CATALOG | varchar(512) | NO | | | | | CONSTRAINT_SCHEMA | varchar(64) | NO | | | | | CONSTRAINT_NAME | varchar(64) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | CONSTRAINT_TYPE | varchar(64) | NO | | | | +--------------------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) 저번 포스팅때 생성한 외래키 제약조건의 이름을 아래처럼 확인 할 수 있다. mysql> select * from information_schema.table_constraints; +--------------------+-------------------+------------------------+--------------+-----------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+------------------------+--------------+-----------------+-----------------+ | def | geoseong | bucketlist_info_ibfk_1 | geoseong | bucketlist_info | FOREIGN KEY | | def | geoseong | PRIMARY | geoseong | increment | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | member_test | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | pjtmake | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | study | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | study1 | PRIMARY KEY | +--------------------+-------------------+------------------------+--------------+-----------------+-----------------+ 6 rows in set (0.14 sec) 제약조건 삭제하기 mysql> ALTER TABLE bucketlist_info DROP CONSTRAINT bucketlist_info_ibfk_1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONST RAINT bucketlist_info_ibfk_1' at line 1 문법에는 하자가 없고 오타도 없는데 이렇게 에러가 뜬다면, 이 제약조건은 분명 외래키 제약조건일 것이다.. 외래키 제약조건은 DROP FOREIGN KEY 문을 사용하여 제거한다. mysql> ALTER TABLE bucketlist_info DROP FOREIGN KEYbucketlist_info_ibfk_1; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.table_constraints; +--------------------+-------------------+-----------------+--------------+-------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+-----------------+--------------+-------------+-----------------+ | def | geoseong | PRIMARY | geoseong | increment | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | member_test | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | pjtmake | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | study | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | study1 | PRIMARY KEY | +--------------------+-------------------+-----------------+--------------+-------------+-----------------+ 5 rows in set (0.01 sec) 제약조건 추가하기 * 연계참조 무결성제약조건 ON UPDATE CASCADE : 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키 값이 포함된 행에서 키 값을 업데이트하려고 하면 해당 외래 키를 구성하는 모든 값도 키에 지정된 새 값으로 업데이트되도록 지정 ON DELETE CASCADE : 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 외래 키가 포함되어 있는 모든 행도 삭제되도록 지정 mysql> ALTER TABLE bucketlist_info ADD CONSTRAINT bucketlist_info_ibfk_1 FOREIGN KEY(userid) -> REFERENCES member_test(userid) ON UPDATE CASCADE ON DELETE CASCADE; Query OK, 5 rows affected (0.13 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.table_constraints; +--------------------+-------------------+------------------------+------------- -+-----------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+------------------------+------------- -+-----------------+-----------------+ | def | geoseong | bucketlist_info_ibfk_1 | geoseong | bucketlist_info | FOREIGN KEY | | def | geoseong | PRIMARY | geoseong | increment | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | member_test | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | pjtmake | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | study | PRIMARY KEY | | def | geoseong | PRIMARY | geoseong | study1 | PRIMARY KEY | +--------------------+-------------------+------------------------+------------- -+-----------------+-----------------+ 6 rows in set (0.01 sec) mysql> SELECT * FROM MEMBER_TEST; +----------+------+------+---------------+----------------+------------+-------- --------+ | userid | name | pwd | phone | email | regdate | created context | +----------+------+------+---------------+----------------+------------+-------- --------+ | geoseong | 거셩 | 1234 | 010-1234-5678 | | 2016-08-04 | NULL | | imf4 | 거셩 | 1234 | 010-1234-5678 | | 2016-08-04 | NULL | +----------+------+------+---------------+----------------+------------+-------- --------+ 2 rows in set (0.00 sec) mysql> select * from bucketlist_info; +----------+-----------------+--------------+---------------+ | userid | bucketlist_name | wishedu_info | wishlife_info | +----------+-----------------+--------------+---------------+ | imf4 | 버켓리스트 | 1 | 2 | | imf4 | 버켓리스트2 | 10 | 20 | | imf4 | 버켓리스트3 | 30 | 40 | | geoseong | 버켓리스트3 | 30 | 40 | | geoseong | NULL | NULL | NULL | +----------+-----------------+--------------+---------------+ 5 rows in set (0.00 sec) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ON DELETE CASCADE 테스트 mysql> delete from member_test where userid='geoseong'; Query OK, 1 row affected (0.01 sec) mysql> select * from bucketlist_info; +--------+-----------------+--------------+---------------+ | userid | bucketlist_name | wishedu_info | wishlife_info | +--------+-----------------+--------------+---------------+ | imf4 | 버켓리스트 | 1 | 2 | | imf4 | 버켓리스트2 | 10 | 20 | | imf4 | 버켓리스트3 | 30 | 40 | +--------+-----------------+--------------+---------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM MEMBER_TEST; +--------+------+------+---------------+----------------+------------+----------------+ | userid | name | pwd | phone | email | regdate | createdcontext | +--------+------+------+---------------+----------------+------------+----------------+ | imf4 | 거셩 | 1234 | 010-1234-5678 | | 2016-08-04 | NULL | +--------+------+------+---------------+----------------+------------+----------------+ 1 row in set (0.00 sec) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ON UPDATE CASCADE 테스트 mysql> update member_test set userid='imf444'; Query OK, 1 row affected (0.34 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from bucketlist_info; +--------+-----------------+--------------+---------------+ | userid | bucketlist_name | wishedu_info | wishlife_info | +--------+-----------------+--------------+---------------+ | imf444 | 버켓리스트 | 1 | 2 | | imf444 | 버켓리스트2 | 10 | 20 | | imf444 | 버켓리스트3 | 30 | 40 | +--------+-----------------+--------------+---------------+ 3 rows in set (0.00 sec) |