SQL Constraints 

  • It used to specify the rules for the data in a table.

  • If we try to insert some data in the table that is not allowed by the constraint that time this action will be aborted.

  • There are two ways to specify the constraints to column  

  1. At the time of creating a table using the create table command.

  2. After table creation using alter table command.


Primary Key Constraint 

  • Single primary key column present in each table.

  • primary key column not allowed to insert Null and duplicate value into table.


Now we are creating  tblperson

Creating table with primary key using column level constraint

Create table tblperson (id Int Primary key,name varchar(50),email varchar(50))

Creating table with primary key using table level constraint

create table tblpersons (id int,empname varchar(50),email varchar(50), Constraint pk_tblPerson_id Primary key(id))

 

 

Insert data 

insert into tblPerson values(1,'Chaitanya Patil','[email protected]',1)

insert into tblPerson values(1,'Chaitanya Patil','[email protected]’)//Violation of PRIMARY KEY constraint 'PK__tblperso__3213E83F84024D43'. Cannot insert duplicate key in object 'dbo.tblperson'. The duplicate key value is (1).

 

insert into tblPerson values(null,'Chaitanya Patil','[email protected]')//Cannot insert the value NULL into column 'id', table 'pratikshadb.dbo.tblperson'; column does not allow nulls. INSERT fails.



Adding Primary Key with Alter Command:


  • After creating a table I want to add a primary key constraint to the table using the alter command but it is not a good practice.

  • Why is it not good practice?

           1. Whenever we create a new table without adding any constraints that time By default every column is nullable.when we perform an alter command we will get an error below.


Alter table tblPerson add CONSTRAINT pk_emp_id PRIMARY key (EID)//Cannot define PRIMARY KEY constraint on nullable column in table 'employee1'.

 

So first we need to drop null constraint for that column

Alter table employee1 alter column EID Int Not Null

After that we need to perform add constraint command

alter table employee1 add CONSTRAINT PK_ID PRIMARY Key (EID)

 

note: It will work if the table is empty with columns not nullable or null and duplicate values not presented in the column.

      

            2.  If already data present in column and data is null and duplicate then it is not possible to add a primary key. If you really want to add it then we need to all null and duplicate records and then need to add.

 

Removing Primary Key Constraint

  • When we must do it, we remove the primary key from the table.


alter table employee1 drop CONSTRAINT PK_ID


Categories: SQL Server Tags: #SQL Server,

Comments