PostgreSQL Constraints

PostgreSQL의 Constraints(제약조건)에 대해 정리하였습니다.

---

< Introduction >

Our database will be designed to accept input from a variety of applications, user filled-forms, and other sources. We’d also like to put data validation in place to protect our DB from receiving unexpected, improperly formatted, or invalid data.

Luckily for us, PostgreSQL offers methods to safeguard a database and maintain data integrity. One of these methods is called constraints. Constraints are rules defined as part of the data model to control what values are allowed in specific columns and tables.

 

Specifically, constraints :

• Reject inserts or updates containing values that shouldn’t be inserted into a database table, which can help with preserving data integrity and quality.
• Raise an error when they’re violated, which can help with debugging applications that write to the DB.

 

 

---

< PostgreSQL Data Types >

With Data Types, we’re telling PostgreSQL which types of values can be inserted into each column in the table.

 

Name	Description
boolean	true/false
varchar or varchar(n)	text with variable length, up to n characters (if specified)
date	calendar date
integer	whole number value between -2147483648 and +2147483647
numeric(a, b)	decimal with total digits (a) and digits after the decimal point (b)
time	time of day (no time zone)

 

However, data types don’t prevent all unexpected data from being inserted into a table. 

 

Example)

CREATE TABLE volunteers (
    id integer,
    name varchar,
    hours_available integer,
    phone_number varchar(12),
    email varchar
);

Issue 1 : we’ve defined phone_number as varchar(12) and might expect a 10-digit phone number formatted as XXX-XXX-XXXX. Consider the following issues that may arise:

1. An incomplete value formatted like XXX-XXXX will be accepted because it’s under 12 characters.
2. A value like +X XXX-XXX-XXXX will cause PostgreSQL to raise an error because it’s longer than 12 characters, even though it’s a valid entry.

 

Issue 2 : PostgreSQL will try to interpret incoming data as the data type the column has been defined as. This process, called type casting, can have mixed results.

1. If one tries to insert 1.5 into our table’s hours_available column, PostgreSQL will cast this value to integer, round the data, and insert it into the table as 2.

2. If one tries to insert 1.5 into the email column, PostgreSQL will insert this into the database by casting 1.5 to '1.5' even though '1.5' is not a valid email address.

 

 

Reference : You can refer to the complete list of available data types in the PostgreSQL documentation.

Chapter 8. Data Types

 

 

---

< Nullability Constraints >

To reject inserts and updates that don’t include data for specific columns by adding a NOT NULL constraint on those columns.

If you try to enter incomplete data, PostgreSQL will raise an error alerting us that these rows violate the constraint and that our insert or update couldn’t be completed.

 

ex)

CREATE TABLE talks (
    id integer,
    title varchar NOT NULL,
    speaker_id integer NOT NULL,
    estimated_length integer,
    session_timeslot timestamp NOT NULL
);

INSERT INTO talks (id, estimated_length)
VALUES (1, 30);

-- ERROR: null value in column "title"  violates not-null constraint
-- Detail: Failing row contains (1, null, null, 30, null).

 

 

---

< Improving Tables with Constraints >

In PostgreSQL, we can use ALTER TABLE statements to add or remove constraints from existing tables.

 

Example : add 'NOT NULL' constraint and drop 'NOT NULL' constraint

-- add 'NOT NULL' constraint
ALTER TABLE talks
ALTER COLUMN session_timeslot SET NOT NULL;

-- drop 'NOT NULL' constraint
ALTER TABLE talks
ALTER COLUMN session_timeslot DROP NOT NULL

However, PostgreSQL will reject the addition of the constraint and raise the following error because NULL values are already present in the column.

ALTER TABLE talks
ALTER COLUMN title SET NOT NULL;

-- SQL Error [23502]: ERROR: column "title" contains null values

 

 

If the table we’re attempting to add a constraint on doesn’t meet the constraint, we can backfill the table so that it does adhere to the constraint. 
Backfilling is a term occasionally used in DB engineering to refer to the process of adding or updating past values.

 

Example : Backfill and apply 'NOT NULL' constraint

UPDATE talks
SET title = 'TBD'
WHERE title IS NULL;

With the table updated so that there are no longer any nulls in title, and we can now apply the NOT NULL constraint.

ALTER TABLE talks
ALTER COLUMN title SET NOT NULL;

 

 

---

< Introduction to Check Constraints >

To implement more precise constraints on our table using CHECK statement.

A CHECK constraint can be written into a 1. CREATE TABLE statement,
or added to an existing table with 2. ALTER TABLE.

 

< Syntax >

CHECK (...)

: write the condition we’d like to test for inside the parentheses.
: The condition tested for inside of parentheses of a CHECK statement must be a SQL statement that can be evaluated as either true or false.

1. Use in ALTER TABLE

ALTER TABLE talks 
ADD CHECK (estimated_length > 0);

2. Use in CREATE TABLE

CREATE TABLE talks (
  estimated_length integer NOT NULL CHECK (estimated_length > 0)
)

 

 

---

< Check Constraints Continued >

Inside a CHECK statement, we can use a wide array of SQL syntax to create our conditions.

 

< Within our check constraint, we can : >

• Make comparisons between columns within the table
• Use logical operators like AND and OR
• Use other SQL operators you may be familiar with (IN, LIKE)
  etc...

: As a general rule, any logic that you might use in a WHERE statement to filter individual rows from an existing table can be applied within a CHECK

 

Example)

