Challenge 3 of 3: FOREIGN KEY Constraint.

You don't need the FOREIGN KEY on FAVORITE_ROCK_ID. Remove that and you are good.

CREATE TABLE MEMBERS (ID PRIMARY KEY, FIRST_NAME, LAST_NAME, FAVORITE_ROCK_ID REFERENCES ROCKS(ID));

John, your code wouldn't work because the Foreign Key syntax isn't correct, at least according to SQL playground.

This should technically work, but it seems this challenge is buggy as the first one. I tried all lower case which was mostly the trick for the first one, didn't work. This is a version with keywords uppercase, everything else lower case, still doesn't work.

CREATE TABLE members (
    id PRIMARY KEY, 
    first_name, 
    last_name, 
    favorite_rock_id REFERENCES rocks(id)
);

The error message is this in all cases so far:

Bummer! Make sure you're creating all 4 columns: id, first_name, last_name, and favorite_rock_id.

And yeah, I copy pasted the names from the message for my column names :).

EDIT: The above code just passed after restarting the challenge and going through the tasks 1 and 2 again. So I guess they fixed it now, or it simply just started working.

They want you to add the keyword integer, work that in where it is appropriate

DROP TABLE MEMBERS; ( id PRIMARY KEY, first_name, last_name, favorite_rock_id REFERENCES rocks(id) );

The code I pasted always works for me.

Here is a hack to get you through the challenge. Let me know if it works for you.

create table rocks(id primary key, name, type, color);