473,900 Members | 3,310 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using an Identity Field as Primary Key

I want to use the Identity field (increment 1,1) as a primary key and
have a unique constraint on my other field which is of type char.
I am worried that related data in other tables may lose referntial
integrity if records in the ID table get messed up and need to be
re-entered.
Can you please advice on best way to do this. I definitely need a
numeric id field because it makes the joins and queries so much
faster.
Jul 20 '05 #1
9 19586
A foreign key can reference unique constraint columns; it doesn't have
to be the primary key.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Rathtap" <am****@yahoo.c om> wrote in message
news:b2******** *************** ***@posting.goo gle.com...
I want to use the Identity field (increment 1,1) as a primary key and
have a unique constraint on my other field which is of type char.
I am worried that related data in other tables may lose referntial
integrity if records in the ID table get messed up and need to be
re-entered.
Can you please advice on best way to do this. I definitely need a
numeric id field because it makes the joins and queries so much
faster.

Jul 20 '05 #2
Rathtap (am****@yahoo.c om) writes:
I want to use the Identity field (increment 1,1) as a primary key and
have a unique constraint on my other field which is of type char.
I am worried that related data in other tables may lose referntial
integrity if records in the ID table get messed up and need to be
re-entered.
Can you please advice on best way to do this. I definitely need a
numeric id field because it makes the joins and queries so much
faster.


Have you actually benchmarked this?

If you have a natural key, use it, and don't make your system more
complex than necessary.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* **************@ 127.0.0.1>...
Rathtap (am****@yahoo.c om) writes:
I want to use the Identity field (increment 1,1) as a primary key and
have a unique constraint on my other field which is of type char.
I am worried that related data in other tables may lose referntial
integrity if records in the ID table get messed up and need to be
re-entered.
Can you please advice on best way to do this. I definitely need a
numeric id field because it makes the joins and queries so much
faster.


Have you actually benchmarked this?

If you have a natural key, use it, and don't make your system more
complex than necessary.


The issue here is that these tables are being used to populate cubes.
We have found that there is a tremendous performance gain when the
Primary and Foreign key tables are joined using int fields as opposed
to using chars.
I therefore cannot make my foreign key reference a unique constraint.
Let me explain with an example.
Table Procedures has fields ProcID(int, PK, Identity(1,1)), ProcCode
(char) and ProcDescription (char).
Table Patient has amongst others ProcID which is a foreign key.
My dilemma is, to keep the database and the programming simple - I
want ProcID to be an identity column, however, I am worried that
should something happen to the Procedures table then the relationship
between the two tables will be lost.
What are the best practices in this situation? I simply cannot have a
char field for FK/PK.
One solution is to store both the ProcedureID and ProcedureCode fields
in the Patient table so that even if the Procedures table has to be
recreated, I can run a procedure to update the FKs in the Patient
table.
Jul 20 '05 #4
Rathtap (am****@yahoo.c om) writes:
The issue here is that these tables are being used to populate cubes.
We have found that there is a tremendous performance gain when the
Primary and Foreign key tables are joined using int fields as opposed
to using chars.
How long are the char columns?

True, that character comparison is more complex, because of collation
rules (unless you use a binary collation). But the overhead is not likely
to be more than say 20%. And that is not a tremendous difference in the
database world. :-)

Maybe there were other problems, for instance joining a char and nchar
column leads to auto-conversion that precludes use of indexes.
I therefore cannot make my foreign key reference a unique constraint.
Let me explain with an example.
Table Procedures has fields ProcID(int, PK, Identity(1,1)), ProcCode
(char) and ProcDescription (char).
Table Patient has amongst others ProcID which is a foreign key.
My dilemma is, to keep the database and the programming simple - I
want ProcID to be an identity column, however, I am worried that
should something happen to the Procedures table then the relationship
between the two tables will be lost.
What are the best practices in this situation? I simply cannot have a
char field for FK/PK.
One solution is to store both the ProcedureID and ProcedureCode fields
in the Patient table so that even if the Procedures table has to be
recreated, I can run a procedure to update the FKs in the Patient
table.


I'm not sure that I follow your example, but if you insist of having the
extra column, this is how you could do it:

CREATE TABLE parent
(parentid int IDENTITY PRIMARY KEY, -- artificial key
parentcode char(n) NOT NULL UNIQUE, -- natural key
other_columns.. .,
UNIQUE (parentid, parentcode))

