Alternate Key, Candidate key, Composite Key, Foreign Key, Primary Key in RDBMS

We use the database to organize information. To maintain data in a correct and well formed, we use concept of keys. There are five types of keys in database which are as follows –

  • Alternate Key
  • Candidate key
  • Composite Key
  • Foreign Key
  • Primary Key

To understand keys, we will take EmployeeMaster table as example –

EmployeeMaster
{
EmployeeID,
EmpFirstName,
EmpLastName,
DesignationID
}

Table keys are:

Alternate Key:  EmpFirstName + EmpLastName

Candidate keys: EmployeeID or EmpFirstName + EmpLastName

Composite Key:  EmpFirstName + EmpLastName

Foreign Key: DesignationID

Primary Key: EmployeeID

There are five types of keys in database which are as follows –

Alternate Key

  • Any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of  that table
  • For e.g. EmpFirstName + EmpLastName

Candidate key

  • Is not null
  • Have unique records
  • Can create multiple candidate key per table
  • Candidate keys are those keys which is a candidate for primary key of a table
  • For e.g. EmployeeID or EmpFirstName + EmpLastName

Composite Key

  • When we create keys on more than one column then that key is known as composite key
  • For e.g. EmpFirstName + EmpLastName

Foreign Key

  • Can create more than one foreign key per table
  • Foreign key are those keys which is used to define relationship between two tables
  • To implement relationship between two tables
  • Also known as referential integrity
  • For e.g. DesignationID which is reference to DesignationMaster table

Primary Key

  • Is not null
  • Have unique records
  • Only one primary key per table
  • Candidate key which is chosen as a primary key for table is known as primary key
  • For e.g. EmployeeID

The query to find who created or dropped or altered object in database – SQL

— read all available traces.

DECLARE @current VARCHAR(500);

DECLARE @start VARCHAR(500);

DECLARE @indx INT;

SELECT @current = path

FROM sys.traces

WHERE is_default = 1;

SET @current = REVERSE(@current)

SELECT @indx = PATINDEX(‘%\%’, @current)

SET @current = REVERSE(@current)

SET @start = LEFT(@current, LEN(@current) – @indx) + ‘\log.trc’;

— CHNAGE FILER AS NEEDED

SELECT CASE EventClass

WHEN 46

THEN ‘Object:Created’

WHEN 47

THEN ‘Object:Deleted’

WHEN 164

THEN ‘Object:Altered’

END

,DatabaseName

,ObjectName

,HostName

,ApplicationName

,LoginName

,StartTime

FROM::fn_trace_gettable(@start, DEFAULT)

WHERE EventClass IN (

46

,47

,164

)

AND EventSubclass = 0

AND DatabaseID <> 2

ORDER BY StartTime DESC

Reference:- http://goo.gl/JHLVLo

Happy Querying 🙂

Increment a value in the SET clause of an UPDATE Statement in SQL

I was working on a task in which I have to add one new column in my existing table and set its value given by the client which is incremental value to previous records.

For e.g.

Id         fname              lname               No

1          user 1              user 1              5

2          user 1              user 1              6

3          user 1              user 1              7

I was looking for some solution and ended up with following solution which works well for me.

-- Create Table
CREATE TABLE usermaster (
fname VARCHAR(20)
,lname VARCHAR(20)
)
-- Insert 100 Records
INSERT usermaster
VALUES (
'user fname'
,'user lname'
) GO 100 -- You can specify any no. of records
-- Add one more column called id using ALTER statement
ALTER TABLE usermaster ADD id INT
-- Update Id column with Increment value
DECLARE @id INT
SET @id = 0 -- you can specify any no. I set it 0 so it start with 1.
UPDATE usermaster
SET @id = id = @id + 1
GO
-- check updated table records
SELECT *
FROM usermaster
GO

Happy Querying 🙂

SSMS Tip: What happened to my keyboard shortcuts? Ctrl+R (Show/Hide Result)

Ctrl+R is a good friend of mine. It is very handy to use this command not only to hide the results pane away and focus on the query, but to also be able to bring the results back just as quickly – without having to run the query again

In SSMS 2014 when I press Ctrl+R to show/hide my result pane, I was getting below message in the left bottom of SSMS.

“(Ctrl+R) was pressed. Waiting for second key of chord…”

Pressing Ctrl+R again I get message as “The key combination (Ctrl+R, Ctrl+R) is not a command”

Here is what I did to make my shortcut work again.

  1. Go to Tools Menu in SSSMS
  2. Click on Option
  3. Go to Keyboard and expand the menu
  4. Click on option Keyboard again
  5. Enter “Window.ShowResultsPane” in Show commands containing: search box
  6. Select “Window.ShowResultsPane”
  7. Change the “Use new shortcut in:” dropdown to “SQL Query Editor“
  8. Put your cursor in the “Press shortcut keys:” box and hit Ctrl+R
  9. Click on “Assign” button before hitting OK

Unlike some other changes that still require an SQL Management Studio restart to take effect, the change takes effect immediately, and CTRL + R should start working again.

Hope this helps! Please write comment and let me know your feedback.

Happy Coding…!!! 🙂

Check foreign key constraints in SQL Server Database

While searching for how to find Primary Key and Foreign Key relationship using INFORMATION_SCHEMA in SQL Server Database, I came across following query which works for me.

SELECT RC.CONSTRAINT_NAME AS ‘FK_Name’
,KF.TABLE_SCHEMA AS ‘FK_Schema’
,KF.TABLE_NAME AS ‘FK_Table’
,KF.COLUMN_NAME AS ‘FK_Column’
,RC.UNIQUE_CONSTRAINT_NAME AS ‘PK_Name’
,KP.TABLE_SCHEMA AS ‘PK_Schema’
,KP.TABLE_NAME AS ‘PK_Table’
,KP.COLUMN_NAME AS ‘PK_Column’
,RC.MATCH_OPTION AS ‘MatchOption’
,RC.UPDATE_RULE AS ‘UpdateRule’
,RC.DELETE_RULE AS ‘DeleteRule’
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
WHERE KF.COLUMN_NAME LIKE ‘%<Column Name>%’


You can also find Foreign Key using EXEC sp_fkeys ‘<TABLE NAME>’


Happy Coding 🙂