Primary Key, Foreign Key Column and Check Constraint - SQL Server

Ø  What is Primary Key?

              A primary key is a unique identifier for a record or row in a database table. It is a column or set of columns that uniquely identifies each record in a table and serves as a reference point for other tables that need to link to that table's records. Primary keys are used to enforce the integrity of the database by ensuring that each record can be uniquely identified, and they also help to optimize database performance by providing a fast way to retrieve data.

To create a primary key in SQL Server, you can use the CREATE TABLE statement with the PRIMARY KEY constraint. Here's an example:

     CREATE TABLE Customers (

    CustomerID int PRIMARY KEY,

    FirstName varchar(50),

    LastName varchar(50),

    Email varchar(50)

);

In this example, we're creating a table called Customers with four columns. The CustomerID column is declared as an int and is designated as the primary key using the PRIMARY KEY constraint. The FirstName, LastName, and Email columns are declared as varchar data types.

Note that you can also create a primary key using the ALTER TABLE statement. For example:

             ALTER TABLE Customers

ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerID);

 This statement adds a primary key constraint to an existing Customers table, using the ADD CONSTRAINT clause and the PRIMARY KEY keyword. The CustomerID column is specified as the primary key column.

 Ø  What is Foreign Key?           

A foreign key is a column or set of columns in a table that refers to the primary key of another table. It is used to establish a relationship between two tables in a relational database. The purpose of a foreign key is to ensure referential integrity, which means that the data in the related tables remains consistent and accurate.

When a table has a foreign key, it is creating a reference to another table's primary key. This creates a link between the two tables, allowing data to be shared and used in different ways. For example, if you have a table of orders and a table of customers, you could use a foreign key in the orders table to reference the customer ID in the customers table. This would allow you to see which customer placed each order, and to link customer information to the orders as needed.

To add a foreign key in SQL Server, you can use the ALTER TABLE statement with the ADD CONSTRAINT clause. Here's an example:

ALTER TABLE Orders

ADD CONSTRAINT FK_Orders_Customers

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

In this example, we're adding a foreign key constraint to the Orders table, using the ADD CONSTRAINT clause and the FOREIGN KEY keyword. The CustomerID column in the Orders table is specified as the foreign key column, and the Customers table is specified as the referenced table, with its CustomerID column as the referenced column.

Note that you can also specify additional options for the foreign key constraint, such as ON DELETE and ON UPDATE actions. For example:

ALTER TABLE Orders

ADD CONSTRAINT FK_Orders_Customers

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

ON DELETE CASCADE

ON UPDATE CASCADE;

 

In this example, we're adding ON DELETE CASCADE and ON UPDATE CASCADE options to the foreign key constraint. This means that if a customer is deleted or updated, all related orders will also be deleted or updated automatically.

Ø  Check Constraints

A check constraint in SQL Server is used to ensure that the data entered into a column or a set of columns meets a certain condition. It can be used to restrict the range of values that can be entered into a column or to validate data against a specific pattern or formula. Check constraints can be applied to a single column or to multiple columns within a table.

Here's an example of creating a check constraint:

CREATE TABLE Employees

(

    EmployeeID int PRIMARY KEY,

    FirstName varchar(50),

    LastName varchar(50),

    Age int,

    CONSTRAINT CHK_Employees_Age CHECK (Age >= 18 AND Age <= 65)

)

 In this example, we're creating a table called Employees with four columns: EmployeeID, FirstName, LastName, and Age. We've also added a check constraint to the table to ensure that the Age column contains values between 18 and 65.

Once the check constraint is in place, any attempt to insert or update data in the Age column that falls outside the specified range will be rejected with an error message. For example:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age) VALUES (1, 'John', 'Doe', 16);

 This statement will fail with the following error message:

The INSERT statement conflicted with the CHECK constraint "CHK_Employees_Age". The conflict occurred in database "MyDatabase", table "dbo.Employees", column 'Age'.

Note that you can also use check constraints to validate data against other columns within the same table or to perform more complex validations using user-defined functions.