SQL Day 9: Unique Key Constraint

LO1: What is Unique Key Constraint

LO2: When to Use Unique Key

LO3: Difference between Unique Key and Primary Key

Prerequisite: SQL Day 3: Creating Tables & Enforcing Primary and Foreign Key

We use Unique Key to enforce the uniqueness of a column i.e. column should not allow duplicate values.

Creating the Unique Key Graphically

To create the unique key graphically you need to right click on the table and click on Design.



Let say on tblEmployee table we need to make the unique key, to do that right click on column and select Indexes/Keys



In Indexes/Keys pop up click on Add button. After clicking on this button click on ellipsis button and select the Email column



Now set the Is Unique, Type and a proper Name.



Then click on the Close button and Save the table. This will create the Unique Key and will display when you refresh the Keys folder.

Creating the Unique Key using Query

Syntax: 

alter table Table_Name
add constraint Unique_Key_Name unique (Column_Name)

Query:

alter table tblEmployee
add constraint UQ_tblEmployee_Email unique (Email)



When we insert the new row with duplicate email then this unique key constraint should be invoked.

Dropping the Unique Key using Graphically

Go to Keys folder, expand it and right click on created unique key and then click on Delete this will delete the created unique key.

Dropping the Unique Key using Query

Syntax:

alter table Table_Name
drop constraint Unique_Key_Name

Query:

alter table tblEmployee
drop constraint UQ_tblEmployee_Email

When to Use Unique Key


A table can have only one primary key. If you want to enforce uniqueness of 2 or more columns, then we use unique key constraint.

Difference between Unique Key and Primary Key

  • A table can have only one Primary key but can have 1 or more unique keys.
  • Primary Key does not allow null values but unique allow only one null

Note: Unique Key only allows one NULL value in complete column i.e. if you try to insert the value as null for the unique key column then only first time (if not already exists NULL) this NULL value successfully inserted and second time when you try to insert the NULL value, unique key constraint will be invoked.


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