March 2017 ~ MiTechDev.com

Mitechdev.com is a programming blog which focused on Web Programming and development

Thursday, 9 March 2017

Identity Column in Sql server

21:01:00 Posted by raviteja swayampu , No comments
What is Identity Column:
We can say an Identity column is an auto generating column.If a column is marked as an identity column then the values of the column are automatically generated(It must be of type int).

when you insert a new row into the table. The following,create table statement marks 'Id' as an identity column with seed = 1 and Identity Increment = 1.Seed and Increment values are optional. If you don't specify the identity and seed they both default to 1.
CREATE TABLE TUser
(
  UserID int IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(MAX)
)
Now while inserting values into table TUser we need not to give values for Identity column (UserID) like below.
INSERT INTO TUser(Name) VALUES('RAVITEJA')
INSERT INTO TUser(Name) VALUES('KRISHNA')
INSERT INTO TUser(Name) VALUES('GOPAL')
The values will be inserted into table like below.

Note:Identity column do not allow any user input if we try to insert any thing into Identity column we will get a message like below.


I got a message while tried to execute below statement.
INSERT INTO TUser(Name) VALUES(4,'RAVITEJA')
Msg 110, Level 15, State 1, Line 1
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
So if you mark a column as an Identity column, you dont have to explicitly supply a value for that column when you insert a new row. The value is automatically calculated and provided by SQL server.

Now,delete the row that you have just inserted and insert another row. You see that the value for UserID is 2. Now if you insert another row UserID is 3. A record with UserID = 1, does not exist, and I want to fill this gap. To do this, we should be able to explicitly supply the value for identity column. To explicitly supply a value for identity column we have to turn on the Identity insert on particular column.

SET IDENTITY_INSERT TUSER ON
now we can insert value into column explicitly
INSERT INTO TUser(UserID,Name) VALUES(1,'Joseph')
As long as the Identity_Insert is turned on for a table, you need to explicitly provide the value for that column. If you don't provide the value, you get an error like this..
Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table 'TUser' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
After, the gaps in the identity column filled, and if you wish SQL server to calculate the value, turn off Identity_Insert.
SET IDENTITY_INSERT TUSER ON
If you have deleted all the rows in a table, and you want to reset the identity column value, use DBCC CHECKIDENT command. This command will reset UserID identity column.
syntax:
 DBCC CHECKIDENT(TUser, RESEED, 0)
After execution of this command in sql sever i got below message...
Checking identity information: current identity value '3', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After reinsert values into table Identity column index start with '0'....

Wednesday, 8 March 2017

Check constraint in sql server

21:38:00 Posted by raviteja swayampu , No comments
CHECK constraint is used to limit the range of values that can be entered in a column.If the value is not in the range sql server do not allow to insert the values into table by throwing an error.

Lets say we have an integer AGE column in TUser table.An user age can not be less than zero and can not more than 150.So,the column do not allow negative values and number more than 150.To limit the values we use CHECK constraint on the column.
We can create CHECK constraint graphically or using query.
Syntax for CHECK constraint :
ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME } CHECK ( BOOLEAN_EXPRESSION )
If the BOOLEAN_EXPRESSION returns true, then the CHECK constraint allows the value, otherwise it doesn't. Since, AGE is a nullable column, it's possible to pass null for this column, when inserting a row. When you pass NULL for the AGE column, the boolean expression evaluates to
UNKNOWN, and allows the value.


The following CHECK constraint limits the age between 0 to 100.
ALTER TABLE TUser
ADD CONSTRAINT Check_TUser_1 CHECK(Age>0 AND Age<100)
Note: Here Check_TUser_1 is name of check constraint.
Example:When i try insert a Age value 105 into TUser table sql server displays a message like below.
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__TUser__Age__239E4DCF". The conflict occurred in database "TestDB", table "dbo.TUser", column 'Age'.
The statement has been terminated.

To drop CHECK constraint:
Use below query to drop CHECK constraint from column..
ALTER TABLE TUser
DROP CONSTRAINT CK_TUser_Age

Cascading referential integrity constraint in sql

06:05:00 Posted by raviteja swayampu , No comments
In this tutorial i am going to explain about Cascading referential integrity constraint in sql server.Before going into this article check previous two article in Sql server learning series for beginners.They are as follows,
1.Creating and Working with Tables in sql server
2.Default constraint in Sql server.
Cascading referential integrity constraint 
Cascading referential integrity constraint allows to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys points.

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

1. No Action: This is the default behaviour. No Action 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, an error is raised and the DELETE or UPDATE is rolled back.

2. Cascade: 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.

3. Set NULL: 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 NULL.

4. Set Default: 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.

Lets's check this Cascading referential integrity constraint(1.cascade on delete and cascade on update) using a example..
1.Create two tables and insert some data..
CREATE TABLE EmpMaster
(
EmpId INT PRIMARY KEY,
EmpName VARCHAR(25)
); 

CREATE TABLE EmpDetails
(
EmpId INT FOREIGN KEY REFERENCES EmpMaster(EmpId) 
ON DELETE CASCADE,
DeptId INT PRIMARY KEY,
DeptName VARCHAR(20)
);

---Inserting Records 
insert into EmpMaster(EmpId,EmpName) values(1,'Kim')
insert into EmpMaster(EmpId,EmpName) values(2,'Slaut')
insert into EmpMaster(EmpId,EmpName) values(3,'John')
insert into EmpDetails(EmpId ,DeptId ,DeptName ) values(1,101,'AAA')
insert into EmpDetails(EmpId ,DeptId ,DeptName ) values(2,101,'AAA')
insert into EmpDetails(EmpId ,DeptId ,DeptName ) values(3,103,'CCC')

