Friday, February 24, 2012
Identity column as a primary key with foreign key relationships
We want to have a user_id which will be a primary key and all other tables
will be joining on this user_id. My question is
Option 1: Have user_id an identity and have foreign key on this identity
table.
Option 2: Dont use identity as user_id and generate user_id with some logic.
Our group is having mixed opion and we will go with maximum number of
suggestions we get here. Also please include why you think the option you
selected is right.
Thanks,
Arshad
--
Arshad
arshadmd-nospam@.gmail.comIDENTITY all the way unless you have good reason for genrerating your
own ID. What justification /resoning do you have for not using it?|||Arshad,
Unfortunately you will also get mixed opinions here. You may read a
response from Celko where he will insult you and insist that you never
use an identity column as a primary key. Others will tell you that it
is OK. I've been through a similar issue and here is what I've learned:
1) If your project will ever be moved to a system like Oracle, you
don't want to use Identity columns as primary keys. If you will never
move your db to another system other than SQL, then using an identity
column as a primary key is OK.
2) I personally have used identity columns as primary keys in many
different scenarios and I have never had a problem with it.
3) Creating a primary key with a stored procedure or some other logic
will work but it only creates more work in coding for you and adds
additional bulk to the table that is not necessary because you will
still have the unique identity as well as a unique primary key.
4) Using multiple columns to create your primary key (social sec. and
birthday) is possible, but that can lead to confusi table relationships
and additional bulk to each related table. I like my table
relationships to be simple and intuitive, which is why I use identity
columns as primary keys.
The bottom line is that you cannot port your db to other systems if you
do this, but if porting your code to another system is not likely to
happen, then the decision is a matter of preference for the developers.
I find using identity columns as primary keys keeps the design simple
and does not add needless bulk and clutter.|||Use an autogenerated Primary key of type integer for a primary key on which
you build relationships. If you also want to use this primary key as a
userId by which to find records this is fine say you want this to be a
customer number. However having a customer 1 followed by a customer 2 etc,,
may not be a good idea if these codes are used to allow any customer access
to the database, say via internet. Its a better idea in such a case to have
non-sequential customer identifications. So a combination of RecordId as the
primary key being automatically genereated and used for relations PK-FK and
a separate USERID field that you generate and that must also be unique is
probably better. One way to generate a non sequential ID is by using a
default value derived as follows. (rand((datepart(month,getdate()) * 100000
+ datepart(second,getdate()) * 1000 + datepart(millisecond,getdate()))) *
1000000000). This generates a random number based on the seeds of the
computer date, you can ensure that its unique by making the column a unique
index.
In my view the primary key should never be part of data being viewed or used
directly by the end user and should always be created automatically by the
database engine and should ONLY be used for that particular purpose. Other
than that the basic most generally accepted rule is make the database engine
itself do as much of the work as possible for ensuring data correctnes,
there is also the fact that there are practical situations in which you may
wnt to change a userid, if the userid is the primary key you have to do
cascading updates and this takes time. If your PK is totally outside of what
users may be allowed to use, there are no cascades involved for updates,
ever.
Also, having the database do the work, removes the onus on the individual
programmer of having to call procedures specifically for creating
Primarykeys or ensuring relationship constraints are correctly applied. If
you do this in code you WILL make mistakes and you WILL forget to call the
procedures sometime.
I've seen many apps that do this in code (still today) and have had to
transfer data from these to corrrectly structured relational tables and each
time I have found things like orphan records or duplicate keys where none
should be.
Hope ths helps and that I haven't started anyone ranting and raving :-)
RD
"Arshad" <Arshad@.discussions.microsoft.com> wrote in message
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com...
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option you
> selected is right.
> Thanks,
> Arshad
> --
> Arshad
> arshadmd-nospam@.gmail.com|||On Fri, 29 Jul 2005 08:50:01 -0700, Arshad wrote:
>Greetings All,
>We want to have a user_id which will be a primary key and all other tables
>will be joining on this user_id. My question is
>Option 1: Have user_id an identity and have foreign key on this identity
>table.
>Option 2: Dont use identity as user_id and generate user_id with some logic.
>Our group is having mixed opion and we will go with maximum number of
>suggestions we get here. Also please include why you think the option you
>selected is right.
>Thanks,
>Arshad
Hi Arshad,
In general, keys can fulfill two distinct functions.
Their first function is to provide a link between a row in a table and
an entity in the real world outside of the database. This is what I call
the business key, since in most cases, the business dictates what key to
use. If the users are employees and the HR department issues employee
numbers, than the business key is the employee number. In an American
tax-related database, SSN would be the business key. In a database that
supports the upkeep of a computer network, the username assigned by the
sysadmins for logging on to the network would be the business key. For
my dentists' customers, last name + address + date of birth might
qualify as the business key. And so on, and so on.
You should only consider having the database generate the business key
if there is at present no business key - and you'll still need to find
who'se in charge and get him or her to sign of on your proposal, since
it's not your job to change the business' processes.
The second function of a key is to link a row in one table to a related
row in (usually) another table - the well known FOREIGN KEY constraint.
In most cases, the FOREIGN KEY will refer to the PRIMARY KEY of the
related table. But it can also refer to any column (or combination of
columns) that is declared as UNIQUE in the related table.
My usual procedure is:
- First, find the business key, This one is always needed, since there
is no sense in storing data in a database if it can't be related back to
the real-world entities that it's supposed to describe.
- Second, determine of there will be any other tables referring to the
rows in this table. If there are, then determine if the business key is
a good condidate for implementing the FOREIGN KEY constraint. If it
isn't (e.g. becuase it is prone to frequent change, or because it is so
long that it would degrade performance in the database), then I'll
introduce a surrogate key - and in 99.9% of all cases, IDENTITY serves
fine as a surrogate key.
That leaves me with two possible designs:
1. Business key is suitable to be used in the FK relationship:
CREATE TABLE Tab1 (BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (BusinessKey)
)
CREATE TABLE Tab2 (BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 some_datetype [NOT] NULL,
other columns,
PRIMARY KEY (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (BusinessKey)
ON UPDATE CASCADE
ON DELETE NO ACTION
)
2. Business key is not suitable for FK relationship - use surrogate key:
CREATE TABLE Tab1 (Tab1_ID int NOT NULL IDENTITY,
BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (Tab1_ID),
UNIQUE (BusinessKey)
)
CREATE TABLE Tab2 (Tab2_ID int NOT NULL IDENTITY,
BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 int [NOT] NULL,
other columns,
PRIMARY KEY (Tab2ID),
UNIQUE (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (Tab1_ID
ON UPDATE NO ACTION -- Note this change!!
ON DELETE NO ACTION
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Arshad wrote:
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
i use this method 99% of the time and have no problems.
the bigger issue for me is what should be the clustered index on the
table.|||Arshad:
Identities do make things easier, but they do bring along some extra
baggage that you need to be aware of.
As Elroyskimms suggested problems regarding another vendor's DBMS
(although there is a work around in Oracle w/ sequences), you will also
need to consider if you ever plan on any type of replication or merging
of data between databases, as the uniqueness of the key does not exist in
this scope.
If you skin is thick enough :>), I'd recommend posting this to
microsoft.public.sqlserver.programming, and await the verbal assault from
Joe Celko. If you can get past his insults and obtuse style, he does
have a good insight on "some" issues.
Jeff Clausius
SourceGear
=?Utf-8?B?QXJzaGFk?= <Arshad@.discussions.microsoft.com> wrote in
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com:
> Greetings All,
> We want to have a user_id which will be a primary key and all other
> tables will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this
> identity table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option
> you selected is right.
> Thanks,
> Arshad
>|||Jeff Clausius wrote:
> If you skin is thick enough :>), I'd recommend posting this to
> microsoft.public.sqlserver.programming, and await the verbal assault from
> Joe Celko. If you can get past his insults and obtuse style, he does
> have a good insight on "some" issues.
I couldn't have said it better myself!
-E|||When I create a diagram, and thereby make constraints, it is nice to
have a singe field to link to that is unique. If there isn't a single
field that makes it unique then and Identity is the perfect choice.
Since I now have this unique key, why not have it the primary key?
This ID is never going to change, nor can it be changed because of the
constraints, with records from other tables referencing it. What I
really despise is when two tables are linked together by more than one
field. If I want to make other unique key constraints I can, but for
consistency I always make the Identity column the primary key.
Another thing I like to do is always name the Identity column ID. That
way I immediately know it is the identity and the primary key for the
table I am looking at. Now lets say the name of the table is
"Department". Now when I link it to the Department Table to the
Employee table, I create a field in the Employee table called
"DepartmentID", and link it to the ID column in the Department table.
So I always know that a field that just ends in "..ID" references the
Primary Key and Identity of another table, and I know the name of the
table, because it is what is in front of "ID". I never have to wonder
or look at constraints to see if a field is referencing another table.
I always know what table and field it references just by the name of the
field. Even those who hate identity columns admin there are times that
they make sense. I say if you are ever going to use them, always use
them. Consistency is the key to making things simple.
Ken Cushing
West Valley City
kcushing
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1761833.html
Identity column as a primary key with foreign key relationships
We want to have a user_id which will be a primary key and all other tables
will be joining on this user_id. My question is
Option 1: Have user_id an identity and have foreign key on this identity
table.
Option 2: Dont use identity as user_id and generate user_id with some logic.
Our group is having mixed opion and we will go with maximum number of
suggestions we get here. Also please include why you think the option you
selected is right.
Thanks,
Arshad
Arshad
arshadmd-nospam@.gmail.com
IDENTITY all the way unless you have good reason for genrerating your
own ID. What justification /resoning do you have for not using it?
|||Arshad,
Unfortunately you will also get mixed opinions here. You may read a
response from Celko where he will insult you and insist that you never
use an identity column as a primary key. Others will tell you that it
is OK. I've been through a similar issue and here is what I've learned:
1) If your project will ever be moved to a system like Oracle, you
don't want to use Identity columns as primary keys. If you will never
move your db to another system other than SQL, then using an identity
column as a primary key is OK.
2) I personally have used identity columns as primary keys in many
different scenarios and I have never had a problem with it.
3) Creating a primary key with a stored procedure or some other logic
will work but it only creates more work in coding for you and adds
additional bulk to the table that is not necessary because you will
still have the unique identity as well as a unique primary key.
4) Using multiple columns to create your primary key (social sec. and
birthday) is possible, but that can lead to confusi table relationships
and additional bulk to each related table. I like my table
relationships to be simple and intuitive, which is why I use identity
columns as primary keys.
The bottom line is that you cannot port your db to other systems if you
do this, but if porting your code to another system is not likely to
happen, then the decision is a matter of preference for the developers.
I find using identity columns as primary keys keeps the design simple
and does not add needless bulk and clutter.
|||Use an autogenerated Primary key of type integer for a primary key on which
you build relationships. If you also want to use this primary key as a
userId by which to find records this is fine say you want this to be a
customer number. However having a customer 1 followed by a customer 2 etc,,
may not be a good idea if these codes are used to allow any customer access
to the database, say via internet. Its a better idea in such a case to have
non-sequential customer identifications. So a combination of RecordId as the
primary key being automatically genereated and used for relations PK-FK and
a separate USERID field that you generate and that must also be unique is
probably better. One way to generate a non sequential ID is by using a
default value derived as follows. (rand((datepart(month,getdate()) * 100000
+ datepart(second,getdate()) * 1000 + datepart(millisecond,getdate()))) *
1000000000). This generates a random number based on the seeds of the
computer date, you can ensure that its unique by making the column a unique
index.
In my view the primary key should never be part of data being viewed or used
directly by the end user and should always be created automatically by the
database engine and should ONLY be used for that particular purpose. Other
than that the basic most generally accepted rule is make the database engine
itself do as much of the work as possible for ensuring data correctnes,
there is also the fact that there are practical situations in which you may
wnt to change a userid, if the userid is the primary key you have to do
cascading updates and this takes time. If your PK is totally outside of what
users may be allowed to use, there are no cascades involved for updates,
ever.
Also, having the database do the work, removes the onus on the individual
programmer of having to call procedures specifically for creating
Primarykeys or ensuring relationship constraints are correctly applied. If
you do this in code you WILL make mistakes and you WILL forget to call the
procedures sometime.
I've seen many apps that do this in code (still today) and have had to
transfer data from these to corrrectly structured relational tables and each
time I have found things like orphan records or duplicate keys where none
should be.
Hope ths helps and that I haven't started anyone ranting and raving :-)
RD
"Arshad" <Arshad@.discussions.microsoft.com> wrote in message
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com...
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option you
> selected is right.
> Thanks,
> Arshad
> --
> Arshad
> arshadmd-nospam@.gmail.com
|||On Fri, 29 Jul 2005 08:50:01 -0700, Arshad wrote:
>Greetings All,
>We want to have a user_id which will be a primary key and all other tables
>will be joining on this user_id. My question is
>Option 1: Have user_id an identity and have foreign key on this identity
>table.
>Option 2: Dont use identity as user_id and generate user_id with some logic.
>Our group is having mixed opion and we will go with maximum number of
>suggestions we get here. Also please include why you think the option you
>selected is right.
>Thanks,
>Arshad
Hi Arshad,
In general, keys can fulfill two distinct functions.
Their first function is to provide a link between a row in a table and
an entity in the real world outside of the database. This is what I call
the business key, since in most cases, the business dictates what key to
use. If the users are employees and the HR department issues employee
numbers, than the business key is the employee number. In an American
tax-related database, SSN would be the business key. In a database that
supports the upkeep of a computer network, the username assigned by the
sysadmins for logging on to the network would be the business key. For
my dentists' customers, last name + address + date of birth might
qualify as the business key. And so on, and so on.
You should only consider having the database generate the business key
if there is at present no business key - and you'll still need to find
who'se in charge and get him or her to sign of on your proposal, since
it's not your job to change the business' processes.
The second function of a key is to link a row in one table to a related
row in (usually) another table - the well known FOREIGN KEY constraint.
In most cases, the FOREIGN KEY will refer to the PRIMARY KEY of the
related table. But it can also refer to any column (or combination of
columns) that is declared as UNIQUE in the related table.
My usual procedure is:
- First, find the business key, This one is always needed, since there
is no sense in storing data in a database if it can't be related back to
the real-world entities that it's supposed to describe.
- Second, determine of there will be any other tables referring to the
rows in this table. If there are, then determine if the business key is
a good condidate for implementing the FOREIGN KEY constraint. If it
isn't (e.g. becuase it is prone to frequent change, or because it is so
long that it would degrade performance in the database), then I'll
introduce a surrogate key - and in 99.9% of all cases, IDENTITY serves
fine as a surrogate key.
That leaves me with two possible designs:
1. Business key is suitable to be used in the FK relationship:
CREATE TABLE Tab1 (BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (BusinessKey)
)
CREATE TABLE Tab2 (BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 some_datetype [NOT] NULL,
other columns,
PRIMARY KEY (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (BusinessKey)
ON UPDATE CASCADE
ON DELETE NO ACTION
)
2. Business key is not suitable for FK relationship - use surrogate key:
CREATE TABLE Tab1 (Tab1_ID int NOT NULL IDENTITY,
BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (Tab1_ID),
UNIQUE (BusinessKey)
)
CREATE TABLE Tab2 (Tab2_ID int NOT NULL IDENTITY,
BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 int [NOT] NULL,
other columns,
PRIMARY KEY (Tab2ID),
UNIQUE (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (Tab1_ID
ON UPDATE NO ACTION-- Note this change!!
ON DELETE NO ACTION
)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Arshad wrote:
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
i use this method 99% of the time and have no problems.
the bigger issue for me is what should be the clustered index on the
table.
|||Arshad:
Identities do make things easier, but they do bring along some extra
baggage that you need to be aware of.
As Elroyskimms suggested problems regarding another vendor's DBMS
(although there is a work around in Oracle w/ sequences), you will also
need to consider if you ever plan on any type of replication or merging
of data between databases, as the uniqueness of the key does not exist in
this scope.
If you skin is thick enough :>), I'd recommend posting this to
microsoft.public.sqlserver.programming, and await the verbal assault from
Joe Celko. If you can get past his insults and obtuse style, he does
have a good insight on "some" issues.
Jeff Clausius
SourceGear
=?Utf-8?B?QXJzaGFk?= <Arshad@.discussions.microsoft.com> wrote in
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com:
> Greetings All,
> We want to have a user_id which will be a primary key and all other
> tables will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this
> identity table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option
> you selected is right.
> Thanks,
> Arshad
>
|||Jeff Clausius wrote:
> If you skin is thick enough :>), I'd recommend posting this to
> microsoft.public.sqlserver.programming, and await the verbal assault from
> Joe Celko. If you can get past his insults and obtuse style, he does
> have a good insight on "some" issues.
I couldn't have said it better myself!
-E
|||When I create a diagram, and thereby make constraints, it is nice to have a singe field to link to that is unique. If there isn't a single field that makes it unique then and Identity is the perfect choice. Since I now have this unique key, why not have it the primary key? This ID is never going to change, nor can it be changed because of the constraints, with records from other tables referencing it. What I really despise is when two tables are linked together by more than one field. If I want to make other unique key constraints I can, but for consistency I always make the Identity column the primary key.
Another thing I like to do is always name the Identity column ID. That way I immediately know it is the identity and the primary key for the table I am looking at. Now lets say the name of the table is "Department". Now when I link it to the Department Table to the Employee table, I create a field in the Employee table called "DepartmentID", and link it to the ID column in the Department table. So I always know that a field that just ends in "..ID" references the Primary Key and Identity of another table, and I know the name of the table, because it is what is in front of "ID". I never have to wonder or look at constraints to see if a field is referencing another table. I always know what table and field it references just by the name of the field. Even those who hate identity columns admin there are times that they make sense. I say if you are ever going to use them, always use them. Consistency is the key to making things simple.
Ken Cushing
West Valley City
Identity column as a primary key with foreign key relationships
We want to have a user_id which will be a primary key and all other tables
will be joining on this user_id. My question is
Option 1: Have user_id an identity and have foreign key on this identity
table.
Option 2: Dont use identity as user_id and generate user_id with some logic.
Our group is having mixed opion and we will go with maximum number of
suggestions we get here. Also please include why you think the option you
selected is right.
Thanks,
Arshad
Arshad
arshadmd-nospam@.gmail.comIDENTITY all the way unless you have good reason for genrerating your
own ID. What justification /resoning do you have for not using it?|||Arshad,
Unfortunately you will also get mixed opinions here. You may read a
response from Celko where he will insult you and insist that you never
use an identity column as a primary key. Others will tell you that it
is OK. I've been through a similar issue and here is what I've learned:
1) If your project will ever be moved to a system like Oracle, you
don't want to use Identity columns as primary keys. If you will never
move your db to another system other than SQL, then using an identity
column as a primary key is OK.
2) I personally have used identity columns as primary keys in many
different scenarios and I have never had a problem with it.
3) Creating a primary key with a stored procedure or some other logic
will work but it only creates more work in coding for you and adds
additional bulk to the table that is not necessary because you will
still have the unique identity as well as a unique primary key.
4) Using multiple columns to create your primary key (social sec. and
birthday) is possible, but that can lead to confusi table relationships
and additional bulk to each related table. I like my table
relationships to be simple and intuitive, which is why I use identity
columns as primary keys.
The bottom line is that you cannot port your db to other systems if you
do this, but if porting your code to another system is not likely to
happen, then the decision is a matter of preference for the developers.
I find using identity columns as primary keys keeps the design simple
and does not add needless bulk and clutter.|||Use an autogenerated Primary key of type integer for a primary key on which
you build relationships. If you also want to use this primary key as a
userId by which to find records this is fine say you want this to be a
customer number. However having a customer 1 followed by a customer 2 etc,,
may not be a good idea if these codes are used to allow any customer access
to the database, say via internet. Its a better idea in such a case to have
non-sequential customer identifications. So a combination of RecordId as the
primary key being automatically genereated and used for relations PK-FK and
a separate USERID field that you generate and that must also be unique is
probably better. One way to generate a non sequential ID is by using a
default value derived as follows. (rand((datepart(month,getdate()) * 100000
+ datepart(second,getdate()) * 1000 + datepart(millisecond,getdate()))) *
1000000000). This generates a random number based on the seeds of the
computer date, you can ensure that its unique by making the column a unique
index.
In my view the primary key should never be part of data being viewed or used
directly by the end user and should always be created automatically by the
database engine and should ONLY be used for that particular purpose. Other
than that the basic most generally accepted rule is make the database engine
itself do as much of the work as possible for ensuring data correctnes,
there is also the fact that there are practical situations in which you may
wnt to change a userid, if the userid is the primary key you have to do
cascading updates and this takes time. If your PK is totally outside of what
users may be allowed to use, there are no cascades involved for updates,
ever.
Also, having the database do the work, removes the onus on the individual
programmer of having to call procedures specifically for creating
Primarykeys or ensuring relationship constraints are correctly applied. If
you do this in code you WILL make mistakes and you WILL forget to call the
procedures sometime.
I've seen many apps that do this in code (still today) and have had to
transfer data from these to corrrectly structured relational tables and each
time I have found things like orphan records or duplicate keys where none
should be.
Hope ths helps and that I haven't started anyone ranting and raving :-)
RD
"Arshad" <Arshad@.discussions.microsoft.com> wrote in message
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com...
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option you
> selected is right.
> Thanks,
> Arshad
> --
> Arshad
> arshadmd-nospam@.gmail.com|||On Fri, 29 Jul 2005 08:50:01 -0700, Arshad wrote:
>Greetings All,
>We want to have a user_id which will be a primary key and all other tables
>will be joining on this user_id. My question is
>Option 1: Have user_id an identity and have foreign key on this identity
>table.
>Option 2: Dont use identity as user_id and generate user_id with some logic
.
>Our group is having mixed opion and we will go with maximum number of
>suggestions we get here. Also please include why you think the option you
>selected is right.
>Thanks,
>Arshad
Hi Arshad,
In general, keys can fulfill two distinct functions.
Their first function is to provide a link between a row in a table and
an entity in the real world outside of the database. This is what I call
the business key, since in most cases, the business dictates what key to
use. If the users are employees and the HR department issues employee
numbers, than the business key is the employee number. In an American
tax-related database, SSN would be the business key. In a database that
supports the upkeep of a computer network, the username assigned by the
sysadmins for logging on to the network would be the business key. For
my dentists' customers, last name + address + date of birth might
qualify as the business key. And so on, and so on.
You should only consider having the database generate the business key
if there is at present no business key - and you'll still need to find
who'se in charge and get him or her to sign of on your proposal, since
it's not your job to change the business' processes.
The second function of a key is to link a row in one table to a related
row in (usually) another table - the well known FOREIGN KEY constraint.
In most cases, the FOREIGN KEY will refer to the PRIMARY KEY of the
related table. But it can also refer to any column (or combination of
columns) that is declared as UNIQUE in the related table.
My usual procedure is:
- First, find the business key, This one is always needed, since there
is no sense in storing data in a database if it can't be related back to
the real-world entities that it's supposed to describe.
- Second, determine of there will be any other tables referring to the
rows in this table. If there are, then determine if the business key is
a good condidate for implementing the FOREIGN KEY constraint. If it
isn't (e.g. becuase it is prone to frequent change, or because it is so
long that it would degrade performance in the database), then I'll
introduce a surrogate key - and in 99.9% of all cases, IDENTITY serves
fine as a surrogate key.
That leaves me with two possible designs:
1. Business key is suitable to be used in the FK relationship:
CREATE TABLE Tab1 (BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (BusinessKey)
)
CREATE TABLE Tab2 (BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 some_datetype [NOT] NULL,
other columns,
PRIMARY KEY (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (BusinessKey)
ON UPDATE CASCADE
ON DELETE NO ACTION
)
2. Business key is not suitable for FK relationship - use surrogate key:
CREATE TABLE Tab1 (Tab1_ID int NOT NULL IDENTITY,
BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (Tab1_ID),
UNIQUE (BusinessKey)
)
CREATE TABLE Tab2 (Tab2_ID int NOT NULL IDENTITY,
BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 int [NOT] NULL,
other columns,
PRIMARY KEY (Tab2ID),
UNIQUE (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (Tab1_ID
ON UPDATE NO ACTION -- Note this change!!
ON DELETE NO ACTION
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Arshad wrote:
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
i use this method 99% of the time and have no problems.
the bigger issue for me is what should be the clustered index on the
table.|||Arshad:
Identities do make things easier, but they do bring along some extra
baggage that you need to be aware of.
As Elroyskimms suggested problems regarding another vendor's DBMS
(although there is a work around in Oracle w/ sequences), you will also
need to consider if you ever plan on any type of replication or merging
of data between databases, as the uniqueness of the key does not exist in
this scope.
If you skin is thick enough :> ), I'd recommend posting this to
microsoft.public.sqlserver.programming, and await the verbal assault from
Joe Celko. If you can get past his insults and obtuse style, he does
have a good insight on "some" issues.
Jeff Clausius
SourceGear
examnotes <Arshad@.discussions.microsoft.com> wrote in
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com:
> Greetings All,
> We want to have a user_id which will be a primary key and all other
> tables will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this
> identity table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option
> you selected is right.
> Thanks,
> Arshad
>|||Jeff Clausius wrote:
> If you skin is thick enough :> ), I'd recommend posting this to
> microsoft.public.sqlserver.programming, and await the verbal assault from
> Joe Celko. If you can get past his insults and obtuse style, he does
> have a good insight on "some" issues.
I couldn't have said it better myself!
-E|||When I create a diagram, and thereby make constraints, it is nice to
have a singe field to link to that is unique. If there isn't a single
field that makes it unique then and Identity is the perfect choice.
Since I now have this unique key, why not have it the primary key?
This ID is never going to change, nor can it be changed because of the
constraints, with records from other tables referencing it. What I
really despise is when two tables are linked together by more than one
field. If I want to make other unique key constraints I can, but for
consistency I always make the Identity column the primary key.
Another thing I like to do is always name the Identity column ID. That
way I immediately know it is the identity and the primary key for the
table I am looking at. Now lets say the name of the table is
"Department". Now when I link it to the Department Table to the
Employee table, I create a field in the Employee table called
"DepartmentID", and link it to the ID column in the Department table.
So I always know that a field that just ends in "..ID" references the
Primary Key and Identity of another table, and I know the name of the
table, because it is what is in front of "ID". I never have to wonder
or look at constraints to see if a field is referencing another table.
I always know what table and field it references just by the name of the
field. Even those who hate identity columns admin there are times that
they make sense. I say if you are ever going to use them, always use
them. Consistency is the key to making things simple.
Ken Cushing
West Valley City
kcushing
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1761833.html
identity column
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 ?
RegardsSam (samuel.berthelot@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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 --|||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 ?|||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|||Sam (samuel.berthelot@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Simon Hayes (sql@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.