Create table with named versus un-named PRIMARY KEY

Question

Create table with named versus un-named PRIMARY KEY

DB_Newbie2007

SSCarpal Tunnel

Points: 4449
More actions
August 27, 2010 at 1:21 pm

#148553

Ok... need some clarification here:
What is the difference (if any) between
------- Statement 1: Use named constraint
CREATE TABLE TempCheck (RowNo int, MyVal int,
CONSTRAINT [PK_TempCheck] PRIMARY KEY (RowNo, MyVal))
INSERT INTO TempCheck
SELECT '1','1' UNION
SELECT '1','2' UNION
INSERT INTO TempCheck
SELECT '1','1'
SELECT * FROM TempCheck
DROP TABLE TempCheck
------- Statement 2: PRIMARY KEY IS CONSTRAINT
CREATE TABLE TempCheck (RowNo int, MyVal int,
PRIMARY KEY (RowNo, MyVal))
INSERT INTO TempCheck
SELECT '1','1' UNION
SELECT '1','2' UNION
INSERT INTO TempCheck
SELECT '1','1'
SELECT * FROM TempCheck
DROP TABLE TempCheck
The only difference I can see is that the CONSTRAINT allows the developer to create a specific named key (('PK_TempCheck'), where the other table without the CONSTRAINT creates a "system-generated" primary key ('PK__TempCheck__4850AF91').
Thanks! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 1

From a structural stand point, they're the same. But, using the system generated keys becomes problematic when you need to support multiple environments for the same database. Let's say you have a production system and a dev system. Now, you let the code generate primary key names. Then, you want to compare databases, so you either write some grungy TSQL code or you pick up a third party tool like Red Gate SQL Compare. Now, when you run the compare out of the box, every time, those PK's come up as different because of the name.

Not the end of the world, but for clarity and documentation, I prefer using my own names on PK's & FK's and other constructs that would otherwise get a system generated name.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

I agree that it is better to create you own names, instead of letting the system generate them. In addition to the problem with database compare, there is also the problem with the error messages from contraint violations being more cryptic than necessary.

I prefer to have a standard naming convention for all constraints. For example: PK_table_name for primary keys, DF_tablename__column_name for defaults, FK_foreign_key_table__referenced_table for foreign keys, CHK_table_name__constraint_description for check constraints, and AK_table_name__column_list for unique contraints.

WayneS SSC Guru Points: 95461 More actions · Answer 3

I was going to comment about the readability of error messages, but I see that Michael has beat me to it. This can be especially helpful if the error is caused by a trigger. For some reason, people forget about them. Seeing an error dealing with a different table that what you're working with will clue you in to look there.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

RonKyle SSC-Dedicated Points: 31560 More actions · Answer 4

AK_table_name__column_list for unique contraints

What does AK stand for? Otherwise you and I create the names the same way.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

RonKyle (8/31/2010)
AK_table_name__column_list for unique contraints
What does AK stand for? Otherwise you and I create the names the same way.

AK = Alternate Key