Identify Primary Keys That Are Not Identity Columns

Continuing the thoughts of knowing what is going on in your database let’s talk a little bit about primary keys.

Primary Keys

A primary key is one that uniquely identifies a given row in a given table. They are a requirement to be able to use foreign key restraints that help to retain referential integrity throughout your database along with being used for replication in some cases. Primary Keys also automatically create an index on your table. Another reason for being aware of what’s going on to make sure things are functioning as you expect in your database.

Ideally you will have actual data already in the table that can be used to uniquely identify each row. This is known as a natural key. A lot of times though you will find the value is a single identity value that auto-increments with each row being inserted and it really doesn’t relate back to the data itself . This is one of the easiest ways to ensure that each row entered can be identified by a single column in the table. It isn’t always the best solution, but as it is one of the easiest, it is probably what you will see most often.

because of the mass use of identify fields to quickly and easily uniquely identify a row, it is worth knowing what tables in your database are using non-identity primary keys. Again, there is not a requirement that a primary key be an identity value, but it is still worth knowing what’s going on in your database.

Detecting Primary Keys that are not Identity Fields

So, how do you find the tables with primary keys that are not identity values? Simple, you use this code:

SELECT  i.name AS IndexName ,
        OBJECT_NAME(ic.OBJECT_ID) AS TableName ,
        COL_NAME(ic.OBJECT_ID, ic.column_id) AS ColumnName ,
        ( SELECT    is_identity
          FROM      sys.columns c
                    INNER JOIN sys.tables t ON c.object_id = t.object_id
          WHERE     c.name = COL_NAME(ic.OBJECT_ID, ic.column_id)
                    AND t.name = OBJECT_NAME(ic.OBJECT_ID)
        ) AS 'identity'
FROM    sys.indexes AS i
        INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
                                              AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1
ORDER BY OBJECT_NAME(ic.OBJECT_ID)

The query goes through the system index tables for your database and returns the primary key index name, the table that index is in, the column used in the primary key as well as if the primary key is an identity of not. If multiple columns are used in the key multiple rows will be returned in sequence.

 

You can use this information to highlight those primary key indexes you may want to double check for consistency and that nothing has to be changed to make sure the uniqueness requirement of the primary key is not potentially violated.

 

[divider]

Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com.

Click here to contact Chris or to follow him on twitter.

Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp. New in 2015 WaterOx Consulting is introducing the SQLPunch series of half day training events designed to help others learn to efficiently and effectively use SQL Server.

He is the founding president of PASSDC and organizes the annual SQLSaturday for the DC area. Chris frequently speaks at and attends SQL Server events, sharing his passion for all things SQL Server.

In 2012 Chris was a finalist in the worldwide Exceptional DBA competition and in 2014 he received the Microsoft MVP award in recognition of his open sharing of his knowledge with the technical community. His blog is currently syndicated to SQLServerCentral.com and ToadWorld.com