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

Replace multiple space with single space – SQL Server

Replace multiple space with single space – SQL Server

DECLARE @Temp TABLE (OriginalString VARCHAR(8000))

INSERT INTO @Temp (OriginalString)
SELECT ‘ Hello. This is Hello World. ‘

SELECT REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(OriginalString)), ‘ ‘, ‘ ‘ + CHAR(7)), CHAR(7) + ‘ ‘, ”), CHAR(7), ”) AS [result]
FROM @Temp
WHERE CHARINDEX(‘ ‘, OriginalString) > 0

Happy Coding 🙂

SQL Tips and Tricks

Find Column Used in Stored Procedure – Search Stored Procedure for Column Name. Also Search Stored Procedure Code and Stored Procedure Text

SELECT obj.NAME SPName

,com.TEXT SPText

FROM sys.syscomments com

INNER JOIN sys.objects obj ON com.Id = obj.OBJECT_ID

WHERE com.TEXT LIKE ‘%Column Name%’

AND TYPE = ‘P’

Find Column Used in Tables – Search Tables for Column Name

SELECT COLUMN_NAME

,TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE ‘%Column Name%’

Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

SELECT DISTINCT obj.NAME

,obj.xtype

FROM syscomments com

INNER JOIN sysobjects obj ON com.id = obj.id

WHERE com.TEXT LIKE ‘%Table Name %’

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 🙂