Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
alm
4,268 PointsSQL Error, but works in the playground as expected, what I am missing/overlooking
The challenge requests the following: Can you create a table named ROCKS with the following columns: ID, NAME, TYPE, COLOR. And make sure that ID is a primary key.
I've entered the following code:
CREATE TABLE ROCKS ( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME VARCHAR (255), TYPE VARCHAR (255), COLOR VARCHAR (255) ); INSERT INTO ROCKS VALUES (Null, 'BIG', 'TYPE1','BROWN'); INSERT INTO ROCKS VALUES (Null, 'BIGGER', 'TYPEB','BLUE');
But when I go to check my work it says: Bummer! SQL Error: PRIMARY KEY must be unique. I thought maybe table ROCKS already exists and added a DROP statement before the CREATE, but then I get an error to start with the CREATE. I've tried leaving the INSERT INTO off and I continue to get the PRIMARY KEY must be unique. I even tried to adding PRAGMA foreign_keys = ON; before the CREATE and once again get I need to start with CREATE. This code works as in SQL Playground with no error, and the table looks as expected, so perhaps I'm misinterpreting the ask. Thanks for your help!
4 Answers
Steven Parker
231,269 PointsYou only need to create the table, you will not need to insert any rows.
But another student already posted about a problem with this challenge. I think there may be a bug in it and suggested they report it to the staff. Hopefully it will be fixed soon.
alm
4,268 PointsThanks! Glad to read it's not just me. I too had tried without the insert rows to no avail. In fact, I had added them in an attempt to get past the issue.
ixioihjfjo
16,195 PointsI reported it and they said they ran it through with the correct answers and it worked properly. Their suggestion was to post it here and see if anyone can help. My query is the following:
CREATE TABLE ROCKS ( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME VARCHAR(255), TYPE VARCHAR(255), COLOR VARCHAR (255) );
SQL Playground lets it go through and works properly. ¯_(ツ)_/¯
Steven Parker
231,269 PointsI'm not convinced. Four people have already posted about this exact issue, and I can't pass this challenge myself. I'm going to try tagging the instructor Ben Deitch here and see if he might be willing to look into this himself.
Another red flag for me is the error it produces: "Bummer! SQL Error: PRIMARY KEY must be unique". I don't think you can get that error from a CREATE TABLE statement. But it might be something caused by a validation process that attempts to insert duplicate keys for testing (but that would indicate a good table). I also managed to get past task 1 once somehow, but then I ran into the exact same issue on task 3.
Steven Parker
231,269 PointsOkay, here's the scoop. This challenge works when you omit column datatypes in a CREATE. None of the databases I use professionally allow this, but SQLite does. SQLite doesn't actually use datatypes, which was covered in the first video of this course. But it normally treats them as a notation and does not cause an error.
I still think it's a bug in the course, particularly since it worked in earlier challenges to supply the datatypes, but you can get around it here by leaving them off.