HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

Error Code: 1239. Incorrect foreign key definition for 'foreign key without name': reference and table don't match

Submitted by: @import:stackexchange-dba··
0
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.

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.