debugsqlMinor
Error Code: 1239. Incorrect foreign key definition for 'foreign key without name': reference and table don't match
Viewed 0 times
definitionwithouterrorincorrectreferenceandforeignmatch1239name
Problem
I'm making a new database in MySQL in which after making the Department table I receive an error when adding other tables.
I had a look at Error 1239: Incorrect foreign key definition but it didn't seem to help.
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.
Solution
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));Code Snippets
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));Context
StackExchange Database Administrators Q#231770, answer score: 3
Revisions (0)
No revisions yet.