SQL constraints are rules that are applied to columns or tables in a database to ensure the accuracy, reliability, and integrity of the data. They help enforce business rules on data storage, ensuring that only valid data is stored in the database.

Here’s a look at the primary SQL constraints:

### 1. PRIMARY KEY
– **Syntax:**
“`sql
CREATE TABLE table_name (
column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
column2 datatype,
…
);
“`
OR
“`sql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
…
CONSTRAINT constraint_name PRIMARY KEY (column1)
);
“`

– **Description:** A primary key uniquely identifies each record in a table. It must contain unique values and cannot contain NULLs. Typically, a primary key is a single column, but it can also be a combination of columns (composite key).

– **Example:**
“`sql
CREATE TABLE Employees (
EmployeeID INT CONSTRAINT PK_Employee PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
“`

– **Use Case:** Commonly used as a unique identifier for records in a table, ensuring that no duplicate records exist.

### 2. FOREIGN KEY
– **Syntax:**
“`sql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
…
CONSTRAINT constraint_name FOREIGN KEY (column) REFERENCES parent_table(column)
);
“`

– **Description:** A foreign key is used to link two tables. It is a column (or a set of columns) that refers to a column in another table, typically the primary key of that table.

– **Example:**
“`sql
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
CONSTRAINT FK_Customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
“`

– **Use Case:** Used to maintain referential integrity between tables, ensuring that a record cannot exist in the child table unless it has a corresponding record in the parent table.

### 3. UNIQUE
– **Syntax:**
“`sql
CREATE TABLE table_name (
column1 datatype CONSTRAINT constraint_name UNIQUE,
…
);
“`

– **Description:** The UNIQUE constraint ensures that all values in a column are different from each other. Similar to a primary key, but a table can have multiple UNIQUE constraints, whereas it can have only one primary key.

– **Example:**
“`sql
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
SerialNumber VARCHAR(100) CONSTRAINT UQ_SerialNumber UNIQUE
);
“`

– **Use Case:** Useful to enforce distinct values in a column, such as email addresses, usernames, or any other field where duplication is not allowed.

### 4. NOT NULL
– **Syntax:**
“`sql
CREATE TABLE table_name (
column1 datatype NOT NULL,
…
);
“`

– **Description:** The NOT NULL constraint ensures that a column cannot have a NULL value, making it mandatory to provide a value for this column.

– **Example:**
“`sql
CREATE TABLE Persons (
PersonID INT PRIMARY KEY,
LastName VARCHAR(50) NOT NULL,
FirstName VARCHAR(50) NOT NULL
);
“`

– **Use Case:** Prevents null data entries in a column, ensuring that important fields (e.g., first and last names) always have a value.

### 5. CHECK
– **Syntax:**
“`sql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
…
CONSTRAINT constraint_name CHECK (condition)
);
“`

– **Description:** The CHECK constraint ensures that all values in a column satisfy a specific condition.

– **Example:**
“`sql
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Salary DECIMAL(10, 2),
CONSTRAINT CHK_Salary CHECK (Salary > 0)
);
“`

– **Use Case:** Used to ensure that values in a column meet specific criteria, such as age limits or salary ranges.

### 6. DEFAULT
– **Syntax:**
“`sql
CREATE TABLE table_name (
column1 datatype DEFAULT default_value,
…
);
“`

– **Description:** The DEFAULT constraint provides a default value for a column when no value is specified during record insertion.

– **Example:**
“`sql
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
CreatedDate DATETIME DEFAULT GETDATE()
);
“`

– **Use Case:** Automatically assigns a default value to a column when no specific value is provided, useful for timestamps, status fields, etc.

### Importance for Data Integrity
Constraints are crucial in maintaining data integrity because they:
– Prevent invalid data entries.
– Enforce relationships between different tables.
– Ensure that data within a table remains consistent and reliable.
– Facilitate the implementation of business rules directly within the database.

By using constraints effectively, database designers can create robust systems that protect against data anomalies and ensure high-quality, reliable data storage solutions.