CREATE TABLE child
(childid some_type NOT NULL PRIMARY KEY,
...
parentid int NOT NULL REFERENCES parent(parentid ),
parentcode char(n) NOT NULL REFERENCES parent(parentco de),
...
FOREIGN KEY (parentid, parentcode)
REFERENCES parent (parentid, parentcode)

This ensures that id:s and codes are unique in the parent table,
you don't have orphan id:s and codes in the child table, and that
id:s and codes are in sync in both tables. The price you pay is
an extra redundant UNIQUE constraint on the parent table.

Note: for brevity I have not named the constraints in this outline.
However, in actual code I recommend that you always name your
constraints (save for temp tables and table variables).

Then again, if you don't have parentcode at all in the child table,
you can always retrieve the parentcode by joining to the parent
table.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...
Rathtap (am****@yahoo.c om) writes:
The issue here is that these tables are being used to populate cubes.
We have found that there is a tremendous performance gain when the
Primary and Foreign key tables are joined using int fields as opposed
to using chars.
How long are the char columns?


The columns are not long -- 15 to 20. The problem is that there are a
few million records and that is why it is slow.
True, that character comparison is more complex, because of collation
rules (unless you use a binary collation). But the overhead is not likely
to be more than say 20%. And that is not a tremendous difference in the
database world. :-)

Maybe there were other problems, for instance joining a char and nchar
column leads to auto-conversion that precludes use of indexes.
I therefore cannot make my foreign key reference a unique constraint.
Let me explain with an example.
Table Procedures has fields ProcID(int, PK, Identity(1,1)), ProcCode
(char) and ProcDescription (char).
Table Patient has amongst others ProcID which is a foreign key.
My dilemma is, to keep the database and the programming simple - I
want ProcID to be an identity column, however, I am worried that
should something happen to the Procedures table then the relationship
between the two tables will be lost.
What are the best practices in this situation? I simply cannot have a
char field for FK/PK.
One solution is to store both the ProcedureID and ProcedureCode fields
in the Patient table so that even if the Procedures table has to be
recreated, I can run a procedure to update the FKs in the Patient
table.


I'm not sure that I follow your example, but if you insist of having the
extra column, this is how you could do it:

CREATE TABLE parent
(parentid int IDENTITY PRIMARY KEY, -- artificial key
parentcode char(n) NOT NULL UNIQUE, -- natural key
other_columns.. .,
UNIQUE (parentid, parentcode))

CREATE TABLE child
(childid some_type NOT NULL PRIMARY KEY,
...
parentid int NOT NULL REFERENCES parent(parentid ),
parentcode char(n) NOT NULL REFERENCES parent(parentco de),
...
FOREIGN KEY (parentid, parentcode)
REFERENCES parent (parentid, parentcode)

This ensures that id:s and codes are unique in the parent table,
you don't have orphan id:s and codes in the child table, and that
id:s and codes are in sync in both tables. The price you pay is
an extra redundant UNIQUE constraint on the parent table.

Note: for brevity I have not named the constraints in this outline.
However, in actual code I recommend that you always name your
constraints (save for temp tables and table variables).

Then again, if you don't have parentcode at all in the child table,
you can always retrieve the parentcode by joining to the parent
table.

This is what I want to do but am not sure is the best way to do it.
Reason being if my parentcode in the parent table is lost, how do I
match it up with the parentid in the child. If I re-insert values in
the
parent, the id's generated will be out of synch with those in the
child.
Jul 20 '05 #6
Rathtap (am****@yahoo.c om) writes:
The columns are not long -- 15 to 20. The problem is that there are a
few million records and that is why it is slow.


Even with a case-insensitive collation, I doubt that the overhead for
a varcar(20) key is more than 30% of an integer key. And that is for
join operations. If you go for the scheme I suggested, you instead
get an overhead for updates.
I'm not sure that I follow your example, but if you insist of having the
extra column, this is how you could do it:

CREATE TABLE parent
(parentid int IDENTITY PRIMARY KEY, -- artificial key
parentcode char(n) NOT NULL UNIQUE, -- natural key
other_columns.. .,
UNIQUE (parentid, parentcode))

CREATE TABLE child
(childid some_type NOT NULL PRIMARY KEY,
...
parentid int NOT NULL REFERENCES parent(parentid ),
parentcode char(n) NOT NULL REFERENCES parent(parentco de),
...
FOREIGN KEY (parentid, parentcode)
REFERENCES parent (parentid, parentcode)

This is what I want to do but am not sure is the best way to do it.
Reason being if my parentcode in the parent table is lost, how do I
match it up with the parentid in the child. If I re-insert values in
the parent, the id's generated will be out of synch with those in the
child.


I may be missing something, but the idea with the outline above is
that you cannot lose a parentcode this way.

But if you really losing sleep over losing partentcodes, then ditch
the artificial key. You cannot both have the cake and eat it.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7
Rathtap wrote:
I want to use the Identity field (increment 1,1) as a primary key and
have a unique constraint on my other field which is of type char.
I am worried that related data in other tables may lose referntial
integrity if records in the ID table get messed up and need to be
re-entered.


That would be the case even if your foreign keys are on char
fields. You can't delete the old row when there are rows in
other tables referring to it, and you can't insert another one,
because it has to be unique.

Really, though, this is what UPDATE is for.

Bill

Jul 20 '05 #8
I'm making a boatload of assumptions here but based on your statement of
using this data for cubes, here we go:

There has always been a debate on using surrogate vs or in conjunction
with natural keys in regards to warehouse data. Most material I've read
recently recommends using a surrogate key and a natural key when
populating a warehouse to build cubes. (what happens when you pull in
application data that has the same natural keys as your current
app?)Unless you are allowing write-backs to the cube data, you probably
don't need to worry about updates. You only need to worry about the
performance of your ETL processing (unless you are doing real-time
stuff) That being the case, I would use that ident key if you are doing
any type of SCD, it makes any changes to the natural key easier to
implement. Your DW should be around longer than most of your
applications (if properly used and designed). Here are some pages that
might help. Remember fact and dim table design is usually a star schema
and not as normalized as your relational tables.

http://dw.ittoolbox.com/documents/document.asp?i=1501

A good discussion on this is here:
http://www.dmreview.com/editorial/dm....cfm?EdID=4892

BTW: The guru of DW (Ralph Kimball) recommends the use of natural and
surrogate keys, if you don't know, Kimball is the Celko of data
warehousing (IMO)

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #9
>> BTW: The guru of DW (Ralph Kimball) recommends the use of natural
and surrogate keys, if you don't know, Kimball is the Celko of data
warehousing (IMO) <<

Actually, Ralph is smarter and nicer than I am -- and he is now
winning all the Reader's Choice Awards in INTELLIGENT ENTERPRISE!!
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
9131
by: Chris Foster | last post by:
I am trying to implement a very fast queue using SQL Server. The queue table will contain tens of millions of records. The problem I have is the more records completed, the the slower it gets. I don't want to remove data from the queue because I use the same table to store results. The queue handles concurrent requests. The status field will contain the following values: 0 = Waiting
4
8111
by: DCM Fan | last post by:
{CREATE TABLEs and INSERTs follow...} Gents, I have a main table that is in ONE-MANY with many other tables. For example, if the main table is named A, there are these realtionships: A-->B A-->C A-->D
112
10400
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please, share your experience in using IDENTITY as PK .
2
2922
by: Andrew Grandison | last post by:
We are converting a legacy visual foxpro system to use a SQL back-end. A number of (existing DBF) tables currently have a zero-filled primary key eg. '000255' which is just an auto-incrementing key - but always stored as a char field with leading zeros. For backward compatibility we are considering retaining this primary key and using an identity field to auto-generate the next value, then convert the new identity value into the new...
10
2136
by: teddysnips | last post by:
Application is a Work Tracking/Timesheet database. The increments of work are stored in the TimesheetItem table. This contains, inter alia, the Work Code, the Start and the Duration that the employee spent that day on a particular project. Some employees in the Network Support Department don't complete a standard 7.5 hour day for various reasons, so for every Network Support person I need to update these particular days with an amount...
4
1708
by: newtophp2000 | last post by:
We have a table that has an identity field along with 5 other domain fields. The identity field is not declared as a primary key. The table has 3.5 million records. A consultant was hired recently to provide insight. His major recommendation: modify the table to make the identity field a primary key (i.e., alter table add constraint...) Is that sound advice? Is it OK to have a table with identity but no primary keys? What would...
3
3584
by: Poul Møller Hansen | last post by:
Hi, I need an auto incrementing field that will contain values like N000001, N000002, N000003 etc. I think the way is to use the value from an identity field in a stored procedure that is triggered at insert. I can't see that it can be made in pure SQL, but Java is not a problem. Any of you that can tell me the way of doing it ?
3
8019
by: mal_k100 | last post by:
Hi All, 1. Created table in SQL Server 2K with Primary key as int Identity 2. Link to table in MS Access 2K 3. Using form in MSAccess to update the linked table I want SQL server to automatically update the int identity column as it would normally. i.e. no need to enter a value even though it is a NOT NULL field. SQL Server recognises this , allows the row details to be entered without the int identity and creates it when moving to the...
13
4452
by: bevanward | last post by:
Hi All I am finding unexpected results when inserted into a newly created table that has a field of datatype int identity (1,1). Basically the order I sort on when inserting into the table is not reflected in the order of the values from the identity field. Have I been wrong in assuming that it should reflect the order from the sort?
1
10976
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10497
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
8043
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7204
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5891
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6082
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4721
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4301
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3320
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.