CODEWITHSUNDEEP
mysqlsql
user5381640
user5381640

Reputation:

Not sure whats wrong with my SQL?

I'm only just starting out in SQL land and am trying to figure out what I am doing wrong with one of my tables. I have as followed 

CREATE TABLE Winemaker ( 
Winemaker_id varchar (20) NOT NULL, 
Winemaker_name varchar (20) NOT NULL, 
key (Winemaker_Id)); 

CREATE TABLE Wine ( 
Wine_Id varchar (20) NOT NULL, 
Wine_name varchar (20) NOT NULL, 
Winemaker_Id varchar (4) NOT NULL, 
Wine_vintage varchar (8) NOT NULL, 
Wine_price varchar (8) NOT NULL 
Primary key (Wine_Id) 
Foreign key (Winemaker_Id) REFERENCES Winemaker(Id));

I am not quite sure what I've done wrong. If anyone could help that would be great, thanks!

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270201

You have several issues, mostly around the definition of the foreign key reference. Try this:

CREATE TABLE Winemaker ( 
    Winemaker_id varchar(20) NOT NULL, 
    Winemaker_name varchar(20) NOT NULL, 
    primary key (Winemaker_Id)
); 

CREATE TABLE Wine ( 
    Wine_Id varchar(20) NOT NULL, 
    Wine_name varchar(20) NOT NULL, 
    Winemaker_Id varchar(20) NOT NULL, 
    Wine_vintage varchar(8) NOT NULL, 
    Wine_price varchar (8) NOT NULL,
    Primary key (Wine_Id),
    Foreign key (Winemaker_Id) REFERENCES Winemaker(Winemaker_id)
);

SQL Fiddle is here.

Notes:

  • You need to define a primary key (or at least unique key) on Winemaker to have a foreign key reference to it.
  • The data type of the column in the referencing table needs to exactly match the data type in the referenced table.
  • You had missing commas.
  • You had extra closing parens and semicolons.
  • Also, spaces between varchar and ( are allowed, but they look awkward.

Upvotes: 1

Related Questions