Constraints in SQL

-


Hello, and welcome to the blog. In this blog we are going to talk about "Constraints in SQL".

We have learn CRUD operations so far, now its time to advance our skills. We will again create a table with some properties and make our database table more reliable and effective. For making our database table more reliable and effective we are going to add some "Constraints" in our table. 
So in this blog we are going to talk about
  1. What is constraints ?
  2. Types Of Constraint.
  3. Create a table with constraints.
Let's start with

1) What is Constraint?

  1. Constraints are nothing but rules which a data row must satisfy to get inserted in database.
  2. For example: When a person wants to take flight he/she have to go through many checks like security, passport, boarding pass, etc. these checks are there to ensure only valid person can enter flight and flight will be safe from intruders.
  3. The same way constraints are there to make sure only valid data gets inserted into the table.
  4. Constraints also ensures reliability and accuracy of data in database table.
  5. If there is any violation of constraint the whole action of inserting or updating data is aborted.

2) Types Of Constraint.

1) NOT NULL :

  1. This constraint ensures that value in the column is not null. 
  2. If you want a particular column in the table should always have a value then apply NOT NULL constraint to it.

2) UNIQUE :

  1. As the name suggests this constraint is use to ensure unique values in a column.
  2. This constraints make sure that a value is not repeated. To avoid duplicate value if you have to add null value in that column that is okay.

3) CHECK :

  1. It is used to apply specific condition to a column which data should satisfy in order get inserted in database table. This you will understand better in example.

4) DEFAULT :

  1. Use to specify default value for a column
  2. If you don't provide value for a column while inserting or updating data then the value you have specified with default constraint get inserted into table.

5) INDEX :

  1. Index is used to make data retrieval faster. The way book index help us to go to desired page the same way we have INDEX in SQL.
  2. Suppose we have a query:
    SELECT FirstName
    FROM Employee
    WHERE EmployeeId=1
  3. Then your DBMS will read all rows in the table and extracts the rows that meet the criteria of the query.
  4. Checking each row of table need a lot of time.
  5. When we use INDEX, it searches the INDEX key column , finds the storage location of row needed by the query and extract the matching row from location.
Now, let's create a table with some of these constraints:

3) Create a table with constraints

CREATE TABLE [dbo].[Employee1](
[Id] [int] UNIQUE ,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NULL,
[Department] [varchar](50) NULL,
[Salary] [decimal](18, 2) check (salary>10000),
[Designation] [varchar](50) Default 'Trainee'
) 

Now our table is ready, let's try to insert some records

  1. insert into Employee1 values (1,null,'Doe','Sales',12000,'Trainee')

  2. As we are trying to enter a null value in FirstName column we are getting error "Cannot insert the vaalue NULL into column  'FirstName', table 'EmployeeManagementSystem.dbo.Employee1'; column does not allow nulls. INSERT fails."
  3. Now, let's try another insert:
    insert into Employee1 values (1,'Jhon','Doe','Sales',2000,'Trainee')

  4. As we have specify SALARY as 2000 which less than 10000 we are getting error "The INSERT statement conflicted with the CHECK constraint "CK__Employee1__Salar__5CD6CB2B". The conflict occurred in database "EmployeeManagementSystem", table "dbo.Employee1", column 'Salary'."
  5. Now let's see another one: 
    insert into Employee1(Id,FirstName,LastName,Department,Salary)
    values (2,'Jhon','Doe','Sales',12000)

  6. As we have specified DEFAULT VALUE for DESIGNATION the record got inserted without any error and with default value as 'Trainee'.
  7. Now let's try and insert one more record with same Id: 
    insert into Employee1(Id, FirstName,LastName,Department,Salary)
    values (1,'Marie','Krook','HR',15000)

  8. As we have already inserted a record with Id as 1 we are getting this error "Violation of UNIQUE KEY constraint 'UQ__Employee__3214EC06A8EC7CF2'. Cannot insert duplicate key in object 'dbo.Employee1'. The duplicate key value is (1)."
  9. So all the constraint we enforced on our table are working fine.
Congratulation on understanding constraints. See you  in some another blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day"

    Comments

    Post a Comment

    Popular posts from this blog

    SQL Joins | Inner Join | Left Join | Right Join | Full Join | Self Join

    -
    Image
    Hello, and welcome to the blog. In this blog we are going to talk about " Joins ". In the  previous blog  we have seen most important topic of Primary Key, Foreign Key and now its time to join some of the tables. So, In this blog we are going to talk about What is join and its purpose Type Of Joins Inner Join Left Join Right Join Full Join Self Join Let's  start with 1) What is join and its purpose There is a clause in SQL called Join. And its purpose is to combine rows from two or more tables which are connected with each other by a common column. When I say connected with each other by a common column I am talking about Foreign Key which we have covered in previous blog. Although if you haven't enforced foreign key for this blog but we do have a common column then also you can perform jo...
    Read more

    Update, Delete and Truncate Commands

    -
    Image
    Hello, and welcome to the blog. In this blog we are going to talk about "Updating and deleting data". We learn about "Inserting and Retrieving Data" in  this blog . Now its time to learn updating and deleting our mistakes. Yes with the help of the UPDATE and DELETE  statements  you can actually update and delete wrong entries in your database respectively. So in this blog we are going too talk about  UPDATE statement DELETE  statement TRUNCATE command Let's start with 1) UPDATE statement : UPDATE statement is used to update a specific record or a bunch of records which exists in your database table. If you made a mistake while inserting data in your database then UPDATE statement is the loophole through which you can correct it. Let's see a example: If you can see in our table I have made a mistake that SALARY  of "Sales Per...
    Read more

    More clauses in SQL

    -
    Image
    Hello, and welcome to the blog. In this blog we are going to talk about "More clauses in SQL". We learn about "WHERE" clause in this blog . Now its time to learn more clause which are going to help us to be more specific about retrieving data from Database and eventually its gonna make us more and more efficient in handling data in Database. So, In this blog we are going to talk about GROUP BY clause HAVING clause ORDER BY clause Now, let's talk about 1) GROUP BY clause This clause is often used with Aggregate Functions (which we have learnt in previous blog ) so that we can group our result with a particular column. I know it sound little confusing but we will solve this confusion with the help of example. Let's consider a scenario where I told you to count number of people working in each department with our existing Employee table. So then you might write a query like this: select count(*) from Employee where Department='IT' Then you will keep rep...
    Read more