Cascading referential integrity constraint in Sql Server

Hi friends! In our previous sessions we have seen how to create tables and enforce primary and foreign key constraints between them. And in our last session we learnt adding a default constraint in SQL Server.

Now, Today we are going to learn Cascading referential integrity constraint in SQL Server

SQL Server defines many cascading referential integrity constraints to choose from. Cascading referential integrity constraints allow you to define the actions SQL Server should take when a user tries to delete or update a key for which foreign keys exist in the database.

Cascading is used with the drop command. Suppose, we want to drop a record from the parent table, even when we have the corresponding records in the child table or we want to drop a parent table, even when a child table exists, then we use Cascading referential integrity constraints.

If you execute a delete command without a cascading constraint, then it will throw an error.

So let's take a look at a practical example of how to use a SQL Cascading Constraint in SQL Server. The example is developed in SQL Server 2016 using the SQL Server Management Studio.

Firstly, we will create two tables named Category and SubCategory.

Category Table:
Here, I have created a Table named as Category which has three columns named Id, Name, Description. Set the identity and Primary Key properties of the Id as follows:

CREATE TABLE [dbo].[Category](
           [Id] [int] IDENTITY(1,1) NOT NULL,
           [Name] [varchar](200) NULL,
           [Description] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
           [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The Category table looks as in the following:

Category Table Structure

SubCategory Table:
Here, I have created a Table named SubCategory which has four columns named Id, CategoryId, Name and Description. Set the identity and Referential Integrity properties of the Id as follows.

CREATE TABLE [dbo].[SubCategory](
           [Id] [int] IDENTITY(1,1) NOT NULL,
           [CategoryId] [int] NOT NULL,
           [Name] [varchar](200) NULL,
           [Description] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_SubCategory] PRIMARY KEY CLUSTERED
(
           [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SubCategory]  WITH CHECK ADD  CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[ Category] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SubCategory] CHECK CONSTRAINT [FK_SubCategory_Category]
GO

Table SubCategory looks as in the following:

Now, let’s try to delete our Category which defines the Foreign Key Constraints.
We could see that, it displays the following error:

Delete from Category where Id='21'

Error while deleting a Primary Key which is used as a Foreign Key in another table

Cascading Referential Integrity Constraints
Now use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table:

CREATE TABLE [dbo].[SubCategory](
           [Id] [int] IDENTITY(1,1) NOT NULL,
           [CategoryId] [int] NOT NULL,
           [Name] [varchar](200) NULL,
           [Description] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_SubCategory] PRIMARY KEY CLUSTERED
(
           [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SubCategory]  WITH CHECK ADD  CONSTRAINT [FK_SubCategory_Category] FOREIGNKEY([CategoryId])
REFERENCES [dbo].[Category] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SubCategory] CHECK CONSTRAINT [FK_SubCategory_Category]
GO

Now we delete a row from the major table:

delete from Category where Id='21'

This statement will successfully delete your Category with Id=21

However, you have the following options when setting up Cascading referential integrity constraint

1. No Action: This is the default behaviour of SQL Server. No Action specifies that you try to DELETE or UPDATE a row with a key that is referenced by the foreign keys in other tables, then an error is raised by  SQL Server and the DELETE or UPDATE query is rolled back.

2. Cascade: It specifies that if an attempt is made to DELETE or UPDATE a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also DELETED or UPDATED in both the tables.

3. Set NULL: It specifies that if an attempt is made to DELETE or UPDATE a row with a key referenced by foreign keys in existing rows in other tables, then all rows containing those foreign keys are set to NULL.

4. Set Default: It specifies that if an attempt is made to DELETE or UPDATE a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values(if a default constraint is defined in that table).

If you need help in how to create a default constraint in SQL Server table then please refer this link, click here.

Summary:
So, Guys this is all about Cascading referential integrity constraint in SQL Server.
I Hope in this post covered all the points about Cascading referential integrity which will be helpful to understand the concept how useful Foreign keys are.

Please share this post with your friends and colleagues.
For any queries please post a comment below.

Happy Coding 😉

Read More

Default constraint in Sql Server

Hi friends! In our last session we learned how to create tables (tblPerson and tblGender) and we made a relation between them using foreign key constraints.

This is the structure of the previous tables that we have worked on.

In this post, we will be learning how to add a Default Constraint into a table in SQL Server.

A column default value can be specified using Default constraint. The Default Constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified, including NULL, i.e if we explicitly provide NULL for any column then Default Constraint will not work.

Here is the General Syntax for Altering an existing column to add a default constraint:

ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME }
DEFAULT { DEFAULT_VALUE } FOR { EXISTING_COLUMN_NAME }

Here is the Syntax for Adding a new column, with default value, to an existing table:

ALTER TABLE { TABLE_NAME } 
ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL } 
CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE }

This command will add a default constraint, DF_tblPerson_GenderId.

ALTER TABLE tblPerson
ADD CONSTRAINT DF_tblPerson_GenderId
DEFAULT 1 FOR GenderId

