Database Constraint by Thirdy Gayares

A database constraint is a rule applied to columns or tables to ensure the integrity, accuracy, and reliability of the data being stored. These constraints help prevent invalid data from being inserted, ensuring consistency across the database. Constraints are essential to protect the structure of a database and maintain proper relationships between data.

Here are the most common types of database constraints, along with multiple examples to help you understand how they work in practice.

1. Primary Key

A Primary Key is a column (or combination of columns) that uniquely identifies each record in a table. A primary key enforces two important rules:

Uniqueness – No two records can have the same value in this column.
Not Null – The primary key column cannot contain NULL values.

Examples:

Example 1: In a student database, student_id can be the primary key since it uniquely identifies each student.
```
  CREATE TABLE Students (
      student_id INT PRIMARY KEY,
      name VARCHAR(50)
  );
```

Example 2: In an employee database, employee_id can be the primary key.

  CREATE TABLE Employees (
      employee_id INT PRIMARY KEY,
      first_name VARCHAR(50),
      last_name VARCHAR(50)
  );

Example 3: A composite primary key uses two or more columns to create a unique identifier.

  CREATE TABLE Orders (
      order_id INT,
      product_id INT,
      PRIMARY KEY (order_id, product_id)
  );

2. Foreign Key

A Foreign Key is a column (or a set of columns) in one table that references the Primary Key in another table. This creates a relationship between the two tables, ensuring that a record cannot exist in the child table unless it exists in the parent table.

Examples:

Example 1: customer_id in the Orders table is a foreign key that references customer_id in the Customers table.

  CREATE TABLE Orders (
      order_id INT PRIMARY KEY,
      customer_id INT,
      FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
  );

Example 2: In an Enrollments table, student_id is a foreign key referencing the Students table.

  CREATE TABLE Enrollments (
      enrollment_id INT PRIMARY KEY,
      student_id INT,
      course_id INT,
      FOREIGN KEY (student_id) REFERENCES Students(student_id)
  );

Example 3: A foreign key on the Orders table references both customer_id and product_id from separate tables.

  CREATE TABLE Orders (
      order_id INT PRIMARY KEY,
      customer_id INT,
      product_id INT,
      FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
      FOREIGN KEY (product_id) REFERENCES Products(product_id)
  );

3. Unique

The Unique constraint ensures that all the values in a column are different. Unlike a primary key, a table can have multiple unique constraints, and these columns can accept NULL values, but only one NULL is allowed.

Examples:

Example 1: In a Users table, the email column should be unique so that no two users can have the same email address.
```
  CREATE TABLE Users (
      user_id INT PRIMARY KEY,
      email VARCHAR(100) UNIQUE
  );
```
Example 2: You can create a unique constraint for multiple columns in a Products table to ensure no two products have the same combination of name and category.
```
  CREATE TABLE Products (
      product_id INT PRIMARY KEY,
      name VARCHAR(100),
      category VARCHAR(50),
      UNIQUE (name, category)
  );
```

Example 3: In a Vehicles table, the combination of license_plate and state must be unique.

  CREATE TABLE Vehicles (
      vehicle_id INT PRIMARY KEY,
      license_plate VARCHAR(10),
      state CHAR(2),
      UNIQUE (license_plate, state)
  );

4. Not Null

The Not Null constraint ensures that a column cannot contain NULL values. It guarantees that every row in the table must have a value for the specified column.

Examples:

Example 1: In a Customers table, the name column should always have a value.

  CREATE TABLE Customers (
      customer_id INT PRIMARY KEY,
      name VARCHAR(100) NOT NULL
  );

Example 2: A Products table where the price cannot be NULL.

  CREATE TABLE Products (
      product_id INT PRIMARY KEY,
      price DECIMAL(10, 2) NOT NULL
  );

Example 3: The date_of_birth column in an Employees table should not allow NULL values.

  CREATE TABLE Employees (
      employee_id INT PRIMARY KEY,
      date_of_birth DATE NOT NULL
  );

5. Default

The Default constraint provides a default value for a column when no value is specified during insertion. This is useful for fields where most rows will have the same value.

Examples:

Example 1: In a Users table, if no status is provided, it defaults to 'active'.

  CREATE TABLE Users (
      user_id INT PRIMARY KEY,
      status VARCHAR(10) DEFAULT 'active'
  );

Example 2: In a Products table, if no stock_quantity is provided, it defaults to 0.

  CREATE TABLE Products (
      product_id INT PRIMARY KEY,
      stock_quantity INT DEFAULT 0
  );

Example 3: In an Orders table, the order_date defaults to the current date.

  CREATE TABLE Orders (
      order_id INT PRIMARY KEY,
      order_date DATE DEFAULT CURRENT_DATE
  );

6. Check

The Check constraint ensures that all values in a column satisfy a specific condition. It enforces data validation at the database level.

Examples:

Example 1: In an Employees table, the salary column must have a value greater than 0.

  CREATE TABLE Employees (
      employee_id INT PRIMARY KEY,
      salary DECIMAL(10, 2),
      CHECK (salary > 0)
  );

Example 2: In a Students table, the age column must ensure the student is at least 18 years old.

  CREATE TABLE Students (
      student_id INT PRIMARY KEY,
      age INT CHECK (age >= 18)
  );

Example 3: In a Products table, ensure that the discount value is between 0 and 100.

  CREATE TABLE Products (
      product_id INT PRIMARY KEY,
      discount INT CHECK (discount BETWEEN 0 AND 100)
  );

Constraints are important for keeping data accurate, consistent, and fast in databases. Using them correctly helps your database run well with valid data and quick queries. Common constraints like Primary Key, Foreign Key, Unique, Not Null, Default, and Check are necessary to build strong and reliable database systems.

Database Constraint

1. Primary Key

Examples:

2. Foreign Key

Examples:

3. Unique

Examples:

4. Not Null

Examples:

5. Default

Examples:

6. Check

Examples:

References: