Advantage of Using Constraint when adding FK or PK

Question

Post reply

Advantage of Using Constraint when adding FK or PK

ramirez.sebastian

SSC Veteran

Points: 251
More actions
April 21, 2010 at 5:53 pm

#234134

Hi All,
I've been reading some sql tutorials and doing some exercise and found that a Primary Key can be added without the use of the Constraint, and I was wondering why should i use the constraint?
EX:
CREATE TABLE Test(
Testid INT Primary Key
,blabla VARCHAR(10)
);
OR
CREATE TABLE Test(
Testid INT
CONSTRAINT PKTestTestId
Primary Key
,blabla VARCHAR(10)
)

Viewing 8 posts - 1 through 7 (of 7 total)

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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 1

Shot in the dark, but I feel lucky. :hehe:

ANSI standard?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Since it's an optional key word, I suspect as well that it's to support the ANSI standard. It may also be to keep the language consistent because you have to use the CONSTRAINT key word when adding a primary key using the ALTER TABLE syntax.

"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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

ramirez.sebastian (4/21/2010)...I was wondering why should i use the constraint?

"constraint" syntax allows you to specify the name of the underlying index so to comply with whatever naming convention you have in place.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

ramirez.sebastian SSC Veteran Points: 251 More actions · Answer 4

thank you all, for your answers. Been able to identify the index sounds like a good enough reason for me to use the constraint. If there are any other advantages I will be glad to hear them.

Thanks again 🙂

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

ramirez.sebastian (4/23/2010)
thank you all, for your answers. Been able to identify the index sounds like a good enough reason for me to use the constraint. If there are any other advantages I will be glad to hear them.
Thanks again 🙂

If the table is a Temp Table, there's actually a DISADVANTAGE to using CONSTRAINT to name the PK... a constraint is one of those objects whose name must be unique in the entire database. If you name a PK on a Temp Table using CONSTRAINT, then whatever procedure you're running that creates the Temp Table can only run one at a time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

Jeff Moden (4/23/2010)
If the table is a Temp Table, there's actually a DISADVANTAGE to using CONSTRAINT to name the PK... a constraint is one of those objects whose name must be unique in the entire database.

Good catch on that specific case.

On the other hand, when working with "permanent" objects I would keep naming PK by resorting to CONSTRAINT keyword.