'외래키'에 해당되는 글 2건

mysql> create table DEPARTMENT (
    -> DEPT_CD varchar(10) not null,
    -> NAME varchar(30) not null,
    -> PHONE int(11) not null,
    -> primary key(DEPT_CD)
    -> );

desc RESERVATION;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| RESERVATION_ID | varchar(10) | NO   | PRI | NULL    |       |
| PATIENT        | int(13)     | NO   | MUL | NULL    |       |
| DEPARTMENT     | varchar(10) | NO   | MUL | NULL    |       |
| DOCTOR         | varchar(5)  | NO   | MUL | NULL    |       |
| DATE           | varchar(10) | NO   |     | NULL    |       |
| TIME           | varchar(10) | NO   |     | NULL    |       |
| TYPE           | varchar(20) | NO   |     | NULL    |       |
| GRANTED        | varchar(10) | NO   |     | NO      |       |
+----------------+-------------+------+-----+---------+-------+

mysql> alter table RESERVATION drop column DEPARTMENT;
ERROR 1025 (HY000): Error on rename of '.\db_project\#sql-d0_2' to '.\db_project\reservation' (errno: 150)

mysql> alter table RESERVATION drop foreign key DEPARTMENT;
ERROR 1025 (HY000): Error on rename of '.\db_project\#sql-d0_2' to '.\db_project\reservation' (errno: 152)

mysql> SHOW INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
091130 21:10:55 Error in foreign key constraint of table db_project/reservation:

there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT "reservation_ibfk_2" FOREIGN KEY ("DEPARTMENT") REFERENCES "departm
ent" ("DEPT_CD") ON DELETE CASCADE

mysql> alter table reservation drop foreign key reservation_ibfk_2;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table reservation drop column DEPARTMENT;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE user ADD CONSTRAINT
       -> h_id FOREIGN KEY(h_id)
       -> REFERENCES home(home_id) ON DELETE CASCADE ON UPDATE CASCADE;