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

Databases

Boban Talevski
Boban Talevski
24,793 Points
Posted by Boban Talevski
Boban Talevski
24,793 Points

Bug in the challenge or am I missing something?

https://teamtreehouse.com/library/creating-and-modifying-database-tables/creating-and-modifying-database-tables/making-changes-to-a-database

Challenge Task 1 of 3 In the last challenge we created a table for the members of our rock collecting club. Now, we'd like to add a ROCKS table to store information about our favorite rocks. 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'm putting

CREATE TABLE ROCKS (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  NAME VARCHAR(255),
  TYPE VARCHAR(255),
  COLOR VARCHAR(255)
);

And getting "SQL Error: PRIMARY KEY must be unique".

I've tried some other variations like adding UNIQUE constraint to the ID column or removing the types from the other columns, but nothing seems to work. On some of these attempts, I also got an error like table rocks doesn't exist or something.

Am i missing something obvious or is there a bug in this challenge?

3 Answers

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

Okay, 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.

Boban Talevski
Boban Talevski
24,793 Points
Boban Talevski
Boban Talevski
24,793 Points

Hmm...I think I tried that to some extent.

Would this be the code?

CREATE TABLE ROCKS (
  ID PRIMARY KEY,
  NAME,
  TYPE,
  COLOR
);

Guess TYPE is a keyword as well, but with this I'm still getting the error 

"Bummer! SQL Error: column ID is not unique"

When I am declaring the type INTEGER for the ID column as Ben said SQLite wants/needs that

CREATE TABLE ROCKS (
  ID INTEGER PRIMARY KEY AUTOINCREMENT, -- tried with or without the AUTOINCREMENT
  NAME,
  TYPE,
  COLOR
);

it's 

"Bummer! SQL Error: PRIMARY KEY must be unique"

And of course, if I try this

CREATE TABLE ROCKS (
  ID,
  NAME,
  TYPE,
  COLOR
);

I get 

"Bummer! Make sure you're id only allows unique values. Hint: ID PRIMARY KEY"

He misspelled "your" to make it even more confusing ;)

And I also tried adding IF NOT EXISTS, same error messages, even tried DROP TABLE IF EXISTS ROCKS, but then the error was something like you should start with CREATE TABLE.

So, not sure how you managed to pass it, but please share the specific code :).

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

It also expects you to name the columns exactly as given in the instructions ... in lower case.

Boban Talevski
Boban Talevski
24,793 Points
Boban Talevski
Boban Talevski
24,793 Points

Oh...wow :). Thanks

Btw, even if passed, there's that message about column id not being unique. It was part of the successfully completed challenge after all, as one would think, which made it even more confusing while troubleshooting this.

Nice! You're doing great!

> create table rocks ( id PRIMARY KEY, name, type, color )

SQL Error: column id is not unique
Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

I believe that message is related to the validation and does not prevent passing the challenge.

Boban Talevski
Boban Talevski
24,793 Points
Boban Talevski
Boban Talevski
24,793 Points

Exactly...it's what I meant. The tests probably insert rows and expect this error message to make sure we actually used PRIMARY KEY (or better yet primary key) for our ID (or id) column. Which is misleading to be given as an error message of why our own DDL code doesn't work, when in fact the tests getting that message means our DDL code actually works as requested.

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

I think it's a bug, based on this question and this other one and the fact that I couldn't solve it either!

Hopefully they'll roll out a fix soon.

If you'd like to make sure it was correctly reported to the staff, you might send in another report as described on the Support page.

Boban Talevski
Boban Talevski
24,793 Points
Boban Talevski
Boban Talevski
24,793 Points

Thanks for pointing the other topics...sent a question to support as well, guess they'll realize it's a bug and fix it soon. If it's indeed a bug though, but judging by the number of people having issues I doubt it's something obvious which we all missed.

Ben Deitch
STAFF
Ben Deitch
Treehouse Teacher
Ben Deitch
Ben Deitch
Treehouse Teacher

Hey Boban!

Thanks for bubbling this up to support. Looks like we were missing a couple things in the code challenges, though it should be fixed now. Let me know if it's not!

Ps. Thanks Steven Parker as well :)