The not-null and unique constraints

Introduction to Relational Databases in SQL

Timo Grossenbacher

Data Journalist

The not-null constraint

  • Disallow NULL values in a certain column
  • Must hold true for the current state
  • Must hold true for any future state
Introduction to Relational Databases in SQL

What does NULL mean?

  • unknown
  • does not exist
  • does not apply
  • ...
Introduction to Relational Databases in SQL

What does NULL mean? An example

CREATE TABLE students (
 ssn integer not null,
 lastname varchar(64) not null,
 home_phone integer,
 office_phone integer
);

NULL != NULL
Introduction to Relational Databases in SQL

How to add or remove a not-null constraint

When creating a table...

CREATE TABLE students (
 ssn integer not null,
 lastname varchar(64) not null,
 home_phone integer,
 office_phone integer
);

After the table has been created...

ALTER TABLE students 
ALTER COLUMN home_phone 
SET NOT NULL;

ALTER TABLE students 
ALTER COLUMN ssn 
DROP NOT NULL;
Introduction to Relational Databases in SQL

The unique constraint

  • Disallow duplicate values in a column
  • Must hold true for the current state
  • Must hold true for any future state

Introduction to Relational Databases in SQL

Adding unique constraints

CREATE TABLE table_name (
 column_name UNIQUE
);

ALTER TABLE table_name
ADD CONSTRAINT some_name UNIQUE(column_name);
Introduction to Relational Databases in SQL

Let's apply this to the database!

Introduction to Relational Databases in SQL

Preparing Video For Download...