PRIMARY KEY | SQL with Manoj

DB Basics – As Primary Key can identify a row uniquely, what are the scenarios you need a Super Key?

September 17, 2015 Leave a comment

In one of my [previous post] I discussed about various types of Keys in DBMS, like Candidate, Primary, Composite & Super Keys.

I got few questions like:
1. If there is already a Primary Key to identify a row uniquely, then in which scenario a Super Key can use used?
2. How to create a Super Key like a Primary Key in SQL Server or Oracle?

–> First of all what is a Primary Key (PK): It is the Candidate key attribute/column that is most suited to maintain uniqueness in a table at the tuple/row level.

–> And a Super Key is a super-set of Candidate key. If you add any other attribute/column to a Candidate Key then it become a Super Key. Or a minimal Super Key is called a Candidate Key.

–> Now consider the same example that I discussed in my [previous post]:

CREATE TABLE Employee (
    EmployeeID, 
    EmployeeName, 
    DOB, -- Date of birth
    DOJ, -- Date of joining
    SSN, -- Social Security Number
    DeptID, -- Department ID
    MgrID -- Manager ID
)

Now in this table there are 2 Candidate Keys i.e. EmployeeID & SSN.
– EmployeeID: should be unique with auto-increment column.
– SSN: should also be unique for every employee.

So, here you cannot make SSN as a Primary Key, because its a PII and secure data.
Hence, creating EmployeeID as Primary Key makes sense, this way SSN will be left as an Alternate Key.

–> Now, coming to the Super Key concept: As per the above definition, a minimal Super Key will be called as a Candidate or Primary Key. Thus every Primary Key and Alternate Key is also a Super Key. And if you combine any other column with the EmployeeID (PK) column, the combination will also be called as a Super Key, like:
– EmployeeID
– EmployeeID + DOB
– EmployeeID + DOJ
– EmployeeID + DeptID + MgrID
– EmployeeID + DOB + DOJ + DeptID + MgrID
– EmployeeID + [other column combinations]

For all the above and other possible combinations the row will still be identified uniquely, thus all these combinations will act like a Super Key.

And, if you add all columns with the EmployeeID, it is called a Trivial Super Key, because it uses all columns of Employee table to identify a row.

And in SQL Server or Oracle there is no syntax as such to create a Super Key, this is just a concept in DBMS.

Hope this helps !!!

Categories: DB Concepts Tags: PRIMARY KEY, Super Key

Difference between Index and Primary Key – MSDN TSQL forum

August 10, 2015 Leave a comment

–> Question:

What is the difference between the Index and the Primary Key?

–> My Answer:

In simple DBMS words:

– A Primary Key is a constraint or a Rule that makes sure to identify a table’s column uniquely and enforces it contains a value, ie. NOT NULL value.

– An Index on the other side is not a constraint, but helps you organize the table or selected columns to retrieve rows faster while querying with SELECT statement.

In SQL Server you can create only one Primary Key, and by-default it creates a Clustered Index on the table with the PK column as the Index key. But you can specify to create Non-Clustered Index with a PK also.

Indexes in SQL Server mainly are:

– Clustered Index

– Non Clustered

… you can specify them as unique or non-unique.

Other type of indexes are:

– ColumnStore

– Filtered

– XML

– Spatial

– Full Text

–> Another Answer by Erland:

A primary key is a logical concept. The primary key are the column(s) that serves to identify the rows.

An index is a physical concept and serves as a means to locate rows faster, but is not intended to define rules for the table. (But this is not true in practice, since some rules can only be defined through indexes, for instance filtered indexes.)

In SQL Server a primary key for a disk-based table is always implemented as an index. In a so-called memory-optimized table, the primary key can be implemnted as an index or as a hash.

–> Another Answer by CELKO:

PRIMARY KEY is define in the first chapter of the book on RDBMS you are too lazy to read. It is a subset of columns in the table which are all not null and unique in the table. An index is an access method used on the records of a physical file.

Many SQL products use indexes to implement keys; many do not (hashing is a better way for large DB products)

Check the video on Primary Keys:

Ref link.

Categories: Differences, SQL Server Questions Tags: Index, MSDN TSQL forum, PRIMARY KEY

SQL Myth | Primary Key (PK) always creates Clustered Index

May 24, 2015 4 comments

… and this a default nature, which can be overrided to create a PK with a Non-Clustered Index instead of a Clustered one.

– What happens when a Primary Key is created on a table?
– If I have a PK on a table will the table be a Heap or not?
– Can I create a PK with a Non-Clustered Index? (this is a big hint)

While Interviewing candidates I’ve confronted them with these type of question, and very few were able to answer these correctly.

–> So, here we will see the default behavior of Primary Keys and how we can override it:

–> Having the “PRIMARY KEY” option inline with the column name: While creating a new table when you specify a “PRIMARY KEY” option inline with the Key Column, by-default it creates a Clustered Index on that table with a PK Constraint on that column.

USE [tempdb]
GO

CREATE TABLE dbo.Employee (
	EmpID		 INT NOT NULL PRIMARY KEY, -- here
	EmpLogInID 	 VARCHAR(255) NULL,
	EmpFirstName VARCHAR(255) NOT NULL,
	EmpLastName	 VARCHAR(255) NOT NULL,
	Gender		 BIT NOT NULL,
	JobTitle	 VARCHAR(255) NULL,
	BOD			 DATETIME NULL,
	DOJ			 DATETIME NULL,
	DeptID		 INT NOT NULL
)
GO

sp_help 'dbo.Employee'
GO

The above image shows:
– a Unique Clustered Index created on the [EmpID] column with a name automatically suggested by the DB-engine, and
– a PK Constraint created on the same column.

