Tables and other objects related to tables(Part 2)

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.

Tables and other objects related to tables.

Good morning friends..In last article we saw how to create a table in database using T-SQL script as well as using management studio. Now lets talk few more objects related to tables. We will talk about few properties those can be set for columns in a table while creating the table. The properties we are going to talk about are as following:-

1)Primary Key
2)Foreign Key
3)Check Constraint
4)Default Constraint
5)Identity Column
6)Not Null Column
7)Unique Key Constraint
8)Computed Column
9)Collation 

There are lot of other properties available while creating a table but as a developer point of view, there are enough to know about. Lets take each property one by one.

Primary Key:- I hope everybody who's background is (IT, CS), would be knowing that primary key is a column or a group of column which uniquely identify each row of the table called primary key. we will learn how to create a primary key on a column or a group of column in the table, before we move ahead, let me one important aspect of primary key column, Primary key column doesn't contain any null value. It means that if we want to create a primary key on column it should not allow a null value to be inserted in that particular column.

In the above image you can see that I have expanded the tables folder in test database and right clicked on table employee that resulted into a menu with few options. Now I will select the design menu as we are going to design the table employee.

when we click on design menu the above window appears, Now we will select EID column and right click on it to make it as a primary key of employee table, another menu will appear with option "set primary key" as an option. You can see in the above image there are three columns "column name", "data type", "allow nulls" and the value of "allow nulls" is checked, it means that this column will accept null values in it. But as I said earlier, Primary key column cannot accept null values in it. Hence, as soon as you click on "set primary key" option, it SQL Server Management Studio automatically unchecked the value of "allow nulls" column.

You can see in the above image that "allow nulls" column is unchecked now. Lets see other properties of column as shown in the above image. I am going to expand the column properties window as shown in the above image so that you can have a clear look.

You need to select the column for which you want to set the properties, I have selected "Name" column and below is the property window for "Name" Column. Allow Null property is marked with "Yes", it means this column will allow null values, You can change it to "No" if you do not want it to accept null values.

Default Value or binding(Default Constraint) :- Default value property is blank, it means there is no default constraint defined on it and no default value assigned to "Name" Column in employee table. You can put a default value 'neeraj' or whatever value you want to see in the column to be entered by default.

Collation :- Collation property is showing value "<database default>" it means that this column will have same collation that database has.Collation is very important aspect in SQL Server. Two columns with different collation cannot be join together. I will talk about collation in detail after this article.

Is Identity :- Value for this column is "No" it means that "Name" Column will not have identity value. This value is only enabled for Int, bigint, tinyint, (Numeric, decimal with decimal factor 0). As "name" column is a varchar field, hence this option is not enabled.

What is Identity Column:- If we set a column as a identity column, we need not to insert any value to this column, it will be automatically filled while inserting a record to the table.

Identity Increment :- You you have set the column property Is identity =Yes, you can also set the incremental value for each record to be inserted.
Example :- Suppose I have set the identity increment value =5 and identity seed=10 then, if  I insert three records in the table. lets see what would be the outcome of the select statement on employee table.

You can see the result, first value for EID is 10, Identity Seed defines what would be the starting value of the identity column for first record. Identity Increment defines what would be increment value per record, hence 10 +5(increment value)=15 , then next value 20 and so on.

Note:- Increment value and Identity seed are not mandatory fields, if we do not define any values for these properties, it will start with value 1 and incremental value will also be 1.

I hope you will like this article, few properties are still left to talk about. we will talk about rest of the properties of the table in my next article. Till then take care and have a wonderful weekend.