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 Modifying Data with SQL Adding Data to a Database Adding Multiple Rows to a Table

Posted by A X
A X
12,842 Points

Comment/Question on Adding Data w/ SQL Code Challenge

I struggled a bit to complete the code challenge for "Adding Data w/ SQL Code" because I didn't recall in what video prior to the code challenge that Andrew mentioned that "you can use no in your ID column to trigger the auto increment". He mentions that in the video after the code challenge...I guess my question is where was the video that this concept was originally introduced? (was I just secretly asleep!?) Also how would that look vs. what I ended up doing for the ID in the code challenge, which was make a super long number since basic numbers through the unique primary key needed error.

My comment is I wish Andrew would've reminded us of this fact in this video: "Adding Multiple Rows to a Table" prior to taking the code challenge where this information would've been useful.

Thanks!

Edit: Ah thanks for explaining jcorum, I'm hard of hearing, and the captions and transcript say No vs. NULL...that probably should be fixed.

2 Answers

jcorum
jcorum
71,830 Points

When you write:

INSERT INTO products (id, name, description, price) VALUES (NULL, "MacBook Pro", "laptop", 1250.00)

you are telling SQL to put NULL in the id field, MacBook Pro in the name field, laptop in the description field and 1250.00 in the price field. You could just as easily have written:

INSERT INTO products (price, description, name, id) VALUES (1250.00, "laptop", "MacBook Pro", NULL)

SQL knows which value to put in which field because you have to list the field names in the same order as the values.

If you have values for all the fields and they are in the same order as the fields occur in the table, then you can do this (at least in some versions of SQL):

INSERT INTO products ("MacBook Pro", "laptop", 1250.00)

In this shortened version we're assuming the fields are in id, name, description and price order in the table, and because id is an auto-increment field -- which means that SQL will fill it in for you -- you don't need to list it, or provide a value for it.

But if you include id in the list of field names, then you need to provide a value for it. In MySQL you could look up the current largest id number (SELECT MAX(id) FROM ...) and then use the next biggest number in your INSERT. But that's tedious, and doesn't work if your SQL does multiple (bulk) inserts, etc. So using NULL is just a way to tell SQL to go ahead and do the auto-increment. But it's best not to list the id in the field name list and then not to put NULL as the value for the id field.

jcorum
jcorum
71,830 Points
jcorum
jcorum
71,830 Points

John mentioned that you could use NULL to trigger auto-increment in the 2nd video (i.e, the one before the one before challenge).
This code works: INSERT INTO products (id, name, description, price) VALUES (NULL, "name", "description", 23.4) But people usually would do it this way instead: INSERT INTO products (name, description, price) VALUES ("X", "DDD", 23.4) That is, if the id field is auto-increment, you don't need to provide a value for it, not even NULL, but you also don't include the field name in the list of fields.

A X
A X
12,842 Points

jcorum, I appreciate your information...I was wondering what we'd put in as a "placeholder" value for areas of the VALUES that we didn't want to modify the value of...so there's NULL (vs. no...I'm hard of hearing...and the transcript and captions say no vs. null), and I'm surprised that SQL is "smart" enough to understand what we mean if we were to just put in "X", "DDD", 23.4 and not the auto-increment value. Does SQL ever get "confused"? Like imagine that we have several string based columns in a row, how would SQL "know" which VALUES we wanted to add and which we didn't? Are we forced to use NULL or is there another way to help SQL "understand what we mean"? Thanks!