Adding a Row to a Table

7:27 with Andrew Chalkley

In this video we'll take a look at adding rows of information into a table.

Teacher's Notes
Video Transcript
Downloads

SQL Used

Inserting a single row:

INSERT INTO <table> VALUES (<value 1>, <value 2>, ...);

This will insert values in the order of the columns prescribed in the schema.

Examples:

INSERT INTO users VALUES  (1, "chalkers", "Andrew", "Chalkley");
INSERT INTO users VALUES  (2, "ScRiPtKiDdIe", "Kenneth", "Love");

INSERT INTO movies VALUES (3, "Starman", "Science Fiction", 1984);
INSERT INTO movies VALUES (4, "Moulin Rouge!", "Musical", 2001);

Inserting a single row with values in any order:

INSERT INTO <table> (<column 1>, <column 2>) VALUES (<value 1>, <value 2>);
INSERT INTO <table> (<column 2>, <column 1>) VALUES (<value 2>, <value 1>);

Examples:

INSERT INTO users (username, first_name, last_name) VALUES ("chalkers", "Andrew", "Chalkley");
INSERT INTO users (first_name, last_name, username) VALUES  ("Kenneth", "Love", "ScRiPtKiDdIe");

INSERT INTO movies (title, genre, year_released) VALUES ("Starman", "Science Fiction", 1984);
INSERT INTO movies (title, year_released, genre) VALUES ("Moulin Rouge!", 2001,  "Musical");

See all of the SQL used in Modifying Data With SQL in the Modifying Data With SQL Cheatsheet.

0:00
Remember, each CRUD operation has its own SQL keyword.
0:04
We already know the keyword for reading from a database SELECT.
0:08
The keyword to create a row is INSERT.
0:11
Let's take a look at the general use case to insert data into a table.
0:16
You start off with the keyboard INSERT INTO table name.
0:19
This shows the database you want to insert some data into a particular table.
0:25
Then you include the keyword VALUES, followed by the values
0:28
we want to insert into the table, the values are surrounded in parentheses.
0:33
The order of these values are important,
0:36
the values have to be inserted in the same order the schema describes the columns.
0:41
Let's take a look at our library database and
0:44
insert some new books into our books table.
0:47
This is what the books table looks like now.
0:49
We have an ID, title, author, genre, and first_published columns.
0:56
The last book ID is 15.
0:59
Our new book should have the ID of 16.
1:02
Let's write the SQL statement to insert George Orwell's 1984.
1:10
First, it's INSERT INTO books,
1:17
followed by VALUES 16,
1:23
the string 1984 as the title.
1:31
George Orwell [SOUND] as the author.
1:39
Fiction as the genre.
1:45
And first published is an integer of 1949.
1:50
Let's run that.
1:53
And as you can see,
1:54
it's been inserted into the books table in the correct columns.
2:00
Selecting all records, looking at the last ID and then incrementing it by one
2:04
yourself, as you entered new rows into a database, seems like a lot of work.
2:10
Also, other database users may be entering their own rows.
2:15
Your generated ID and their generated ID may clash.
2:18
Remember, IDs are supposed to be unique, and they're used as a reference for
2:23
a particular row in the table.
2:25
SQL databases have automated this tedious dance, and
2:29
conflicts can be avoided with a feature called auto increment.
2:33
Auto increment generates the latest ID for you so you don't have to.
2:38
Most database tables with the ID column will have the auto
2:41
increment feature switched on.
2:43
People who designed the database schema would have most likely switched it on.
2:48
Let's insert another book, Dune.
2:50
Instead of having an integer, we enter the value, or rather,
2:55
the absence of a value, NULL.
2:58
When the row is inserted, Because
3:03
the ID column has been designated as an auto-incremented column,
3:08
the database will give the new entry the correct ID, which is 17.
3:14
Sometimes if the schema allows, you can insert NULL values into columns
3:19
when you don't know the information or you want to fill it in later.
3:23
For example, in our loans table, we have missing values in the returned_on column.
3:29
This is because they haven't been returned yet.
3:31
If we wanted to enter a new entry into the loans table where we didn't want the value
3:36
to be entered into the returned_on column, we could just use the value of NULL.
3:43
Now it's not very clear what all these values are.
3:46
Luckily, SQL has an alternative way to make this clearer.
3:51
Just after the table name,
3:53
we can include another set of parentheses with the column names in.
4:12
Then followed by the keyword VALUES.
4:18
And then all the values you want.
4:22
You're also not restricted by ordering the columns in this order.
4:26
As long as you order the values in the same order as you order your columns
4:30
they will get inserted correctly.
4:33
For example,
4:33
I could move the ID to the second to the last column in the columns entry.
4:46
If I move the ID to the second to last and
4:49
its value to the second to last, it would have the same results as if
4:54
they were written in the order they were in the schema.
5:03
You can change this into any combination you'd like as
5:07
long as the order of the values matches the column order in your statement.
5:16
Even with the book ID moved to the third column and value positions,
5:21
this same result will happen.
5:23
The row will be created with the correct values in each column.
5:28
Only the ID column will get auto incremented, not the returned_on.
5:32
The value in this case will be empty.
5:36
Finally, because the ID and the returned_on are NULL, meaning that
5:41
they are absent, we don't have to specify them in our columns or values.
5:53
The database will automatically know that there should be NULL because you didn't
5:58
specify them.
5:59
When we run the statement, we see the final sixth row entered
6:03
with the auto incremented ID and the returned_on as NULL.
6:08
We can write the general case of these insert statements like this.
6:11
The INSERT INTO keyword combo, followed by the table name or
6:15
the column names that you want to enter values in, surrounded in parenthesis.
6:20
Then the keyword VALUES followed by a value-filled pair of parentheses.
6:27
Something you should be aware of is, while some columns allow for null values,
6:31
database designers requires certain columns must have a value present.
6:36
For example, if you wanted to delete the book_id, And
6:42
the value in our INSERT query and
6:44
we try to create a new entry, we would get an error.
6:49
Error: NOT NULL constraint failed: loans.book_id.
6:54
It doesn't make sense that we should be able to create a loan
6:58
without an associated book.
7:00
The schema has been designed with that in mind.
7:04
In fact, the ID, book_id, patron_id,
7:09
loaned_on, and return_by can not have null values.
7:15
Only the returned_on can be NULL.
7:17
This makes sense since it's going to be returned at a future time.
7:21
People can get extensions and update the returned_by, but it can't be NULL.

You need to sign up for Treehouse in order to download course files.

Modifying Data with SQL

Adding a Row to a Table

SQL Used

Sign in