Data Integrity And Referential Integrity In SQL
In this article let us understand the classification and implementation of integrity in SQL. Integrity in SQL server means something be right, trusty and consistent. The data in database must be right and in good condition.
Data Integrity :
Data integrity means data should be complete, consistent and accurate in database. In data integrity we ensures that each row of table is uniquely identified so we can retrive data separately. To maintain data integrity we need constraints on columns. Constraints means set of rules. Data integrity can be achieved through data types,uniqueness and by referential integrity.
Data Type :
Data type can be used in data integrity by providing proper data type to the column. For example int, varchar, nvarchar, date, datetime, money are data types in SQL.
In above table we can provide proper attribute name and data type to the column. Suppose we accidentally try to enter a employee name into a date field it is not in proper format. If the system enforces data integrity, it will prevent us from making these types of mistakes.
Uniqueness:
Uniqueness can be provided in data integrity by using unique key. If we set unique key to column it uniquely identified a record. Unique does not allow any duplicate values. Unique key is same as primary key but difference is it can allow more than one null values.
How to set unique key?
First open SQL server management studio and create the table. Right click on that table and open design. Select the column which you want to apply unique key. Right click on selected column and open Indexes/keys. Click on add button and set the type as unique Key as shown in below.
Primary Key :
Primary key is a key in a table which identifies the each record uniquely. Primary key is used to locate a record. In primary key null’s should not be allowed, it should be unique and it can not be modified.
How to set primary key?
To create primary key we choose field which can not be changed. So in above Employee table EmpName, DOJ and Salary can be change but EmpId can not change because it is auto-incremented. To make EmpId auto-increment change identity specification. Right click on EmpId and set it as primary key as given below.
Referential Integrity :
Referential integrity shows relationship between two tables. Referential integrity ensures that value in one table refers the existing value in another table. In real world any data we deal with is not stand alone means it has references. For example we have customer and customer can have different addresses like home address, office address etc. Referential integrity can be implemented using primary key and foreign key.
Foreign Key :
Foreign key is key in a one table refers to the primary key in another table. The referenced table is called parent table and table with foreign key is called child table.
How to set foreign key?
To create foreign key we require two tables which shows relationship between them. Lets consider two table as tbl_Employee and tbl_address …
We want to create a relationship between above two table, right click on EmpId column in employee table make it as primary key. Same way set the AddressId as a primary key in address table.
Next go to the address table right click on the addressId and select relationships ->Add ->Tables and Column Specification and select primary key and foreign key table from drop down list.
If you are beginner in SQL read my article SQL Server For Beginners …
Thank You For reading !!
