SQL Server - How to create Foreign Key constraints?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.

The basic fundamental is that A Not null constraint restrict the insertion of null values into a column. 

Foreign Key Constraint is another feature of SQL which is also known as referential integrity constraint and responsible to maintain the integrity relationship between two tables. Hence, a Foreign Key is a field in a data table that is a Primary key in another table. A table can have more than one foreign key and can have multiple referential relationships to more than one table also.

They cannot accept null but can have multiple duplicate data as well.

How can we create Foreign Key constraints?

A table can have more than one foreign key and we can create this constraint at two levels -

Column Level Foreign Key Constraints - We can define Foreign Key constraint with CREATE TABLE statement or in time with table definition. 

For example, the below query creates a table Employee with the column fields EmpId with Primary Key constraint, FirstName, LastName and Age as NOT NULL but MobileNo column with Unique constrain, IsActive column with Default constraint and DeptId with Foreign Key Constraint references Department table at DeptId column . Thus, Age column has some specific check constraint to validate the data before inserting into the data table.

---- Column Level Primary Key Constraint CREATE TABLE TBL_DEPARTMENT ( DeptId int NOT NULL Primary Key, DeptName varchar(25) NOT NULL, IsActive Bit Default(1) ) ---- Insert values into table Insert Into TBL_DEPARTMENT (DeptId, DeptName) Values(101,’Sales & Marketing’) ---- Column Level Foreign Key Constraint CREATE TABLE TBL_EMPLOYEE ( EmpId int NOT NULL Primary Key, FirstName varchar(25) NOT NULL, LastName varchar(25) NOT NULL, Age int NOT NULL CHECK(Age>=18), MobileNo Bigint Unique, IsActive Bit Default(1), DeptId int Foreign Key References Tbl_Department(DeptId) ) ---- Insert values into table INSERT INTO TBL_EMPLOYEE (EmpId, FirstName, LastName, Age, MobileNo,DeptId) VALUE (101,’Ryan’,’Arjun’,22,7856124580,101)

Keep in your mind that if you are not going to assign a name for primary key constraint, Foreign Key Constraint, unique constraint, check constraint and default constraint then system/SQL server will generate system defined constraint names for the primary key, Foreign Key, unique, check and default constraint column.

Table Level Foreign Key Constraints -

You can create a Foreign Key constraint after creating the table with the help of Alter Table command as given below.

---- Table Level - Foreign Key being added to another column ALTER TABLE TBL_EMPLOYEE ADD Constraint  FK_Emp_DeptId Foreign Key (DeptId) References Tbl_Department(DeptId) ---- drop Foreign Key Constraint ALTER TABLE TBL_EMPLOYEE Drop Constraint  FK_Emp_DeptId

What happen, if you have some data into your table?

If you have some data into your tables, for example employee table is in relation with department table with the help of deptId foreign key –

1. If you want to delete some records from department table and these records are referenced to employee table then SQL Server will not allow this operation and will through errors.
2. If you want to delete some records from the department table and these records are referenced to employee table then you should remove all the referenced data from the employee table. After that, you can remove these records from department table
3. If you want to drop department table containing referenced to employee table based on deptId foreign key, then you have to forcefully remove this relationship from employee table by dropping the foreign key deptId.
4. You cannot insert null value for a foreign key column because it would be primary key in the another table and primary key never support null values.

To watch a live demo, please visit our YouTube Channel -

SQL Server - How does Default Constraint Work?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.

The basic funda is that A Not null constraint restrict the insertion of null values into a column. 
Default Constraint is another feature of SQL which ensures to provide a default value for the fields. It specifies a default value in case an insertion query any value is not specified for this column then the default value will be inserted into the column.

How can we create Check constraints?

In a table one or more column can contain a Default Constraint. We can create Default constraints at two levels -

Column Level Default Constraints - We can define Default constraint with CREATE TABLE statement or in time with table definition.  

