Primary Key Properties in Scripted Creation

Question

Post reply

Primary Key Properties in Scripted Creation

Dobermann

SSChasing Mays

Points: 613
More actions
April 16, 2010 at 5:52 pm

#223528

I am new to T-SQL and cannot understand why the following creates a PK with additional alpha numerics after the name in the Keys section:
CREATE TABLE OWNERS (OwnerIDINT PRIMARY KEY IDENTITY)
Using the Object Explorer in MS SQL MS 2008, under Keys for the new table, it shows:
PK__OWNERS__8193859807F6335A
Why is it adding all the numbers (and alpha) and more importantly, how can I create it without it adding them?
TIA,
Dobermann

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Why is it adding all the numbers (and alpha) and more importantly, how can I create it without it adding them?

Why do you want to create them without all the numbers, etc?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

defining a constraint name is optional...primary key, foreign key, defaults, check constraints...all of them will be auto named unless you explicitly define it:

here's your table with the named constraint instead:

CREATE TABLE [dbo].[OWNERS] (

[OWNERID] INT IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK__OWNERS__OWNERID] PRIMARY KEY CLUSTERED (OwnerID))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dobermann SSChasing Mays Points: 613 More actions · Answer 3

bitbucket-25253 (4/16/2010)
Why do you want to create them without all the numbers, etc?

Because I like it to be clean and just contain the necessary info in the title. The other stuff is just extra baggage.

Maybe I am also anal-retentive <G>...

Dobermann

Dobermann SSChasing Mays Points: 613 More actions · Answer 4

Lowell (4/16/2010)
defining a constraint name is optional...primary key, foreign key, defaults, check constraints...all of them will be auto named unless you explicitly define it:
here's your table with the named constraint instead:
CREATE TABLE [dbo].[OWNERS] (
[OWNERID] INT IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK__OWNERS__OWNERID] PRIMARY KEY CLUSTERED (OwnerID))

Why does yours work and mine does not?

CREATE TABLE OWNERS (

OwnerIDINT NOT NULL PRIMARY KEY IDENTITY CONSTRAINT PK_OWNERS_OwnersID)

I did not use the [] on the names, as they did not need it plus IDENTITY defaults to (1,1), so I omitted that too. PKs are automagically clustered, so I omitted that. If I add a comma after NOT NULL, it does not like it.

So what am I not seeing? I am brain dead on it. I always thought the order did not matter.

Thanks again,

Dobermann

Lowell SSC Guru Points: 323518 More actions · Answer 5

it's just syntax.

After the datatype, you say constraint/nameOfConstraint/Type of constraint if you are doing it inline:

CREATE TABLE OWNERS2 (OwnerID INT CONSTRAINT [PK__OWNERS__TWO] PRIMARY KEY IDENTITY)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dobermann SSChasing Mays Points: 613 More actions · Answer 6

Thank you, Lowell. I had played around with many combinations but not the right one apparently. I will be making a note of your answer in my "book of notes" for future reference.

I appreciate your help,

Dobermann

Paul White SSC Guru Points: 150467 More actions · Answer 7

Dobermann (4/16/2010)
PKs are automagically clustered

This is true by default, but a PK will be created as non-clustered if it is added to a table that already has a clustered index.

CREATE TABLE OWNERS

(

AlternateKey INT UNIQUE CLUSTERED NOT NULL,

OwnerID INT PRIMARY KEY IDENTITY,

);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dobermann SSChasing Mays Points: 613 More actions · Answer 8

Paul White NZ (4/18/2010)
Dobermann (4/16/2010)
PKs are automagically clustered
This is true by default, but a PK will be created as non-clustered if it is added to a table that already has a clustered index.
CREATE TABLE OWNERS
(
AlternateKey INT UNIQUE CLUSTERED NOT NULL,
OwnerID INT PRIMARY KEY IDENTITY,
);

Yes, I agree. Since I was creating a new table though, I knew it had but the one PK and did not have to worry about declaring clustered. I was stuck on the syntax and neither BOL nor Google seemed to come up with an answer - at least under the search criteria that I could think of.

I noticed that while I had learned to put NOT NULL right after field type, you and many others do not. I used to think it was a matter of personal style, but after seeing the problems I had at the beginning of this post and based upon Lowell's post, I am beginning to wonder if I should change my way of ordering.

Dobermann

Paul White SSC Guru Points: 150467 More actions · Answer 9

Dobermann (4/18/2010)
Yes, I agree. Since I was creating a new table though, I knew it had but the one PK and did not have to worry about declaring clustered.

Ok. I must admit I often omit CLUSTERED / NONCLUSTERED myself in forum postings, but tend to include it in production code - there's no real reason, except that the default has changed once before (SQL 7 IIRC).

I noticed that while I had learned to put NOT NULL right after field type, you and many others do not. I used to think it was a matter of personal style, but after seeing the problems I had at the beginning of this post and based upon Lowell's post, I am beginning to wonder if I should change my way of ordering.

I am a big fan of always using NULL / NOT NULL - since the default depends on the current setting of ANSI_NULL_DFLT_OFF / ANSI_NULL_DFLT_ON. I do occasionally skip NOT NULL on PRIMARY KEYs and IDENTITY columns - but that is just laziness 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi