Primary key and unique key

Difference between Primary Key & Unique Key
Primary Key
Unique Key
Primary Key can't accept null values.
Unique key can accept only one null value.
By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
By default, Unique key is a unique non-clustered index.
We can have only one Primary key in a table.
We can have more than one unique key in a table.
Primary key can be made foreign key into another table.
In SQL Server, Unique key can be made foreign key into another tabl





PRIMARY KEYUNIQUE KEY
NULLIt doesn’t allow Null values.
Because of this we refer
PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT		Allows Null value. But only one Null value.
INDEXBy default it adds a clustered indexBy default it adds a UNIQUE non-clustered index
LIMITA table can have only one PRIMARY KEY Column[s]A table can have more than one UNIQUE Key Column[s]
CREATE SYNTAXBelow is the sample example for defining a single column as a PRIMARY KEY column while creating a table:
CREATE TABLE dbo.Customer
(
Id INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(50)
)
Below is the Sample example for defining multiple columns as PRIMARY KEY. It also shows how we can give name for the PRIMARY KEY:
CREATE TABLE dbo.Customer
(
Id INT NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(50),
CONSTRAINT PK_CUSTOMER PRIMARY KEY(Id,FirstName)
)
Below is the sample example for defining a single column as a UNIQUE KEY column while creating a table:
CREATE TABLE dbo.Customer
(
Id INT NOT NULL UNIQUE,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(50)
)
Below is the Sample example for defining multiple columns as UNIQUE KEY. It also shows how we can give name for the UNIQUE KEY:
CREATE TABLE dbo.Customer
(
Id INT NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(50),
CONSTRAINT UK_CUSTOMER UNIQUE(Id,FirstName)
)
ALTER SYNTAXBelow is the Syntax for adding PRIMARY KEY CONSTRAINT on a column when the table is already created and doesn’t have any primary key:
ALTER TABLE dbo.Customer
ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id)
Below is the Syntax for adding UNIQUE KEY CONSTRAINT on a column when the table is already created:
ALTER TABLE dbo.Customer
ADD CONSTRAINT UK_CUSTOMER UNIQUE(Id)
DROP SYNTAXBelow is the Syntax for dropping a PRIMARY KEY:
ALTER TABLE dbo.Customer
DROP CONSTRAINT PK_CUSTOMER
Below is the Syntax for dropping a UNIQUE KEY:
ALTER TABLE dbo.Customer
DROP CONSTRAINT UK_CUSTOMER

Comments

Post a Comment

Popular posts from this blog

Prime Number Program in C#

Prime number is a number that is greater than 1 and divided by 1 or itself. In other words, prime numbers can't be divided by other numbers than itself or 1. For example 2, 3, 5, 7, 11, 13, 17, 19, 23.... are the prime numbers. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 {     class Program     {         static void Main(string[] args)         {             int number, i, m = 0, flag = 0;             Console.WriteLine("PLEASE ENTER THE NUMBER TO CHECK PRIME NUMBER");             number = int.Parse(Console.ReadLine());             m = number / 2;             for(i=2;i<=m;i++)             {           ...
Read more

Fibonacci Series in C#

In the case of Fibonacci series, the next number is the sum of the previous two numbers for example 0, 1, 1, 2, 3, 5, 8, 13, 21 etc. The first two numbers of Fibonacci series are 0 and 1. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 {     class Program     {         static void Main(string[] args)         {             int val1 = 0, val2 = 1, val3, i, number;             Console.WriteLine("Please Enter any number");             number = int.Parse(Console.ReadLine());             Console.WriteLine(val1 + " " + val2);             for (i=2;i<=number;i++)             {                 val3 =...
Read more

view in sql server

Image
A view is nothing more than a  saved SQL query . A view can also be considered as a virtual table .   A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. SQL CREATE VIEW Syntax CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE coHEndition SQL Script to create tblEmployee table: CREATE TABLE  tblEmployee (  Id  int Primary Key ,  Name  nvarchar (30),  Salary  int ,  Gender  nvarchar (10),  DepartmentId  int ) When this query is executed , the database engine actually retrieves the data from the underlying base tables,  tblEmployees and tblDepartments . The View itself, doesnot store any data by default. When to Use a Vi...
Read more