PRIMARY KEY

Is there any difference between defining a primary key this way:
personID int not null
PRIMARY KEY CLUSTERED(personID)
and this way:
CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
'DAC,
With the first you're going to end up with a system generated name for the
PRIMARY KEY that will not match your environment's naming convention.
HTH
Jerry
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||Well, in the second way you get to choose the name for the constraint.
The first syntax is called a 'column level constraint' and the second is
called a 'table level constraint'. The two you have shown here will behave
internally exactly the same way.
With a table level constraint, you can have a composite key; a column level
constraint is on a single column.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||assuming that both are in valid statements, you get a randomly named key
in the 1st and a name of your choosing in the 2nd.
other than that, i don't believe so.
DazedAndConfused wrote:

>Is there any difference between defining a primary key this way:
>personID int not null
>PRIMARY KEY CLUSTERED(personID)
>and this way:
>CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
>'
>
>|||Yes. One has a system generated constraint name and the other has a user
specified constraint name. I prefer to use the user specified constraint
name because it makes it easier to coerce the optimizer to use the implicit
index for the primary key constraint--WITH(INDEX(PK_CP_personID))--and to
query sysobjects and sysindexes during troubleshooting.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||Thank you,
When I go into entrerprise managers Diagram, under Indexs/Keys tab there is
a check box for Create Unique and two Radio buttons:
Constraint and Index
What is the difference between constraint and index?
How would I create an index and why?
"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
> assuming that both are in valid statements, you get a randomly named key
> in the 1st and a name of your choosing in the 2nd.
> other than that, i don't believe so.
> DazedAndConfused wrote:
>|||I think you can create foreign key relationships if you use a unique
constraint instead of a unique index, but I'm not absolutely certain about
that. From a performance standpoint, there is no difference, because a
unique constraint always creates a unique index.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Thank you,
> When I go into entrerprise managers Diagram, under Indexs/Keys tab there
is
> a check box for Create Unique and two Radio buttons:
> Constraint and Index
> What is the difference between constraint and index?
> How would I create an index and why?
>
> "Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
> news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
>|||A constraint is a logical construct. By defining a constraint you are
telling SQL Server how you want your data to behave, and how you want the
system to control it. A PK constrains the data to uniqueness, and
non-nullability. A unique constraint constrains the data to uniqueness.
Right now, SQL Server physically enforces your constraint requirements by
building an index, but theoretically, it could enforce the requirements in
another way.
An index is a physical construct that is primarily used for performance
reasons (but is also used to provide the enforcement of uniqueness
constraints). There are all kinds of reasons to build indexes, and you
should find a good book or site on SQL Server query tuning to get more
information about the design and use of indexes.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Thank you,
> When I go into entrerprise managers Diagram, under Indexs/Keys tab there
> is a check box for Create Unique and two Radio buttons:
> Constraint and Index
> What is the difference between constraint and index?
> How would I create an index and why?
>
> "Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
> news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
>|||fyi: an FK can use either
Brian Selzer wrote:

>I think you can create foreign key relationships if you use a unique
>constraint instead of a unique index, but I'm not absolutely certain about
>that.
>
>|||Right now I have a lot of tables that have a foreign key constraint in a
column named "updateby" that must have an entry in the "users" table in the
"userID" column. Does that mean I have indexes all over the place? Am I
creating a mess and a lot of overhead? I am trying to find out if you can go
too far with relationships and constraints.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eS%23$$IuuFHA.3548@.tk2msftngp13.phx.gbl...
>A constraint is a logical construct. By defining a constraint you are
>telling SQL Server how you want your data to behave, and how you want the
>system to control it. A PK constrains the data to uniqueness, and
>non-nullability. A unique constraint constrains the data to uniqueness.
>Right now, SQL Server physically enforces your constraint requirements by
>building an index, but theoretically, it could enforce the requirements in
>another way.
> An index is a physical construct that is primarily used for performance
> reasons (but is also used to provide the enforcement of uniqueness
> constraints). There are all kinds of reasons to build indexes, and you
> should find a good book or site on SQL Server query tuning to get more
> information about the design and use of indexes.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
>