Constraints in SQL Server

SQL constraints are foundational to creating robust and reliable databases. Here's an overview of each:

SQL Constraints

Constraints enforce rules on data in tables, ensuring accuracy and integrity. Common constraints include:

1. NOT NULL

• Purpose: Ensures a column cannot have a NULL value.
• When to Use: Use for columns that must always have a value (e.g., primary keys, user IDs, timestamps).

Example:

CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    Name VARCHAR(50) NOT NULL
);

2. UNIQUE

• Purpose: Ensures all values in a column are unique.
• When to Use: Use for columns like email addresses, usernames, or identification numbers.

Example:

CREATE TABLE Users (
    UserID INT NOT NULL,
    Email VARCHAR(100) UNIQUE
);

3. PRIMARY KEY

• Purpose: Combines NOT NULL and UNIQUE, uniquely identifying each row.
• When to Use: Use for the main identifier of a table (e.g., ID columns).

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL
);

4. FOREIGN KEY

• Purpose: Establishes a relationship between two tables.
• When to Use: Use to maintain referential integrity, linking child tables to parent tables.

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

5. CHECK

• Purpose: Ensures values meet a specific condition.
• When to Use: Use for columns with specific business rules (e.g., age limits, ranges).

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Age INT CHECK (Age >= 18)
);

6. DEFAULT

• Purpose: Assigns a default value if none is provided.
• When to Use: Use for columns that need a consistent default (e.g., statuses, timestamps).

Example:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Status VARCHAR(20) DEFAULT 'In Stock'
);

7. INDEX

• Purpose: Speeds up queries on specific columns.
• When to Use: Use for frequently searched or sorted columns.

Example:

CREATE INDEX idx_lastname ON Employees (LastName);

Choosing the appropriate constraint and type depends on your application's requirements. Constraints ensure data accuracy and integrity, while data types dictate how data is stored and queried efficiently.

Hi, 

Please refer below articles.

https://www.c-sharpcorner.com/article/an-easy-way-to-understand-constraints-in-sql-server/

https://www.c-sharpcorner.com/UploadFile/f0b2ed/constraints-in-sql-server/

They are rules applied to table columns to ensure the validity, integrity, and consistency of the data.

1. NOT NULL Constraint: a column cannot have a NULL value. For example, in an Employees table, the FirstName and LastName fields should not be NULL, as every employee must have a first and last name.

2. UNIQUE Constraint: all values in a column are distinct. you can use it when a column must store unique values, such as an email address in a Users table. 

3. PRIMARY KEY Constraint: combination of NOT NULL and UNIQUE, you can assign it to a column or a set of columns that serve as the unique identifier for table records, like EmployeeID in an Employees table. Each table should have one primary key.

4. FOREIGN KEY Constraint: establishes a relationship between columns in two tables, enforcing referential integrity. In other words we use it to link related data between tables. An Orders table may have a CustomerID column that references the CustomerID in a Customers table to make sure that every order is associated with a valid customer.

5. CHECK Constraint: you can make sure that all values in a column satisfy a specific condition.

   For example, a Salary column can have a CHECK constraint to be  within a reasonable range, such as greater than zero.

6. DEFAULT Constraint: provides a default value for a column when none is specified. For example, you can set up a default value of GETDATE() for an OrderDate column to record the date an order was placed.

• SQL Constraints - W3Schools
• SQL Constraints - GeeksforGeeks
• SQL Constraints - SQL Tutorial