Now, let’s test our query.
This insert statement below does not provide a value for GenderId column, so the Default Constraint should work and set its default value of 1 will be inserted in this record.

Insert into tblPerson(ID,Name,Email) values(4,'XYZ','x@x.com')

Now Select your rows from the table using this statement

Select * from tblPerson

Now, you could see that the Default Constraint has inserted a default value of in the table.
Let’s try by passing NULL value. The following Insert Statement will insert NULL, instead of using the default.

Insert into tblPerson(ID,Name,Email,GenderId) values (5,'ABC','a@a.com',NULL)

Finally, If you want to drop any constraint from your table, use this statement.

ALTER TABLE { TABLE_NAME } 
DROP CONSTRAINT { CONSTRAINT_NAME }

Summary:
So, Guys this is all about Default constraint in Sql Server
I Hope in this post covered all the points about how to pass a default value into the table if any value is not specified for that, which will be helpful to understand the concept Default constraint

Please share this post with your friends and colleagues.
For any queries please post a comment below.

Happy Coding 😉

Read More

Creating and Working with tables in Sql Server

Hi friends! In our last post we have seen How to create Database and perform Alter, Delete/ Drop operation on databases in SQL Server.

Today, We are going to learn how to create a Tables in SQL Server and implementing Foreign Key Constraint in our table, also the benefits of Foreign key in our Database.

The main aim of this post is to teach how to make Tables in SQL Server and establish a relationships between 2 tables using Primary Key and Foreign Key Relation.

Table Structure

There are 2 ways to make Tables as similar to Databases in SQL Server.
To know how to make Databases in SQL Server please go through my previous post

• Creating and Working with Database.

There are 2 ways to make Tables in SQL Server.

1. Graphically
2. using a Query

First Let's discuss how to make Table in SQL Server Graphically.

We need to follow some steps to make a Table.

1. Firstly connect to the SQL Server Management Studio(SSMS). Need help? click here
2. Expand Databases folder by clicking on "+" icon on the left side.
3. Now, Right click on Tables folder in Object explorer window and
4. Click on New Table option.
5. Fill Column Name, Data Type and Allow Nulls(if you want to pass NULL value as default), as shown below and save the table as tblPerson. 

Table Design of tblPerson

Following steps creates a table with the name tblPerson with 4 columns.
Now, Let’s make another table using a Query in SQL Server.
The general syntax to create a table in SQL Server is

Create Table TableName (Column1Name Datatype(size) Contraint if any, Column2Name Datatype(size), so on..)

So, Let’s make a table with the name tblGender, with 2 Columns ID and Gender respectively.
ID column is Primary Key Column.

The primary key is used to uniquely identify each row in a table.Primary key does not allow nulls.

Create Table tblGender(ID int Not Null Primary Key,Gender nvarchar(50))

Now, Let's add the reference between both the tables using Foreign Key relation.
In tblPerson table, GenderID is the foreign key referencing ID column in tblGenderTable.
Foreign key references also can be added in two ways i.e. graphically using SSMS and using a query as well.
To graphically add a foreign key reference in SQL Server we need to follow some steps as follows:

1. Right click on tblPerson table in Tables Folder and select Design option.
2. In the table design window, right click on GenderId column and select Relationships option.

   

   Selecting Relationship Option from Context Menu
3. In the Foreign Key Relationships window, click on Add button as shown in picture.

   

   Adding a New Relation

4. Now expand, in Tables and Column Specification row, by clicking the, ">" sign
   
5. Click on the elipses ("...") button, that is present in Tables and Column Specification row
   
6. From the Primary Key Table dropdown, select tblGender
   
7. Click on the row below, and select ID column
   
8. From the column on the right hand side, select GenderId
9. Click OK and then click close.
   
10. Finally save the table.
    
11. You will get a Confirmation Message if you create a Relationship after Table Creation, then click on Ok.
    

Here, is the another way to reference Foreign key Relation using a query.

Alter table tblPerson 
add constraint tblPerson_GenderId_FK FOREIGN KEY (GenderId) references tblGender(ID)

General Syntax of Foreign Key Constraint is

Alter table ForeignKeyTable add constraintForeignKeyTable_ForiegnKeyColumn_FK 
FOREIGN KEY (ForiegnKeyColumn) references PrimaryKeyTable (PrimaryKeyColumn)

Foreign keys are used to enforce database integrity. In layman's terms, A foreign key in one table references to a primary key in another table.

At last I need to discuss why is it so important to use Foreign Key in our Database? 

The foreign key constraint prevents invalid data form being inserted into the foreign key column. The values that you enter into the foreign key column, has to be one of the values contained in the table it points to.

Summary:
So, Guys this is all about Creating ad working with Tables in SQL Server.
I Hope in this post covered all the points about Table Creation and Foreign Key Reference which will be helpful to understand the clear concept of Foreign Key, Why do we use it?

Please share this post with your friends and colleagues.
For any queries please post a comment below.

Happy Coding 😉

Read More

Creating and Working with Database

Hi friends! In our last post we have seen different approaches how we can connect with SQL Server Management Studio(SSMS).