when i try to execute below statement statement in sql server i got this error like this
delete from EmpMaster where EmpId=3

Error:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__EmpDetail__EmpId__0DAF0CB0". The conflict occurred in database "TestDB", table "dbo.EmpDetails", column 'EmpId'.

The statement has been terminated.

Note: Similarly we also get error while updating the values of EmDetails column EmpId..For this we go for this constraints to eliminate the errors..
 1.Cascading on DELETE:
One of the Foreign Key Constraints uses ON DELETE CASCADE option which may be added after the REFERENCES clause of CREATE TABLE command, as shown here.
CREATE TABLE EmpDetails
(
EmpId INT FOREIGN KEY REFERENCES EmpMaster(EmpId) 
ON DELETE CASCADE,
DeptId INT,
DeptName VARCHAR(20)
)
If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. Cascade deletes all rows containing data involved in the foreign key relationship. Deleting a record in the 'EmpMaster' table, all corresponding Foreign Key records in the Employees table must be deleted.
For example deleting a parent record of EmpId.In EmpDetails (child table) I am not deleting any child records.
delete from EmpMaster where EmpId=1
if i run this query the row with EmpId=1 in EmpDetails also deleted.
mitechdev.com

Note: You can see by using ON DELETE CASCADE on Foreign Key column you can delete the child table implicitly when deleting the parent table. No Error is raised here.

2. Use of ON UPDATE CASCADE:
If the primary key for a record in the 'EmpMaster' table changes, all corresponding records in the 'EmpDetails' table must be updated using a cascading update.
Creating the ON UPDATE CASCADE on Foreign Key Table:
CREATE TABLE EmpDetails
(
EmpId INT FOREIGN KEY REFERENCES EmpMaster(EmpId) 
ON UPDATE CASCADE,
DeptId INT,
DeptName VARCHAR(20)
)
Ex: execute below statement below
Update EmpMaster set EmpId=30 where EmpId=3
mitechdev.com

Tuesday, 7 March 2017

Default Constraint in sql server

07:00:00 Posted by raviteja swayampu , No comments
In previous tutorial of this series we learned about how to create and work(added primary and foreign key constraints to the tables) with tables in sql server..
How to create and work with tables in sql server
In this tutorial we are going to learn how to add DEFAULT constraint to the column in table.And in how many ways we can do that in sql server.before doing that we discuss about what is DEFAULT constraint.
DEFAULT:
A column default 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.

We can add DEFAULT constraint to a table..
1.By altering column using ALTER command or
2.While adding a new column to existing table.

1.Using ALTER:
ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME }
DEFAULT { DEFAULT_VALUE } FOR { EXISTING_COLUMN_NAME 

2.While adding a new column:
ALTER TABLE { TABLE_NAME } 
ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL } 
CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE }
Example:
ALTER TABLE tblPerson
ADD CONSTRAINT DF_tblPerson_GenderId
DEFAULT 1 FOR GenderId
The insert statement below does not provide a value for GenderId column, so the default of 1 will be inserted for this record.
Insert into tblPerson(ID,Name,Email) values(5,'Sam','[email protected]')
On the other hand, the following insert statement will insert NULL, instead of using the default.
Insert into tblPerson(ID,Name,Email,GenderId) values (6,'Dan','[email protected]',NULL)

To drop the constraint from the table's column we can use DROP command
Syntax to drop constraint:
ALTER TABLE { TABLE_NAME } 
DROP CONSTRAINT { CONSTRAINT_NAME }
In next tutorials we will learn more about Sql server...

Creating and Working with Tables in sql server

01:57:00 Posted by raviteja swayampu , No comments
The aim of this article is to create tables with names tblPerson and tblGender and implement  primary key and foreign key constraints on columns of the two tables. In SQL Server, tables can be created graphically in two ways.This tutorials tell us how to create and work with tables.
1.Using SQL Server Management Studio (SSMS) or
2.Using a query(Sql query).

tblGender

tblPerson
To create tblPerson table, graphically, using SQL Server Management Studio
1. Right click on Tables folder in Object explorer window
2. Select New Table
3. Fill Column Name, Data Type and Allow Nulls, as shown below and save the table as tblPerson.

The following statement creates tblGender table, with ID and Gender columns. The following statement creates tblGender table, with ID and Gender columns. ID column, is the 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))
In tblPerson table, GenderID is the foreign key referencing ID column in tblGender table. Foreign key references can be added graphically using SSMS or using a query.

To graphically add a foreign key reference
  • Right click tblPerson table and select Design
  • In the table design window, right click on GenderId column and select Relationships
  • In the Foreign Key Relationships window, click Add button
  • Now expand, in Tables and Column Specification row, by clicking the, + sign
  • Click on the elipses button, that is present in Tables and Column Specification row
  • From the Primary Key Table, dropdownlist, select tblGender
  • Click on the row below, and select ID column
  • From the column on the right hand side, select GenderId
  • Click OK and then click close.
  • Finally save the table.
Using query:
Alter table tblPerson 
add constraint tblPerson_GenderId_FK FOREIGN KEY (GenderId) references tblGender(ID)
Note: Generally we add foreign key to table column using ALTER command.
Alter table ForeignKeyTable add constraint ForeignKeyTable_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 points to a primary key in another table. 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.