Good Morning friends..Today we will learn few more properties of tables and other objects related to tables. In this article we will talk about following properties of tables and other objects related to tables.
1)Foreign Key
2)Check Constraint
3)Unique Key Constraint
4)Computed Column
Foreign Key:- A foreign key in a table can be defined as a key in a table that has a reference to another column in a different table. It means that the values in the foreign key column should have same values as primary key column has. Lets take an example of foreign key.
Note:- For creating foreign key we can refer with Primary key column or unique key column.
I have two table Employee and Dept in my database. Lets create a primary key on Employee table first.
To create primary key on table Employee, just expand the Tables folder tree view and select the Employee table, right click on it and click on design menu. once you click on design menu, the below window will open in front of you.
Once you have opened the design mode of employee table, right click on the column that you want to make as primary key, a menu will pop up with an option "set primary key". Select that option and click on the save button(Floppy icon). Now we have created a primary key on the table employee. Lets create a foreign key on table Dept.
We will open the Dept table in design mode as we did with employee table and right click on the EID column of Dept table as shown in the below image.
We will click on "Relationships" menu and a new window will come up.
Now click on add button and see what happens.
In the right side of the dialogue box, if we select "Tables and Columns Specification" caption, a small button will appear in front of that caption. Please refer the below image.
Click on the small button which is appeared after selecting the "tables and columns specifications" caption.A new window will appear. Please see below image for your reference.
We have to give a name to the foreign key constraint, You can see that "Fk_Dept_Dept" name has given by SQL server to the foreign key constraint. We can change it, if we want to change. In the above dialogue box there is one drop down list at the left side, from where we can choose the primary key table, We want to refer Employee table, hence we will select employee table in the drop down list.
Once we select the Employee table in the drop down list, there is also a drop down list underneath to the above drop down list where we selected the "Employee" table. In this drop down list we will have all columns of the Employee table. we will select "EID" column in this drop down list. At the right side of the dialogue box and underneath the "Dept" table field there is also a drop down list where all of the columns of dept table are present. We will select "EID" column as we want to make "EID" column of Dept table as a foreign key which will reference to "EID" column of Employee table.
Clicking on "Ok" button will take us on the below screen. Now click on close button and click on the save button(Floppy icon). Now foreign key is created on table Dept and foreign key column is EID.
Lets try to add one row in dept table first having EID value=70, which is not present in Employee table and see what happens.
If we try to insert a row which is having EID value that is not already present in the Employee table, we will be getting the error message that is shown in the above picture. Lets insert EID 70 in the Employee table first and then in Dept table.
Now we are able to insert the record having EID value 70 in the Dept table.
Check Constraint :- Check constraint is used as check point before inserting a value into a particular column. Lets take an example to have a better understanding of check constraint.
Create table Customer(CustId Int,CustName varchar(100),Earning Numeric(10,2) check (Earning>10))
I have created a table Customer having three columns in it and third column of Customer is having Check constraint defined on it. I have put a condition that Earning >10 , it means that this column will check the column value before inserting the row, if the value is <=10 then , it will throw an error message, otherwise it will allow the row that is having Earning >10.
In the above picture, you can see that I am trying to insert a value 10 which resulted into an error message.
Unique Key Constraint:- Unique key constraint is quite similar to Primary key constraint, It can uniquely identify the each row of the table and can also used a reference to a foreign key constraint. Unique key has below two properties which are different from Primary key.
1) While creating primary key, it creates clustered index on the key column, on the other hand Unique key constraint create a non clustered index on the key column.
2)Primary key column doesn't allow a null value in it, while Unique key allow one Null value in the key column.
Create table Customer(CustId Int unique ,CustName varchar(100),Earning Numeric(10,2))
You just need to write Unique key word in front of the column that you want to make as Unique key.
4)Computed Column:- Computed column is also known as derived column. Computed column can be defined as a column which is result of some calculation based on requirement. We need not to explicitly insert a value in computed column. It is populated automatically based on the formula definition. Lets take an example.
CREATE TABLE EMP(EID INT,NAME VARCHAR(20),BASIC_SAL INT,HRA INT, TOTAL_SAL AS BASIC_SAL+HRA)
In the above T-SQL statement, I am creating a table EMP having Five columns in it, EID, NAME,BASIC_SAL, HRA and TOTAL_SAL where TOTAL_SAL is a computed column.
We need not to provide any datatype to a computed column. Below are the few properties of computed columns.
1)Computed column cannot be used for insert and update statement(we cannot insert,update a computed column)
2)Computed columns values are calculated at run time, these values are not physically stored in the database.
3)Computed column's value can be physically stored by using persisted clause. This feature was introduced in SQL 2005 available on later versions.
CREATE TABLE EMP(EID INT,NAME VARCHAR(20),BASIC_SAL INT,HRA INT, TOTAL_SAL AS BASIC_SAL+HRA persisted)
4)If you want to create a check constraint, primary key, foreign key or not null constraint on a computed column, you need to make it persisted first.
Note :- But there is one more exception that computed column should be using deterministic expressions.
Example :- If we have two columns A and B and the computed column is defined as follows.
C=A + datediff(day,'01 jan 2012',getdate())
I hope you will find this article useful for you.. In my next article we will talk about few other topics of SQL Server. Till then take care and have a good day.
1)Foreign Key
2)Check Constraint
3)Unique Key Constraint
4)Computed Column
Foreign Key:- A foreign key in a table can be defined as a key in a table that has a reference to another column in a different table. It means that the values in the foreign key column should have same values as primary key column has. Lets take an example of foreign key.
Note:- For creating foreign key we can refer with Primary key column or unique key column.
I have two table Employee and Dept in my database. Lets create a primary key on Employee table first.
To create primary key on table Employee, just expand the Tables folder tree view and select the Employee table, right click on it and click on design menu. once you click on design menu, the below window will open in front of you.
Once you have opened the design mode of employee table, right click on the column that you want to make as primary key, a menu will pop up with an option "set primary key". Select that option and click on the save button(Floppy icon). Now we have created a primary key on the table employee. Lets create a foreign key on table Dept.
We will open the Dept table in design mode as we did with employee table and right click on the EID column of Dept table as shown in the below image.
We will click on "Relationships" menu and a new window will come up.
Now click on add button and see what happens.
In the right side of the dialogue box, if we select "Tables and Columns Specification" caption, a small button will appear in front of that caption. Please refer the below image.
Click on the small button which is appeared after selecting the "tables and columns specifications" caption.A new window will appear. Please see below image for your reference.
We have to give a name to the foreign key constraint, You can see that "Fk_Dept_Dept" name has given by SQL server to the foreign key constraint. We can change it, if we want to change. In the above dialogue box there is one drop down list at the left side, from where we can choose the primary key table, We want to refer Employee table, hence we will select employee table in the drop down list.
Once we select the Employee table in the drop down list, there is also a drop down list underneath to the above drop down list where we selected the "Employee" table. In this drop down list we will have all columns of the Employee table. we will select "EID" column in this drop down list. At the right side of the dialogue box and underneath the "Dept" table field there is also a drop down list where all of the columns of dept table are present. We will select "EID" column as we want to make "EID" column of Dept table as a foreign key which will reference to "EID" column of Employee table.
Clicking on "Ok" button will take us on the below screen. Now click on close button and click on the save button(Floppy icon). Now foreign key is created on table Dept and foreign key column is EID.
Lets try to add one row in dept table first having EID value=70, which is not present in Employee table and see what happens.
If we try to insert a row which is having EID value that is not already present in the Employee table, we will be getting the error message that is shown in the above picture. Lets insert EID 70 in the Employee table first and then in Dept table.
Now we are able to insert the record having EID value 70 in the Dept table.
Check Constraint :- Check constraint is used as check point before inserting a value into a particular column. Lets take an example to have a better understanding of check constraint.
Create table Customer(CustId Int,CustName varchar(100),Earning Numeric(10,2) check (Earning>10))
I have created a table Customer having three columns in it and third column of Customer is having Check constraint defined on it. I have put a condition that Earning >10 , it means that this column will check the column value before inserting the row, if the value is <=10 then , it will throw an error message, otherwise it will allow the row that is having Earning >10.
In the above picture, you can see that I am trying to insert a value 10 which resulted into an error message.
Unique Key Constraint:- Unique key constraint is quite similar to Primary key constraint, It can uniquely identify the each row of the table and can also used a reference to a foreign key constraint. Unique key has below two properties which are different from Primary key.
1) While creating primary key, it creates clustered index on the key column, on the other hand Unique key constraint create a non clustered index on the key column.
2)Primary key column doesn't allow a null value in it, while Unique key allow one Null value in the key column.
Create table Customer(CustId Int unique ,CustName varchar(100),Earning Numeric(10,2))
You just need to write Unique key word in front of the column that you want to make as Unique key.
4)Computed Column:- Computed column is also known as derived column. Computed column can be defined as a column which is result of some calculation based on requirement. We need not to explicitly insert a value in computed column. It is populated automatically based on the formula definition. Lets take an example.
CREATE TABLE EMP(EID INT,NAME VARCHAR(20),BASIC_SAL INT,HRA INT, TOTAL_SAL AS BASIC_SAL+HRA)
In the above T-SQL statement, I am creating a table EMP having Five columns in it, EID, NAME,BASIC_SAL, HRA and TOTAL_SAL where TOTAL_SAL is a computed column.
We need not to provide any datatype to a computed column. Below are the few properties of computed columns.
1)Computed column cannot be used for insert and update statement(we cannot insert,update a computed column)
2)Computed columns values are calculated at run time, these values are not physically stored in the database.
3)Computed column's value can be physically stored by using persisted clause. This feature was introduced in SQL 2005 available on later versions.
CREATE TABLE EMP(EID INT,NAME VARCHAR(20),BASIC_SAL INT,HRA INT, TOTAL_SAL AS BASIC_SAL+HRA persisted)
4)If you want to create a check constraint, primary key, foreign key or not null constraint on a computed column, you need to make it persisted first.
Note :- But there is one more exception that computed column should be using deterministic expressions.
Example :- If we have two columns A and B and the computed column is defined as follows.
C=A + datediff(day,'01 jan 2012',getdate())
I hope you will find this article useful for you.. In my next article we will talk about few other topics of SQL Server. Till then take care and have a good day.
No comments:
Post a Comment