Constraints

Constraints are rules applied to database columns to control the type of data that can be inserted into them. They are specified after defining the data type for a column and are essential for maintaining the accuracy and reliability of the data in your database. Let’s explore the various types of constraints with an example.

Here’s a SQL statement that creates a table named celebs with several constraints:

CREATE TABLE celebs (
   id INTEGER PRIMARY KEY,
   name TEXT UNIQUE,
   date_of_birth TEXT NOT NULL,
   date_of_death TEXT DEFAULT 'Not Applicable'
);

Types of Constraints

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT

Activity

Now, let’s apply what we’ve learned. We’re going to create a new table called awards with specific constraints:

CREATE TABLE awards (
   id INTEGER PRIMARY KEY,
   recipient TEXT NOT NULL,
   award_name TEXT DEFAULT 'Grammy'
);

In this table, the recipient column cannot be NULL, and the award_name column defaults to 'Grammy' if no value is provided during an INSERT operation.

name	type
id	INTEGER
recipient	TEXT
award_name	TEXT

Why Are Constraints Important?

• Data Integrity: Constraints ensure that the data entered into the database is accurate and reliable.
• Avoiding Errors: They prevent common data entry errors by enforcing rules at the database level.
• Consistency: Helps maintain consistent data across different parts of the application.