Today, We are going to learn how to create a database and perform tasks on database such as Creating, Altering and Dropping our Database.

So, Let's start with Creating a Database.
We can Create, Alter or Drop a database with 2 methods. Which are

1. Graphically using SQL Server Management Studio (SSMS)
2. Using a Query

Let's describe each method one-by-one.
Firstly, to create a database graphically we have to follow some steps

1. Expand the Database Server by clicking on "+" icon as shown in the picture.

   

   Database Server

2. Right Click on Databases folder in the Object Explorer
   
3. Click on the New Database option
4. You will be prompted to enter name of New Database and user to make it as the owner.
5. In the New Database dialog box, enter the Database name (MyFirstDatabase).

   

   Adding name to database

6. If You want to change the owner of Database click on the button with ... icon, for default settings we keep it untouched.
7. Click OK.
8. Now if you Refresh the databases folder, you can see your Database is created.

   

   New database created

Now, Let's discuss the second method to create a database using a query.
Query to Create Database is:

Create database DatabaseName

Now, Let's find out the background process, what actually is getting executed behind the scenes.
So, Whenever you create a database either graphically or with the help of a Query, the following 2 files gets generated.

• .MDF file - Data File (Contains actual data) //usually large in size
• .LDF file - Transaction Log file (Used to recover the database)  // small in size

Now, lets start with the operation on database. To Alter database which we have already created

Alter database DatabaseName Modify Name = DatabaseName

Alternate way to achieve this task is, you can also use system stored procedure 'sp_renameDB'

Execute sp_renameDB 'OldDatabaseName','DatabaseName'

To Delete or Drop an existing database, we can use Drop Statement

Drop Database DatabaseThatYouWantToDrop

Dropping a database, deletes the LDF and MDF files.

If any database is already in use then we cannot perform drop or delete command over it.
You get an error stating - Cannot drop database "DatabaseThatYouWantToDrop" because it is currently in use.
If you still want to delete that type of databases from your server then we have an approach in SQL Server to put that Database in Single User Mode and then we can perform the drop command execution.

To bring database in Single User Mode

Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate

Rollback Immediate option will rollback all the pending Transaction and closes all the connections to the database.

System databases cannot be dropped.

Summary:
So, Guys this is all about Creating and Working with Database

I Hope in this post covered all the points about how we can make a database and perform Alter, Drop and Delete commands on Databases, which will be helpful to understand the concept how SQL Server works.

Please share this post with your friends and colleagues.
For any queries please post a comment below.

Happy Coding 😉

Read More

Connecting to SQL Server using SSMS

Introduction
Hi friends! In this blog we will be discussing How to connect to the SQL Server using SQL Server Management Studio (SSMS).

Background
SQL Server Management Studio (SSMS), is the client tool that can be used to connect to the Database engine and write/execute SQL queries.

Process to Connect to SSMS
Method 1:
To connect to the SQL Server Management Studio

1. Click Start
2. Select All Programs
3. Select Microsoft SQL Server 2005, 2008, 2008 R2, 2012 or 2016(Depending on the version installed)
4. Select SQL Server Management Studio

SSMS in All Programs

Method 2:
To connect to the SQL Server Management Studio

1. Click Start
2. Type "SSMS" without quotes("")
3. Select SQL Server Management Studio

SSMS using Search

Method 3:
To connect to the SQL Server Management Studio

1. Click Start
2. Type "run" or press Window + R.
3. Type "SSMS" without quotes("")
4. Hit Enter.

Run Command to open SSMS

You will now see, Connect to Server window.

Sql Server Management Studio

1. Select Database Engine as the Server Type. The other options that you will see here are Analysis Services(SSAS), Reporting Services (SSRS) and Integration Services(SSIS).
   Server type = Database Engine 
2. Next you need to specify the Server Name. Here we can specify the name or the server or IP Address.If you have SQL Server installed on your local machine, you can specify, (local) or just . (dot) or any other name that you have specified while installing SQL Server
   Server name = (local) / . / othername
3. Now select Authentication. The options available here, depends on how you have installed SQL Server. During installation, if you have chosen mixed mode authentication, you will have both Windows Authentication and SQL Server Authentication. Otherwise, you will just be able to connect using windows authentication.
4. If you have chosen Windows Authentication, you don't have to enter user name and password.
5. If you have chosen SQL Server Authentication, you have to enter user name and password to connect to your database.

Now you are successfully connected with the Sql Server as you can see in the below picture.

Start writing Sql Queries

Now, click on New Query, on the top left hand corner of SSMS (highlighted in the about picture). This should open a new query editor window, where we can type SQL queries and execute.

Summary:
SSMS is a client tool and not the Server by itself. Usually database server (SQL Server), will be on a dedicated machine, and developers connect to the server using SSMS from their respective local (development) computers.

So, Guys this is how we Connect to SQL Server using SSMS.
I Hope this post will be helpful to understand Connecting to SQL Server using SSMS.

Please share this post with your friends and colleagues to help them.

For any queries please post a comment below.

Happy Coding 😉

Read More