For example, the below query creates a table Employee with the column fields EmpId, FirstName, LastName and Age as NOT NULL but MobileNo column with Unique constraint and IsActive column with Default value. Thus, Age column has some specific check constraint to validate the data before inserting into the data table.

---- Column Level Unique Constraints CREATE TABLE TBL_EMPLOYEE ( EmpId int NOT NULL, FirstName varchar(25) NOT NULL, LastName varchar(25) NOT NULL, Age int NOT NULL CHECK(Age>=18), MobileNo Bigint Unique, IsActive Bit Default(1) ) ---- Insert values into table INSERT INTO TBL_EMPLOYEE (EmpId, FirstName, LastName, Age, MobileNo) VALUE (101,’Ryan’,’Arjun’,22,7856124580)

Keep in your mind that if you are not going to assign a name for unique constraint, check constraint and default constraint then system/SQL server will generate static constraint names for the unique, check and default constraint column.

Table Level Default Constraints -

You can create a default constraint after creating the table with the help of Alter
Table command as given below.

---- Table Level - Default being added to another column ALTER TABLE TBL_EMPLOYEE ADD Sex Char(1) Constraint  DF_Emp_Sex Default(‘M’) ---- drop Default Constraint ALTER TABLE TBL_EMPLOYEE Drop Constraint  DF_Emp_Sex ---- Table Level - Default being added to existing column ALTER TABLE TBL_EMPLOYEE ADD Constraint  DF_Emp_Sex Default(‘M’) For Sex

What happen,if you have some data into your table?

If you have some data into your table, for example in Sex field you have data such as Null and you want to apply default constraint on this column. Then you can create Table level default constraint but system will not update the existing data and the existing data in this column will remain as Is.

In this case, SQL will allow this constraint to your column and will be provide the default
values for new incoming data if that value is not specified in the insert query.

To Watch a demo, please visit our YouTube channel - 

SQL Server - How to Apply Check Constraints?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.

The basic funda is that A Not null constraint restrict the insertion of null values into a column. 

Check Constraint - It is another feature of SQL which ensures that column value must be matched with some specific conditions and ensures that provided value passes all the Check constraints before storing it into data table otherwise the data for insertion will be rejected.

How can we create Check constraints?

In a table one or more column can contain a Check Constraint. We can create Check constraints at two levels -

• Column Level Check Constraints - We can define Check constraint with CREATE TABLE statement or in time with table definition. 

For example, the below query creates a table Employee with the column fields EmpId, FirstName, LastName and Age as NOT NULL but MobileNo column with Unique constraint. Thus, Age column has some specific check constraint to validate the data before inserting into the data table.

---- Column Level Unique Constraints CREATE TABLE TBL_EMPLOYEE ( EmpId int NOT NULL, FirstName varchar(25) NOT NULL, LastName varchar(25) NOT NULL, Age int NOT NULL CHECK(Age>=18), MobileNo Bigint Unique ) ---- Insert values into table INSERT INTO TBL_EMPLOYEE (EmpId, FirstName, LastName, Age, MobileNo) VALUE (101,’Ryan’,’Arjun’,22,7856124580)

Keep in your mind that if you are not going to assign a name for unique constraint and check constraint then system/SQL server will generate static constraint names for the unique and check constraint column. 

• Table Level Check Constraints -

You can create a Check constraint after creating the table with the help of Alter Table command as given below-

---- Table Level - Unique being added to another column ALTER TABLE TBL_EMPLOYEE ADD Age Int Constraint  CK_Emp_Age CHECK(Age>=18) ---- drop Check Constraint ALTER TABLE TBL_EMPLOYEE Drop Constraint  CK_Emp_Age

What happen, if you have some data into your table?

If you have some data into your table, for example in Age field you have value such as 16 and you want to apply check constraint on this Age column. Then you can create Table level Check constraint but you cannot avoid this value means you cannot say to your Age column that Age>=18 so on.

In this case, SQL will not allow this constraint to your column because this condition is not going to satisfy your existing data.

Demo - Class, please visit us at YouTube -