0

I'm making a new database in MySQL in which after making the Department table I receive an error when adding other tables.

CREATE TABLE Department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key(dept_name));

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);

CREATE TABLE Instructor (ID varchar (5),name varchar (20) not null,dept_name varchar (20),salary numeric (8,2),primary key (ID),
foreign key (dept_name) references Department);

I had a look at Error 1239: Incorrect foreign key definition but it didn't seem to help.

2

You need to add column name along with table name.

  CREATE TABLE Department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key(dept_name));

        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));

        CREATE TABLE Instructor (ID varchar (5),name varchar (20) not null,dept_name varchar (20),salary numeric (8,2),primary key (ID),
        foreign key (dept_name) references Department(dept_name));
  • odd my 6th edtion book database concepts didn't type it that way. i put it in line per line ....ughh. anyhow it worked Thanks. – Chaos Order Mar 10 '19 at 8:09
  • Yes, referencing only the table is valid SQL syntax (when you want to reference the PK) but MYSQL hasn't implemented it yet. – ypercubeᵀᴹ Mar 10 '19 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.