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

Development Tools Database Foundations Joining Relational Data Between Tables in SQL Keys and Auto-Incrementing Values

Jason Dittmer
Jason Dittmer
8,273 Points
Posted by Jason Dittmer
Jason Dittmer
8,273 Points

Challenge Task 2, Missing the 'pk_id' Column

I'm stuck on Challenge Task 2. The question is: Alter the "t_movies" table to add an auto incrementing primary key called "pk_id" first.

Here's my incorrect answer:

ALTER TABLE t_movies ADD COLUMN pk_id INTERGER AUTO_INCREMENT PRIMARY KEY FIRST;

I get the following errors: Bummer! You're missing the 'pk_id' column from the 't_movies' table.

SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERGER AUTO_INCREMENT PRIMARY KEY FIRST' at line 1

3 Answers

Gloria Dwomoh
Gloria Dwomoh
13,116 Points

The issue here is that it didn't tell you to add a column, it said you should add a primary key; "add an auto incrementing primary key called "pk_id" first." So eventually it suppose to be along the lines of...

ALTER TABLE t_movies ADD pk_id INT PRIMARY KEY AUTO_INCREMENT FIRST;
yannverreault
yannverreault
12,599 Points

I just want to point out that if the user type ADD COLUMN instead of ADD, it will work well. He just mispelled INTEGER. This is his error.

Aaron Lafleur
Aaron Lafleur
10,474 Points
Aaron Lafleur
Aaron Lafleur
10,474 Points

Hi Jason,

I thought I'd mention that when I pasted your code into the code challenge and changed the spelling of INTEGER (check yours), everything worked fine. You are, in fact, adding a column with a primary key. I think, if I read the documentation properly, you can get away with not including COLUMN after ADD....it is implied.

Gloria Dwomoh
Gloria Dwomoh
13,116 Points

You are welcome.

Sherrie Gossett
Sherrie Gossett
14,924 Points

I got a correct answer with this: 

ALTER TABLE t_movies ADD COLUMN pk_id INTEGER AUTO_INCREMENT PRIMARY KEY FIRST;

Note: Your first answer Jason had "INTERGER" instead of "INTEGER." I believe the reason why the answer posted above also works is because the COLUMN keyword is optional.

Also see: (http://dev.mysql.com/doc/refman/5.6/en/optimizing-primary-keys.html)

"The primary key for a table represents the column or set of columns that you use in your most vital queries...

If your table is big and important, but does not have an obvious column or set of columns to use as a primary key, you might create a separate column with auto-increment values to use as the primary key. These unique IDs can serve as pointers to corresponding rows in other tables when you join tables using foreign keys." etc.