Your data, our passion.
Snack-Sized Data Basics
SQL - Data Insert Restrictions
Let's quickly recap what we've done in the last few lessons.
​
We've been using PostgreSQL through sqliteonline.com by selecting PostgreSQL from the left menu, and clicking on Click to connect.
​​
Since sqliteonline.com has already created a database for us, we didn’t need this step, but here’s how to create one.
​​
CREATE DATABASE name_of_database;
We created a table.
​​
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
allergies VARCHAR(100) DEFAULT 'None'
);
​​​
INSERT INTO students (first_name, last_name, date_of_birth, allergies)
VALUES
('Alice', 'Johnson', '2015-04-15', 'Peanuts'),
('David', 'Lee', '2014-01-30', 'Gluten'),
('Eva', 'Martinez', '2014-12-05', 'Shellfish');
​
We added data to our table.
In this lesson, we'll cover a few examples where data can't be inserted successfully.
Not following the NOT NULL constraint
Three of our columns have a NOT NULL constraint. If you try to insert data without providing values for those columns, you’ll get an error. For example, the query below leaves out first_name. Running it will return an error, reminding you that first_name cannot be NULL.
INSERT INTO students (last_name, date_of_birth, allergies)
VALUES
('Johnson', '2015-04-15', 'Peanuts');
On the other hand, leaving out allergies will work because we set it to default to 'None' when no value is provided.
INSERT INTO students (last_name, first_name, date_of_birth)
VALUES
('Johnson', 'Sandra', '2015-04-15');
Not following the data type constraint
Even though student_id is automatically added through SERIAL PRIMARY KEY, entering a valid integer number would still work as long as it hasn't already been used.
However, if you tried to insert a non integer value, the system will return an error, specifying that student_id needs to be an integer.
Here's an example below.
INSERT INTO students (student_id, last_name, first_name, date_of_birth, allergies)
VALUES
('Hello', 'Johnson', 'Sandra', '2015-04-15', 'Peanuts');
Here’s another example of an incorrect data type. The query below attempts to insert a non-date value into the date_of_birth column.
INSERT INTO students (last_name, first_name, date_of_birth, allergies)
VALUES
('Johnson', 'Sandra', 'Hello', 'Peanuts');
Not following the PRIMAY KEY constraint
As mentioned earlier, we can manually enter student_id even though it auto generates, but we cannot enter the same value more than once.
In the example below, the first insert will work.
INSERT INTO students (student_id, last_name, first_name, date_of_birth, allergies)
VALUES
( 21, 'Johnson', 'Sandra', '2015-04-15', 'Peanuts');
Inserting student_id as 21 a second time will result in an error, due to the PRIMARY KEY constraint.
INSERT INTO students (student_id, last_name, first_name, date_of_birth, allergies)
VALUES
( 21, 'Johnson', 'Sandra', '2015-04-15', 'Peanuts');
Now it’s your turn to practice! Try experimenting with different data types and constraints to see what kinds of errors are triggered when the input doesn’t match what’s expected.