mySQL: Error when attempting to create a Foreign Key

Question

I am trying to create two tables that share a common column. These tables are currently empty as I would like to add the data after all are linked appropriately. I can't seem to get to the bottom of the syntax error. I can create the table 'publisher' with no issues but can't reference it's primary key 'publishername' as a foreign key to the next table.

I get the dreaded E

RROR 1064 "near 'references publisher(publishername))' at line 1".

I'm lost because I've used a similar syntax previously to do this. Much thanks in advance!

create table publisher (
 publishername varchar(30) primary key, 
 city varchar(15),
 country varchar(10), 
 telephone varchar(15), 
 yearfounded int(4));

create table book (
booknumber int(3) primary key, 
bookname varchar(50), 
publicationyear int(4), 
pages int(4), 
publishername varchar references publisher(publishername));

This is a bad design due to a publisher name changing. It ought to be a thin auto_increment INT or similar, unique — Drew, Oct 08 '16 at 20:19
This problem is referencing error 1064 but it is more of a design issue. Thus the dupe hammer close target. — Drew, Oct 08 '16 at 23:15

asfandahmed1 · Accepted Answer · 2016-10-08T19:20:22.220

0

It seems like you didn't give length of the foreign key. This works fine . You can check it here.

create table publisher (
 publishername varchar(30) primary key, 
 city varchar(15),
 country varchar(10), 
 telephone varchar(15), 
 yearfounded int(4));

create table book (
booknumber int(3) primary key, 
bookname varchar(50), 
publicationyear int(4), 
pages int(4), 
publishername varchar(30) references publisher(publishername));

edited Oct 08 '16 at 19:20

answered Oct 08 '16 at 19:17

asfandahmed1

462
5
23

Oh wow. I can't believe I didn't see that. For some reason, I thought referencing the foreign key to the primary key would automatically inherit the length. Thanks for clearing up that misunderstanding! – W.B Oct 08 '16 at 19:19
No problem @W.B :) – asfandahmed1 Oct 08 '16 at 19:21

mySQL: Error when attempting to create a Foreign Key

1 Answers1