Having issues creating foreign keys

Question

I have a table called member:

create table member(id int NOT NULL auto_increment PRIMARY KEY, name varchar(255) NOt NULL, email varchar(255) NOT NULL, userName varchar(255) 
NOT NULL, password varchar(255) NOT NULL, handicap int);

and trying to create a table stableford which will have a foreign key name from the member table:

create table stableford(id int NOT NULL auto_increment PRIMARY KEY, title varchar(255) NOT NULL, player_name varchar(255) NOT NULL, score int NOT NULL, INDEX(player_name), FOREIGN KEY(player_name) REFERENCES member(name));

Name of database is golfclub

I get an error cant create table 'golfclub.#sql-d1c_6' (errno:150)

Answer 1

Use the id on the member table as the foreign key and would be better to change it to something like membber_id. Here are references to help: W3Schools foreign key constraint

Answer 2

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

The error you are getting is because of following reasons:

1. The column name of table member you have referenced is not a primary key. So for adding a foreign key you have to reference the primary key of the referenced table which is id in this case. Go through this the documentation.
2. Syntax Error: You are missing the closing ) of second create statement.