Create a Primary Key Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create a Primary Key Column

Author

Topic

janets20
Starting Member

12 Posts

Posted - 2010-05-28 : 09:55:07

Hi,

which is a better way.

1) while creating the table define the primary key

2) First create a table and add Primary Key constraint

Regards
Janet

Kristen
Test

22859 Posts

Posted - 2010-05-28 : 10:36:20

Either IMHO

If creating an @TableVar table you HAVE to do it in the DEFINE statement

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-28 : 10:44:40

Method 1 will have minimum code

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-05-28 : 11:46:46

although it occurs to me that there is a trap there


CREATE TABLE MyTable
(
    PKCol1 INT PRIMARY KEY,
    PKCol2 INT PRIMARY KEY,
    Col3 ...
)

and

CREATE TABLE MyTable
(
    PKCol1 INT,
    PKCol2 INT,
    Col3 ...
    PRIMARY KEY
    (
        PKCol2,
        PKCol1
    )
)

or

CREATE TABLE MyTable
(
    PKCol1 INT,
    PKCol2 INT,
    Col3 ...
    CONSTRAINT [PK_MyName]
    PRIMARY KEY CLUSTERED
    (
        PKCol2,
        PKCol1
    )
    WITH (IGNORE_DUP_KEY = OFF)
)

Option 1 does not allow the order of the keys to be stated - important that most selective key-field is first - but it may not be first in the column definition list.

Option 2 allows ordering of keys, but not much else (but is quite nice because you can see the ordering and which fields are in the PK - separately for the hundreds

of defined columns

Option 3 allows you to put a Name on the PK which makes it MUCH easier to drop etc (well, maybe not the PK but for other constraints - such as DEFAULTS - its very helpful to have them named)

Option 3 also allows other options to be specified.

And at that point you might as well put it in a separate statement 'coz it is as-good-as a separate statement by then

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources