identity column

[Sam]

Hi,
I would like a piece of advice.
I have 3 foreign keys in a table used as primary keys for this table.
Is it useful in that case to have just one identity column that would
be used as the unique primary key, thus no need to have 3 primary keys
?
Advantages vs Drabacks ?

Regards

[Erland Sommarskog]

Sam (sa**************@voila.fr) writes:

I would like a piece of advice.
I have 3 foreign keys in a table used as primary keys for this table.
Is it useful in that case to have just one identity column that would
be used as the unique primary key, thus no need to have 3 primary keys
?
Advantages vs Drabacks ?

Sometimes this makes things easier. Sometimes it makes things more
difficult.

The advantage with the IDENTITY column are more directly apparent with
less typing and all that. The disadvantages are more subtle but once you
run into them, you often find that they are more serious.

Thus, unless there is a special reason for it, do not use a surrogate key.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Mark D Powell]

Sam, many developers seem to follow the dogma that every table must
have a unique numeric primary key (PK). I do not. The question comes
down to this: will the application or end users ever use the generated
PK? If no, then why add it? In this case there is no point.

On the other hand you need to consider if this table will have child
rows and how they will relate back to the table in question. You also
have to consider if any of the PK columns are subject to value changes
and if so how such changes will be handled.

So in my opinion the choice on how to construct the PK comes down to
how will the data be accessed and updated? Does the chosen PK meet all
the application requirements?

HTH -- Mark D Powell --

[Sam]

Maybe it's clearer with my tables. Here are the ones concerned with
only the relevant fields:

User(UserId)
Web(WebId)
Query(QueryId)
UserQueries(UserId, WebId, QueryId)

UserQueries is the trouble maker. Right now (UserId, WebId, QueryId)
are FK and PK at the same timefor this table. They must be FK anyway,
but wouldn't it be better to have something like:

UserQueries(UserQueriesId, UserId, WebId, QueryId)

with userQueriesId being Identity type ?

[Simon Hayes]

I guess you mean that your table has a composite primary key made up of
three columns, each of which is a foreign key on another table? If
that's the correct primary key according to your data model, and you
don't have any serious performance issues, then it's probably best not
to change anything.

You might use IDENTITY as an artificial key either because there is no
natural key for the table, or because the natural key is so wide
physically that it creates real performance issues. Even if you did
decide to use IDENTITY instead of the natural key, you would still keep
a UNIQUE constraint on the natural key to make sure you don't get any
duplicates. And then you would be improving performance at the cost of
more maintenance, so it's probably better not to do it unless you
really have to.

Simon

[Erland Sommarskog]

Sam (sa**************@voila.fr) writes:

Maybe it's clearer with my tables. Here are the ones concerned with
only the relevant fields:

User(UserId)
Web(WebId)
Query(QueryId)
UserQueries(UserId, WebId, QueryId)

UserQueries is the trouble maker. Right now (UserId, WebId, QueryId)
are FK and PK at the same timefor this table. They must be FK anyway,
but wouldn't it be better to have something like:

UserQueries(UserQueriesId, UserId, WebId, QueryId)

with userQueriesId being Identity type ?

It could be, but I say that the odds are poor. Unless you really some
use which mandates a surrogate key, there is no reason to have any
UserQueriesId.

If you add an IDENTITY column you should still create a UNIQUE constraint
for (UserId, WebId, QueryId).
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Simon Hayes (sq*@hayes.ch) writes:

You might use IDENTITY as an artificial key either because there is no
natural key for the table, or because the natural key is so wide
physically that it creates real performance issues.

Just to add to this: Adding the IDENTITY column could in fact
decrease your performance as well, as the table would be larger. If
there is a child table, that could use this identity column as its
FK, then that table could indeed be smaller. Then again, you could then
find that more operations on the child would require joining to the
parent table, in which case you are again losing in the IDENTITY column.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

Some real DDL will help. Never use an IDENTITY as a key, sicne by
definition it is not an attribute of the entity being modeled.
Basically it says that you don't know RM and are going back to using a
physical locator, like pointer chains, to navigate yoru data. You have
to have the relational keys anyway; if you don't, you will get
redundant duplicates. Do you really mean to destroy all your data
integrity? How do you know that this "magivalk one size fits all"
number is actually in synch everywhere it is used? You don't. So all
that IDENTITY does is make the physical storage used bigger and
therefore slower. Your code will not port. Etc.

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE Web
(web_id INTEGER NOT NULL PRIMARY KEY,
..);

CREATE TABLE Queries
(query_id INTEGER NOT NULL PRIMARY KEY,
..) ;

Now throw in some DRI actions and let the schema take care of itself
for you

UserQueries
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
web_id INTEGER NOT NULL
REFERENCES Web(web_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
query_id INTEGER NOT NULL,
REFERENCES Queries(query_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (user_id, web_id, query_id));

You might want to read a book basic relational theory and look up what
Dr. Codd said about system generatee surrogates. The fact that
IDENTITY is not a key is a matter of definition, not opinion.