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.
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.
