sql_constraints

SQL Constraints

SQL Constraints Explained

SQL constraints are rules enforced on data columns within a table. They are used to ensure the accuracy, reliability, and integrity of the data. If any DML operation (INSERT, UPDATE, DELETE) violates a constraint, the operation is aborted.

Constraints can be defined at two levels:

• Column-level: Defined as part of a column definition and applies only to that specific column.
• Table-level: Defined separately from column definitions and can apply to one or more columns in the table (necessary for multi-column primary keys or unique constraints).

Constraints can be specified when a table is created using CREATE TABLE or added to an existing table using ALTER TABLE.

Common SQL Constraints

Here are the most commonly used SQL constraints:

2. **NOT NULL**

   Ensures that a column cannot have a NULL value. Every row must have a value for this column.

   `-- Column-level
   

CREATE TABLE Employees ( EmployeeID INT NOT NULL, LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255) );</pre></li> 2. <li>**UNIQUE**<p>Ensures that all values in a column (or a set of columns) are distinct. A table can have multiple UNIQUE constraints. NULL values are typically allowed (and multiple NULLs are often permitted as NULL is not equal to NULL, but behavior can vary by RDBMS).</p><pre>— Column-level CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductCode VARCHAR(50) UNIQUE, ProductName VARCHAR(255) ); — Table-level (for multi-column unique constraint) CREATE TABLE UserSettings ( UserID INT, SettingKey VARCHAR(100), SettingValue VARCHAR(255), CONSTRAINT UQ_UserSetting UNIQUE (UserID, SettingKey) );</pre></li> 3. <li>**PRIMARY KEY**<p>Uniquely identifies each record in a table. A primary key must contain unique values and cannot contain NULL values (it's implicitly NOT NULL and UNIQUE). A table can have only one primary key, which can consist of single or multiple columns (a composite key).</p><pre>— Column-level CREATE TABLE Departments ( DepartmentID INT PRIMARY KEY, DepartmentName VARCHAR(100) ); — Table-level (for composite primary key) CREATE TABLE OrderDetails ( OrderID INT, ProductID INT, Quantity INT, PRIMARY KEY (OrderID, ProductID) );</pre></li> 4. <li>**FOREIGN KEY**<p>Ensures referential integrity by linking a column (or columns) in one table to a primary key or unique key in another table (the parent table). This prevents actions that would leave orphaned records. You can also specify actions like ON DELETE CASCADE(delete child rows when parent row is deleted) orON UPDATE SET NULL (set foreign key to NULL if parent key is updated).</p><pre>CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE, CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE SET NULL — Example action ); — (Assuming a Customers table with CustomerID as PRIMARY KEY exists)</pre></li> 5. <li>**CHECK**<p>Ensures that all values in a column satisfy a specific condition or expression. The condition must evaluate to TRUE or UNKNOWN (e.g., if checking a NULL value) for the constraint to be satisfied.</p><pre>CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Salary DECIMAL(10, 2), Age INT, CONSTRAINT CK_Salary CHECK (Salary > 0), CONSTRAINT CK_Age CHECK (Age >= 18 AND Age <= 65) );</pre></li> 6. <li>**DEFAULT**<p>Specifies a default value for a column when no value is explicitly provided during an INSERT operation.</p><pre>CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(255) NOT NULL, StockQuantity INT DEFAULT 0, DateAdded TIMESTAMP DEFAULT CURRENT_TIMESTAMP );`

Syntax for Defining Constraints

During Table Creation (CREATE TABLE):

CREATE TABLE table_name (
    column1 datatype [CONSTRAINT constraint_name] column_constraint,
    column2 datatype [DEFAULT default_value],
    ...
    [CONSTRAINT table_constraint_name] table_constraint (column_list)
);

Adding Constraints to an Existing Table (ALTER TABLE):

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_list);

Example: ALTER TABLE Employees ADD CONSTRAINT CK_EmpEmail CHECK (Email LIKE '%@%.%');

Naming constraints (e.g., FK_CustomerOrder, CK_Salary) is a good practice as it makes them easier to manage (e.g., to drop or modify later) and understand from error messages.

Importance of Constraints:

• Data Integrity: Prevents invalid or inconsistent data from being entered into the database.
• Data Accuracy: Helps maintain the correctness of the data.
• Database Structure: Defines relationships between tables and enforces business rules at the database level.
• Performance: Primary keys and unique constraints often have associated indexes, which can speed up queries.

The next chapters will describe some of these constraints in more detail, focusing on how they are applied and their impact.