Alter command with constraints

-


Hello, and welcome to the blog. In this blog we are going to talk about "Alter command with constraints".

In the previous blog we have seen how to create a table with constraint. If you are following the blog we already have a table "Employee", what if we have to apply constraints to that table. Don't worry "ALTER" command is there to help us.

So, In this blog we are going to talk about
  1. Adding Constraints
    1. Adding null/ not null constraint
    2. Adding UNIQUE constraint
    3. Adding CHECK constraint
    4. Adding Default constraint
    5. Adding Index
  2. Knowing Constraints
  3. Renaming Constraints
  4. Removing Constraints
Let's begin with,

1) Adding Constraints

  1. When we initially started, we created a simple table without any constraints here. So we are going to add constraint in that table.
  2. "ALTER" command is used to add constraint to existing table.
  3. For most of the constraint syntax is pretty simple and same.
  4. Let's see syntax first then we will jump to examples:
    ALTER TABLE table_name
    ADD CONSTRAINT constraint_name CONSTRAINT(column_name)
  5. With ALTER command you can give some logical name to a constraint. for example: In previous blog when we got errors like "The INSERT statement conflicted with the CHECK constraint "CK__Employee1__Salar__5CD6CB2B"". In this statement "CK__Employee1__Salar__5CD6CB2B" is actually a constraint name
  6. The name is complex and difficult to remember, so alter command give us to flexibility to name constraints correctly we can alter it with some logical and simple name.

1. Adding null/ not null constraint

  1. We are discussing NOT NULL constraint first as it is quite different from others syntactically.
  2. So let's make FirstName  column of table NOT NULL:
    ALTER TABLE Employee
    ALTER COLUMN FirstName varchar(50) NOT NULL

  3. Before adding this constraint you must make sure that, there is no null values in that column, you must update all null values of the column with some values then only you can enforce "not null" constraint.
  4. Otherwise you will get an error "The statement has been terminated".

2. Adding UNIQUE constraint

  1. As we know the purpose of this constraint let's jump to example:
    ALTER TABLE Employee
    ADD CONSTRAINT UNI_Id UNIQUE(Id)

  2. UNI_Id is the constraint name. I personally like to use UNI as prefix for naming UNIQUE constraint you can use prefix of your choice.

3. Adding CHECK constraint

  1. ALTER TABLE Employee
    ADD CONSTRAINT CHK_Sal CHECK(Salary>10000)

  2. Before running this ALTER command make sure SALARY column of your table does not contain any value which is less than 10000
  3. CHK_Sal is constraint name here. I personally like to use CHK as prefix for naming CHECK constraint you can use prefix of your choice.

4. Adding DEFAULT constraint

  1. ALTER TABLE Employee
    ADD CONSTRAINT DF_Des DEFAULT 'Trainee' for Designation

  2. DF_Des is the constraint name. I personally like to use DF as prefix for naming CHECK constraint you can use prefix of your choice.
  3. Syntactically it may seems to be different but still easy to understand and you don't have to memorize syntax that you can always google it, more important thing is to understand concept.

5. Adding INDEX constraint

  1. Syntactically adding INDEX constraints is different:
    CREATE INDEX index_name
    ON table_name (column_name)
  2. Let's see a example to understand it better:
    CREATE INDEX idx_FirstName
    ON Employee (FirstName)

Now let's check our constraints are working or not with some insert statements
  1. insert into Employee values (1,null,'Doe','Sales',12000,'Trainee')

  2. As in previous blog we got error we got it here again"Cannot insert the value NULL into column 'FirstName', table 'EmployeeManagementSystem.dbo.Employee'; column does not allow nulls. INSERT fails."
  3. insert into Employee values (15,'Jhon','Doe','Sales',2000,'Trainee')

  4. We got error saying "The INSERT statement conflicted with the CHECK constraint "CHK_Sal". The conflict occurred in database "EmployeeManagementSystem", table "dbo.Employee", column 'Salary'."
  5. insert into Employee(Id,FirstName,LastName,Department,Salary)
    values (22,'John','Doe','Sales',12000)

  6. We got a record with default designation as "Trainee".
  7. insert into Employee(Id, FirstName,LastName,Department,Salary)
    values (1,'Marie','Krook','HR',15000)

  8. We got a error saying "Violation of UNIQUE KEY constraint 'UNI_Id'. Cannot insert duplicate key in object 'dbo.Employee'. The duplicate key value is (1)."
Well done. Our constraints are working fine and if you have observed the name of constraints are better now. We learn adding constraints now what if we forget what constraints we have applied to a table, as in real life we have n number of table. Don't worry that what we are going to learn next.

2) Knowing Constraints:

To know constraints you can simply run following query :
SELECT constraint_name, constraint_type
FROM Information_schema.TABLE_CONSTRAINTS
WHERE table_name='Employee'


Note: Replace Employee with your table name in query

3) Renaming constraints

  1. Yes we can even rename constraint as well.
  2. Here's syntax:
    exec sp_rename 'existing_constraint_name','new_constraint_name'
  3. let's see a example:
     exec sp_rename 'CK__Employee1__Salar__5CD6CB2B','CHK_SAL'

  4. Renaming constraint will give you a warning you should not worry about. As a programmer we only worry about errors not warnings.
Now the final part,

4) Removing constraints

  1. Usually constraints are removed when there is change in policies. For example if you decide the minimum salary will be 12000 not 10000 then you must remove check constraint and then again add constraint with updated value.
  2. Why? Can't we update existing constraint?
  3. No, while I am publishing this blog there is no such facility. So in order to update a constraint you have to remove it first and then add constraint with updated value.
  4. Now, let's see syntax :
    ALTER TABLE table_name DROP CONSTRAINT constraint_name
  5. Example:
    ALTER TABLE Employee DROP CONSTRAINT chk_sal
  6. Wait we are not done yet as there is difference in deleting INDEX constraint
  7. So here is how it is done:
    DROP INDEX table_name.index_name
  8. Example:
    DROP INDEX Employee.idx_FirstName
Note: You cannot delete null and not null constraint you can just change them not null and null respectively.

Congratulation on understanding how to add constraints to existing table. There are more constraints that we will see in next blog. 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