SQL Day 04: Adding Default Constraint

LO: Adding Default Constraint

Prerequisite: Creating Tables

In creating a table chapter we have created the table named tblEmployee which have the not null columns that are we always need to provide the values for these columns to insert the values.


In tblEmployee table ID, Name and Email are not null where as GenderID allow nulls.
Let’s write the query to insert the values in tblEmployee and select the all records after the successful insert.

Insert into tblEmployee (ID, Name, Email) values (5,'David','david@test.com')
select * from tblEmployee



You should see the NULL is inserted for the GenderID column because we did not give any value while inserting the data.
Now, look at the tblGender table. We have specified value 3 for unknown.

Let’s take an example suppose you did not know the gender of the person and you want 3 to be inserted whenever the gender is not defined. To implement this you need to set the default constraint.

So basically Default constraint is used to insert the default value if no other value is specified, including NULL.

Adding Default Constraint in Existing Column

To add default constraint on existing column we need to use the alter command.

Syntax: 

alter table (table_name)
Add Constraint (constraint_name)
Default (default_value) For (column_name)

Example:

alter table tblEmployee
Add Constraint DF_tblEmployee_GenderID
Default 3 For GenderID

When you execute the above statement then default constraint get created and will display in Constraint folder.



Adding Default Constraint in Existing Table New Column

Let’s say we need to add the new column in the table with the default constraint then we need to write following query.

Syntax: 

alter table (table_name)
add (column_name) (Data_type) (Null | Not Null)
Constraint (constraint_name) default (default_value)

Now we again try to insert the values in the tblEmployee table and not give the GenderID.

Insert into tblEmployee (ID, Name, Email) values (5,'Tom','tom@test.com')
select * from tblEmployee

Once the query is completed, this will insert the default value as 3 in GenderID column as initialized above.



Now we try to insert the values in tblEmployee with the GenderID value.

Insert into tblEmployee (ID, Name, Email, GenderID) values (5,'Marry','marry@test.com', 2)
select * from tblEmployee

Once the query is completed, this will insert the value 2 for the record because we specified the value 2 for the GenderID so this will ignore the default value and will insert the 2 in the GenderID for the record.



Trick: Let’s see what will happen when we insert the GenderID value as NULL. Will this be inserted or the default value gets inserted?
The answer is NULL value will be inserted and the default value will NOT be executed because NULL is considered as value.


Dropping the Default Constraint

To drop the default constraint we need to alter the table and then drop the constraint.

Syntax:
alter table (table_name)
drop constraint (contraint_name)

Example:
alter table tblEmployee
drop constraint DF_tblEmployee_GenderID
That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading