|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
PRIMARY KEYIs 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 Show quote "DazedAndConfused" <AceMago***@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) > > ?? > > I think that the difference between this two method
only is how to define primary key, But primary key always is a Constraint on our tables. I think that it's no difference between this two method in how to work primary key. +989125332260 *** Sent via Developersdex http://www.developersdex.com *** 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. Show quote "DazedAndConfused" <AceMago***@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: Show quote >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? Show quote "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: > >>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) >> >>?? >> >> >> 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. Show quote "DazedAndConfused" <AceMago***@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... > > 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) > >> > >>?? > >> > >> > >> > > fyi: an FK can use either
Brian Selzer wrote: Show quote >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. > > > 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. Show quote "DazedAndConfused" <AceMago***@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... >> 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) >>> >>>?? >>> >>> >>> > > 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. Show quote "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" <AceMago***@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... >>> 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) >>>> >>>>?? >>>> >>>> >>>> >> >> > > On Fri, 16 Sep 2005 13:38:02 -0400, DazedAndConfused wrote:
>Right now I have a lot of tables that have a foreign key constraint in a Hi DazedAndConfused,>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? SQL Server will create an index for PRIMARY KEY and UNIQUE constraints, but not for a FOREIGN KEY constraints (though it is often wise to manually add a supporting index). > Am I Integrity should always be your first concern. Define all the>creating a mess and a lot of overhead? I am trying to find out if you can go >too far with relationships and constraints. constraints that are needed to ensure that your integrity remains intact. Worrying about the overhead this introduces comes later. The cost of reparing corrupted data is always higher! Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) I only mentioned PK and unique constraints. I did not say that FKs have
indexes created; they do NOT. FKs define how you want your data to behave, but SQL Server does not enforce that using indexes. It is difficult to go too far with relationships and constraints. It is easy to go overboard with indexes. Show quote "DazedAndConfused" <AceMago***@yahoo.com> wrote in message news:OaEkpVuuFHA.2840@TK2MSFTNGP10.phx.gbl... > 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" <AceMago***@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... >>>> 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. Show quote "DazedAndConfused" <AceMago***@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) > > ?? > >  |
|||||||||||||||||||||||
Other interesting topics