Inserting Data into Tables with Constraints: NOT NULL, DEFAULT

Today we're gonna dive into another super important part of working with databases — how to insert data into tables that have NOT NULL and DEFAULT constraints. By now, you already know the basic syntax for the INSERT INTO command, and you get how to add rows to tables with a certain structure. Now it's time to get into the details — because dealing with constraints is often where devs start pulling their hair out. But don't worry, by the end of this lecture you'll be handling these mechanisms like a pro!

What are constraints?

Constraints are rules that you put on table columns. They help keep your data logically correct. For example, you can't leave a field empty if it absolutely has to have data (NOT NULL), or you can skip specifying a value for a column if there's already a default value set up (DEFAULT).

Types of constraints: - NOT NULL — means the column can't have NULL. - DEFAULT — sets a value that will automatically be inserted into the column if you don't specify one.

Constraints help keep your data consistent. Imagine you're storing a list of students in a students table, and one of the rules is that every student has to have a name (NOT NULL). Without this constraint, someone might forget to enter a name, and you'd end up with a "nameless student" in your database. Or another example: you decide that the default value for the number of credits for a new course is 0 (DEFAULT), so you don't have to think about it every time.

Working with the NOT NULL constraint

The NOT NULL constraint makes you specify a value in a certain column when adding a record. If you try to insert NULL into that column or just leave it out, the database will "get mad" (you'll get an error). Let's check out an example.

Let's create a simple students table:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,     -- Name can't be empty
    age INT
);

Notice: the name column has a NOT NULL constraint, but the age column doesn't.

Example of inserting data with NOT NULL

Now let's try adding some students:

-- Successful insert
INSERT INTO students (name, age) VALUES ('Alice', 23);
-- Success: name is specified, field satisfies NOT NULL constraint

-- Example of an error:
INSERT INTO students (age) VALUES (30);
-- Error: name field can't be NULL, since it has a NOT NULL constraint

When you get an error like null value in column "name" violates not-null constraint, don't freak out — that's just PostgreSQL reminding you to pay attention.

Using default values (DEFAULT)

The DEFAULT constraint tells the database: "If you forget to specify a value for this column, use this value instead." This makes life easier for devs, because you don't have to fill in every single field by hand.

Example of a table with a DEFAULT constraint

Let's create a courses table:

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    credits INT DEFAULT 0   -- Default value for credits column is 0
);

Example of inserting data with DEFAULT

Now let's try adding some courses:

-- Explicitly specified value for credits
INSERT INTO courses (name, credits) VALUES ('Mathematics', 5);

-- Didn't specify value for credits — default value (0) will be used
INSERT INTO courses (name) VALUES ('History');

-- Let's see what we got
SELECT * FROM courses;

Result:

Notice: when we added the History course, we didn't specify the number of credits. PostgreSQL automatically filled in the credits column with 0.

Using NOT NULL and DEFAULT together

These columns require a value, even if you don't specify one yourself. For example, in the courses table, the credits column can be both DEFAULT and NOT NULL, to make sure nobody accidentally leaves it empty (NULL).

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    credits INT NOT NULL DEFAULT 0
);

Example of inserting data

Let's say every new course should have 0 credits by default, but NULL is not allowed.

-- Success: credits will be set to default value
INSERT INTO courses (name) VALUES ('Physics');

-- Error: trying to explicitly insert NULL into a NOT NULL column
INSERT INTO courses (name, credits) VALUES ('Chemistry', NULL);

Example: Adding a student with a required name

Let's create a students table with constraints:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,                         -- Name is required
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Enrollment date defaults to current date
);

Now let's add some students:

-- Name is specified, enrollment date will be filled in automatically
INSERT INTO students (name) VALUES ('Bob');

-- Let's try not specifying the name (will get an error)
INSERT INTO students (enrollment_date) VALUES ('2024-10-01');

Great example of how PostgreSQL helps guarantee correct data: all students must have a name, and the enrollment date doesn't need to be specified explicitly.

Tips and "gotchas"

When you're working with NOT NULL and DEFAULT constraints, keep a few important things in mind. For example, if your data often has NULLs, the NOT NULL constraint can be a real pain. And if you set a default value for every column, you might accidentally end up with bogus data (like 0 instead of a real value).

Usually, if you add a new column to an existing table, its cells for already existing rows will be filled with NULL. But if you specify DEFAULT X, then the whole column will be filled with value X. That's super handy and helps keep your database consistent.

For example, a new column is a FOREIGN KEY to an existing table. And it can't have NULL or 0. It has to reference existing rows in the existing table.

What if you forgot to add constraints?

No worries — you can always add constraints later using the ALTER TABLE command. For example:

-- Add a DEFAULT constraint to an existing column
ALTER TABLE courses ALTER COLUMN credits SET DEFAULT 0;

-- Add a NOT NULL constraint to an existing column
ALTER TABLE students ALTER COLUMN name SET NOT NULL;

These queries won't break your existing data — you can use them on tables that already have valuable stuff in them.

Now you're ready to use the power of PostgreSQL to manage constraints! Your data will be more structured and protected from mistakes, and your queries will be logical and predictable.