–> Overriding this behavior by having a “CONSTRAINT” option: Here we will not create the PK inline with the Key Column [EmpID]. But we will:
– have a separate PK constraint created with Non-Clustered Index for [EmpID] column, and
– an another SQL statement to create a Clustered Index on the [EmpLogInID] column.

DROP TABLE dbo.Employee
GO

CREATE TABLE dbo.Employee (
	EmpID		 INT NOT NULL,
	EmpLogInID 	 VARCHAR(255) NULL,
	EmpFirstName VARCHAR(255) NOT NULL,
	EmpLastName	 VARCHAR(255) NOT NULL,
	Gender		 BIT NOT NULL,
	JobTitle	 VARCHAR(255) NULL,
	BOD			 DATETIME NULL,
	DOJ			 DATETIME NULL,
	DeptID		 INT NOT NULL
	CONSTRAINT [PK_Employee_EmpID] PRIMARY KEY NONCLUSTERED (EmpID ASC) -- here
)
GO

-- Creating the Clustered Index separately on an other column:
CREATE CLUSTERED INDEX [CI_Employee_EmpLogInID] ON dbo.Employee(EmpLogInID ASC)
GO

sp_help 'dbo.Employee'
GO

The above image shows:
– a Clustered Index (Non-Unique) created on the [EmpLogInID] column with a name we provided,
– a Non-Clustered Index (Unique) created on the [EmpID] column, and
– a PK Constraint created on the [EmpID] column with a name we provided.

So, it is advised to choose your PK & Clustered/Non-Clustered index wisely based upon a proper and justified Business logic. Please do not consider this as a Use Case, but just an example on how to deal with PKs & Indexes.

Check the video on Primary Keys:

Categories: Misconception Tags: Clustered Index, Heap, Non Clustered Index, PRIMARY KEY

DB Basics – What are Candidate, Primary, Composite & Super Keys and Difference between them?

September 15, 2014 7 comments

Let’s take an example of an Employee table:

CREATE TABLE Employee (
    EmployeeID, 
    EmployeeName, 
    DOB, -- Date of birth
    DOJ, -- Date of joining
    SSN, -- Social Security Number
    DeptID, -- Department ID
    MgrID -- Manager ID
)

1. Candidate Key: is the attribute/column or a set of attributes/columns in a relation/table that qualifies for uniqueness of each tuple/row. A relation/table can have one or more than one Candidate Keys. A Candidate key is also known as a minimal Super key.

Here in Employee table columns EmployeeID & SSN individually can maintain uniqueness in a table, thus are eligible for Candidate keys. The columns EmployeeName + DOB combined can also make up a Candidate Key, but there is a narrow chance that 2 Employees with same name can be born in same day.

2. Primary Key: is the Candidate key attribute/column that is most suited to maintain uniqueness in a table at the tuple/row level. More about PK.

Here in Employee table you can choose either EmployeeID or SSN column for a PK, EmployeeID is preferable choice because SSN is a secure (PII) value.

3. Alternate Key: are the other Candidate key attribute/columns that you didn’t choose as Primary key column.

Like if you choose EmployeeID as a PK then SSN would be the Alternate key.

4. Super Key: is a superset of Candidate key. If you add any other attribute/column to a Candidate Key then it become a Super Key.

Like EmployeeID + EmployeeName is a Super Key.

5. Composite Key: If a table do have a single column that qualifies for a Candidate key, then you have to select 2 or more columns to make a row unique.

Like if there is no EmployeeID or SSN columns in Employee table, then you can make EmployeeName + DOB as a Composite Primary Key. But still there can be a narrow chance of duplicate rows.

–> Check the video:

Categories: DB Concepts Tags: Alternate Key, Candidate Key, Composite Key, PRIMARY KEY, Super Key

DB Basics – Difference between Primary Key & Unique Key

November 3, 2009 2 comments

Primary Key & Unique Key are nothing but Constraints that can be set at column level of a table, to maintain Uniqueness in the table and thus not allowing duplicate entries.

–> Primary Key (PK) Constraint:

1. A PRIMARY KEY uniquely identifies every row in a database table.

2. The PK constraint on a Table’s COLUMN enforces:
– – UNIQUE values and
– – NOT NULL values

3. Every table can have only one PK defined on a particular column (or more than 1 columns).

4. You can create PK on a Table with:
– – CREATE TABLE statement (in two ways)
– – or later with ALTER TABLE … ADD CONSTRAINT statement.

5. You can create a PK with an IDENTITY column.

6. PK can be Composite Key, containing more than one column.

PRIMARY Key and INDEXes:

7. In SQL Server on creating a Primary Key on a table, a Clustered Index is created with PK column as the Clustering Key.

8. You can also create a Primary Key with a Non-Clustered Index, check this blog post.

Check the video on Primary Keys:

–> Unique Key (PK) Constraint:

1. A UNIQUE KEY just like PK uniquely identifies every row in a database table.

2. The UK constraint on a Table’s COLUMN enforces:
– – UNIQUE values (no duplicate values)
– – Allow single NULL value (But PK do not allow NULL values)

3. A table can have one or more than one UK defined on many columns (or more than 1 columns). But only 1 PK.

4. You can create UK on a Table with:
– – CREATE TABLE statement (in two ways)
– – or later with ALTER TABLE … ADD CONSTRAINT statement.

6. UK can be Composite Key, containing more than one column.

7. A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

Unique Key and INDEXes:

8. In SQL Server on creating a Unique Key on a table, a Non-Clustered Index is created with the column(s) as the Index Key.

Check the video on Unique Keys: