SQL Day 6: Check Constraint

LO 1: Check Constraint

Check Constraints is used to limit the range of values, which can be entered in the column.

Let say we have the AGE column in the tblEmployee table and in reality, a personage cannot be a negative value and may not exceed 150 years. To check whether the inserted values are within the range or not we can use Check Constraints. If the inserted value is outside the range then this constraint will throw an error.

Let’s have a look at the tblEmployee table.



In this table, Age column is in of integer type (you can check the data type by right click on table and select Design or you can select the table and then press Alt+F1)



Age column data type is displayed as int and integer column will accept any value including NULL, negative value and a positive value greater than 150.



We can add Check Constraints in two ways.

Adding Check Constraints Graphically

Using designer, expand the table tblEmployee then right click on Constraints folder and then select New Constraint.



Remember: A constraint is just as a Boolean expression which will return true or false value.

In Expression, we will give the expression that returns true or false.

  • If the value returned true then the value will be accepted and will be inserted into the table. 
  • If the value returned false then the value will not be accepted and will not be inserted into the table and error message will be displayed.


Save this table by pressing Ctrl+S. This will display an error window because when we are creating this constraint “Check Existing Data On Creation Or Re-Enabling” set to “Yes” which means this constraint will check the data in the table according to the specified expression and if found invalid data then it will throw an error.



When the invalid data is deleted from the tblEmployee then this check constraint will we saved.
Or you can simply set “Check Existing Data On Creation Or Be Re-Enabling” set to “No”, in this case, this check constraint will not check the existing data in the tblEmployee table.

After saving the table, Refresh the Constraints folder and the created constraint “CK_tblEmployee_Age” will be displayed.



Let’s try to insert the negative value of value greater than 150. This will display the error message.



Trick: Now let’s try to insert the NULL value in Age column; will this insert or an error will be displayed?

The answer is value will be inserted because when age is specified as NULL then the Boolean expression will be treated as Unknown and will return true.



Let’s try to insert the valid range value. This will be inserted successfully.


Adding Check Constraints Using Query

Simply execute the query and this will create the Check Constraint on the tblEmployee table.

Syntax:

Alter table (table_name)
Add Constraint (constraint_name) check (boolean_expression)

Example:

Alter table tblEmployee
Add Constraint CK_tblEmployee_Age check (Age>0 AND Age<150)<150 code="">


Dropping the Check Constraint

We can drop the check constraint in two ways.

Graphically: Expand the Constraint folder, select the constraint and click on Delete. This will delete the Check constraint.


Query: We need to execute the below query to drop the check constraint.

Syntax: 

Alter table (table_name)
Drop Constraint (constraint_name)

Example: 

Alter table tblEmployee
Drop Constraint CK_tblEmployee_Age

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