ALTER TABLE talks 
ADD CHECK (estimated_length > 0 AND estimated_length < 120);

ALTER TABLE attendees
ADD CHECK (standard_tickets_reserved + vip_tickets_reserved = total_tickets_reserved.);

 

 

---

< Using Unique Constraints >

When designing a PostgreSQL data model, it’s a good practice to structure tables such that rows are uniquely identifiable by some combination of attributes.

To prevents the same value using UNIQUE keyword.

A UNIQUE constraint can be written into a 1. CREATE TABLE statement,
or added to an existing table with 2. ALTER TABLE.

 

< Structuring your tables in this way leads to a few benefits : >

• The structure of your data model and the contents of individual tables are more easily interpreted.
• Queries to access information from the table can be simpler. For example, if we’d like to query our attendees table to find out how many tickets an attendee has reserved, having a unique identifier for each attendee allows us to get a result without any intermediate aggregation.
• Identifying and implementing a PRIMARY KEY is easier on tables with UNIQUE constraints already in place.

 

Example)

-- to make sure that no two people submit the same email address when they register.
ALTER TABLE attendees 
ADD UNIQUE (email);

-- to ensure that a speaker is never booked for multiple talks at the same time.
ALTER TABLE talks
ADD UNIQUE (speaker_id, session_timeslot)

-- to ensures attendees(attendee_id) are registered for only one talk at a time(session_timeslot).
CREATE TABLE registrations (
    id integer NOT NULL,
    attendee_id integer NOT NULL,
    session_timeslot timestamp NOT NULL,
    talk_id integer NOT NULL,
    UNIQUE (attendee_id, session_timeslot)
);

 

 

---

< Primary Key >

Having unique constraints is useful, but an important part of building a relational data model requires defining relationships between tables. Primary keys are essential to defining these relationships.

A primary key is a column (or set of columns) that uniquely identifies a row within a database table.

A table can only have one primary key, and in order to be selected as a primary key a column (or set of columns) should:
1. Uniquely identify that row in the table (like a UNIQUE constraint)
2. Contain no null values (like a NOT NULL constraint)

 

< primary keys improve your data model in several other ways : >

• Many joins will use the primary key from one table to join data with another table
• Primary keys can improve query performance
• Primary keys help to enforce data integrity within a table by ensuring that rows can be uniquely identified

 

Example)

ALTER TABLE attendees
ADD PRIMARY KEY (id); 

CREATE TABLE attendees (
  id integer PRIMARY KEY,
  name varchar NOT NULL
)

 

 

---

< Foreign Key >

When discussing relations between tables, you may see the terms parent table and child table to describe two tables that are related. More specifically, values inserted into child table must be validated by data that’s already present in a parent table.
Formally, this property that ensures data can be validated by referencing another table in the data model is called referential integrity. Referential integrity can be enforced by adding a FOREIGN KEY on the child table that references the primary key of a parent table.

If the parent table doesn’t contain the data a user is attempting to insert, PostgreSQL will reject the insert or update and throw an error.

A FOREIGN KEY constraint can be written into a 1. CREATE TABLE statement,
or added to an existing table with 2. ALTER TABLE.

 

Example :

In our example above registrations is a child table of talks because entries in registrations must reference the primary key from talks. Suppose talks also has a column named id as a primary key. Now, we can update our registrations table with a foreign key using the following statement.

CREATE TABLE registrations (
  talk_id REFERENCES talks (id)
)

ALTER TABLE registrations
ADD FOREIGN KEY (talk_id)
REFERENCES talks (id);


--Suppose we now want to enter a registration for talk_id = 100, 
--which does not yet exist in the talks table. Trying to insert a registration for this talk yields an error
-- because there is not a corresponding entry in talks to reference yet.
INSERT INTO registrations VALUES (100, 1, '2020-08-15 9:00:00', 1);
--SQL Error [23503]: ERROR: insert or update on table "registrations" violates foreign key constraint "registrations_id_fkey"
--Detail: Key (talk_id)=(100) is not present in table "talks".

 

 

---

< Foreign Keys - Cascading Changes >

By default, a foreign key constraint will prevent an engineer from deleting or updating a row of a parent table that is referenced by some child table.
This behavior is sometimes explicitly specified in a CREATE TABLE statement using REFERENCES talks (id) ON DELETE RESTRICT or REFERENCES talks (id) ON UPDATE RESTRICT.

However, rather than preventing changes, CASCADE clauses (ON UPDATE CASCADE, ON DELETE CASCADE) cause the updates or deletes to automatically be applied to any child tables.

 

Example :

suppose we’d like to set up our database to automatically unregister attendees from a talk that’s been cancelled.

ALTER TABLE registrations
ADD FOREIGN KEY (talk_id)
REFERENCES talks (id) ON DELETE CASCADE

SELECT * 
FROM registrations
WHERE talk_id = 1;

id	attendee_id	session_timelot	talk_id
8	2	2020-08-15 9:00:00	1
9	5	2020-08-15 9:00:00	1
10	8	2020-08-15 9:00:00	1
11	9	2020-08-15 9:00:00	1

DELETE FROM talks 
WHERE id = 1;

SELECT * 
FROM registrations
WHERE talk_id = 1;

id

attendee_id

session_timelot

talk_id

: Because we’ve specified ON DELETE CASCADE on our foreign key, the DELETE statement ran successfully even though there were still rows in registrations that referenced talk_id = 1.

 

 

Reference : PostgreSQL Constraints

5.3. Constraints