Primary key

[Guest]

Hi,

Some of my Tables don't have a column that could be used for a primary key
definition. All tables have indexes defined, however.

Is there any benefit to make an extra column to define the primary key, even
if I will not use this column in queries?

Thanks,

Lubomir

 

[Guest]

Lubomir,

Is there some combination of columns that can guarantee unique rows? Or is
there the potential for duplicate rows?

Kerry Moorman

 

[Guest]

Kerry,

There will never be two identical rows. However, a value in a column can be
in another row also. So, each column can have multiple occurences of the
value.

Even if the table doesn't have a primary key, it has at least the index.

 

[Guest]

Lubomir,

Whatever combination of columns guarantees that there will never be two
identical rows is the primary key.

So you can just designate that combination of columns as a composite primary
key.

Kerry Moorman

 

[Guest]

Kerry,

Yes, I found I can do it. expet one table, if I want to do it, I owuld have
to make the primary composite key from all columns.

If I don't have a primary key but have an index, is it any difference from
performance point view in comparison with the same table having the primary
key?

Thanks,
Lubomir

 

[Earl]

I think you are looking at the wrong end of the horse. As part of your
datatabase design, one of the earliest steps will be to identify the primary
key. By definition, a primary key is used to uniquely identify each
instance of an entity (i.e., each row). As Kerry has noted, the PK is how
you GUARANTEE that uniqueness for each row. If you couldn't guarantee
uniqueness, how could you ever know for sure that the row you found was the
data you were looking for? Sometimes it might take multiple columns to
define a primary key. Personally, I shy away from such a structure and use
surrogate identity keys (a discussion all unto itself).

Note also that an index is unrelated to the concept of a primary key (altho
the primary key may be indexed). The folks over in the
microsoft.public.sqlserver.programming forum can elaborate on this to the
nth degree (or at least to BCNF) if you are interested.

 

[Cor Ligthert [MVP]]

Lubomir,

Yes and you should not use this column in Queries.

The GUID is made for this and acting better than the autoidentifier in
DataTable situations.

http://msdn2.microsoft.com/en-us/library/aa373931.aspx

Cor

 

[Miha Markic]

Out of curisoity, if your table won't partecipate in queries, why do you
need indexes?

 

[Miha Markic]

Lubomir,

Yes and you should not use this column in Queries.

The GUID is made for this and acting better than the autoidentifier in
DataTable situations.

Autoidentifiers are much more easier to read by humans and thus it is easier
to identify the row when one has a PK value.

 

[Cor Ligthert [MVP]]

Miha,

I follow what Earl has written (in my idea he wrote that it is not a good
idea to make it usable for the computer and for humns) about what you
replied to me.

Cor

 

[Guest]

Thanks for all those answers and ideas.

Lubomir