I have read Database system concepts, 6th edition, Silberschatz. I’m going to implement the university database system shown in chapter 2 on OS X on MySQL. But I have a trouble with creating the table course
. the table department
looks like
mysql> select * from department -> ; +------------+----------+-----------+ | dept_name | building | budget | +------------+----------+-----------+ | Biology | Watson | 90000.00 | | Comp. Sci. | Taylor | 100000.00 | | Elec. Eng. | Taylor | 85000.00 | | Finance | Painter | 120000.00 | | History | Painter | 50000.00 | | Music | Packard | 80000.00 | | Physics | Watson | 70000.00 | +------------+----------+-----------+ mysql> show columns from department -> ; +-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | dept_name | varchar(20) | NO | PRI | | | | building | varchar(15) | YES | | NULL | | | budget | decimal(12,2) | YES | | NULL | | +-----------+---------------+------+-----+---------+-------+
Creating the table course
causes the following error.
mysql> create table course -> (course_id varchar(7), -> title varchar (50), -> dept_name varchar(20), -> credits numeric(2,0), -> primary key(course_id), -> foreign key (dept_name) references department); ERROR 1215 (HY000): Cannot add foreign key constraint
after searching google for foreign key constraint, I have just learned that the word ‘foreign key constraint’ indicates that data from foreign key column in the table course
must exist in primary key column in the table department
. But I should have met this error when inserting data.
If not, why does author make me execute that SQL statement?
If I really execute erroneous SQL statement, Does I have to designate dept_name
in course table as foreign key after inserting some data?
EDIT : typing set foreign_key_checks=0
into mysql>
does not fix the error.
------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2013-09-21 16:02:20 132cbe000 Error in foreign key constraint of table university/course: foreign key (dept_name) references department): Syntax error close to: ) mysql> set foreign_key_checks=0 -> ; Query OK, 0 rows affected (0.00 sec) mysql> create table course -> (course_id varchar(7), -> title varchar(50), -> dept_name varchar(20), -> credits numeric(2,0), -> primary key(course_id), -> foreign key (dept_name) references department); ERROR 1215 (HY000): Cannot add foreign key constraint
Advertisement
Answer
The syntax of FOREIGN KEY
for CREATE TABLE
is structured as follows:
FOREIGN KEY (index_col_name) REFERENCES table_name (index_col_name,...)
So your MySQL DDL should be:
create table course ( course_id varchar(7), title varchar(50), dept_name varchar(20), credits numeric(2 , 0 ), primary key (course_id), FOREIGN KEY (dept_name) REFERENCES department (dept_name) );
Also, in the department
table dept_name
should be VARCHAR(20)
More information can be found in the MySQL documentation