SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Identity and Primary Keys
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 8

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 12/05/2001 :  00:33:30  Show Profile  Reply with Quote
Supposing if i make a integer field as a pk , how will i tackle concurrent inserts and still keep the integer field unique(in the sense that there shouldnt be inserts fails).

i have this problem. i applied this approches but it fails.

i read the max value of trnno(which is my pk) and add a record by adding 1 to it, but when there are more then one insert at the same time , it ends up failing one insert.

How will i tackle such a problem.

i thought of using identity column but i cant afford to miss any values and morever when some1 adds a record i have to display back trnno field(which is hard capturing for me on a insert).

Any suggestion



-------------------------


Edited by - sdnazim on 12/05/2001 00:34:49
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 12/05/2001 :  02:22:10  Show Profile  Reply with Quote
sdNazim,

Look at using "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"

DavidM
It gets windy at a thousand feet...."Tutorial D"
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 12/05/2001 :  04:20:17  Show Profile  Reply with Quote
Thanx David, that helped.

but therez another problem.

in my project as there are different types of transactions , so for keeping track of what should be the next pk value for the each transaction type , i maintain a table like
whose schema is serial_no(sno ,trntype, number) whenever am adding i read the number value and add 1 to it , to the corresponding trntype.
again am confused here on how to apply serilizability on concurrent updates. this is from an Asp page.
can u xplain me in little more detail

Thanx

-------------------------
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 12/06/2001 :  02:07:59  Show Profile  Reply with Quote
knock knock any1 there.

Did i post this Q in wrong forum........? ,i thought its concerning Primary key issue and i posted it.



-------------------------
Nazim
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/06/2001 :  06:39:08  Show Profile  Visit nr's Homepage  Reply with Quote
you can

begin tran
add one to next ID field
if error
rollback tran - return
insert rec with new id
if error
rollback tran - return
commit tran

or use a trigger to add one to the max id on insert.

These will both ensure the id is consecutive.


==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 12/07/2001 :  06:25:43  Show Profile  Reply with Quote
nr i wrote similar code for inserting , but couple of times 2 transactions ended up having the same record no.( i didnt specify primary key on the field. coz, i wanted to make sure that it doesnt adds records with the same no ... now tht i have added primary key , it ended up in rejecting 1 user request..which is what i want to avoid).

how can i handle this better?

Thanx for the help.


-------------------------
Nazim -- "Success is when Preparedness meets Opportunity"

Edited by - sdnazim on 12/07/2001 06:26:43
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/07/2001 :  06:52:26  Show Profile  Visit nr's Homepage  Reply with Quote
Which one did you use?
It is important not to

begin tran
select @id = nextid + 1 from tbl
update tbl set nextid = @id
insert rec
commit tran

as this will not lock the table until the update.

begin tran
update tbl set @id = nextid + 1, nextid = nextid + 1
insert rec
commit tran
should work

as should
begin tran
update tbl set nextid = nextid + 1
select @id = nextid from tbl
insert rec
commit tran






==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Mr.
Starting Member

1 Posts

Posted - 04/01/2002 :  16:04:37  Show Profile  Reply with Quote
The problem I have with Autonumber fields is when they are used as foreign keys and specific values are tied into the business logic.

Let's say for example I have an application which has some secure features which I don't want every user to have access to. So I set up these tables:

SECURITY
--------
ID (autonumber)
LEVEL varchar(20)

MYUSERS
------
ID (autonumber)
NAME varchar(50)
SECURITYID integer


Then I add rows for "ADMINISTRATOR" and "USER" to the SECURITY table and update the user table accordingly assigning the id value to each user for the corresponding security level. Let's also assume "ADMINISTRATOR" has ID 1 and "USER" has ID 2.

I can create a simple stored proc to return whether or not the user is an administrator - now here is where things fall apart.

create proc IsAnAdmin(@UserID int) as
declare @SecID integer

Select @SecID = SecurityID
from MyUsers
where id = @UserID

if @SecID = 1
return 1
else
return 0

--end IsAnAdmin

As you can see the stored proc is using the actual VALUE of the ID. Using a numeric value makes things more difficult to figure out. Also, there is nothing special about the number 1 other than it represents the order in which "ADMINISTRATOR" was added to the database.

If this was part of an application which I decide to distribute then I must make sure that the client sets up the database and inserts the "ADMINISTRATOR" row before the "USER" row (and that the seed is set to 1).

Programmers use the term "Magic Number" to represent these kinds of static values. To avoid them most programming languages support enumeration which allows using a string of text to refer to the name instead of the number (e.g. adOpenStatic, adLockOptimistic versus 3,3). If the number values change the programmers do not have to look through all the code and change each value because of the alias(enumeration).

Following this line of thought I should change the MYUSERS.SECURITYID field to something like SECURITY_LEVEL varchar(20), fill in the actual data instead and rewrite the sql statement to read:

Select @IsAnAdmin = count(*)
from MyUsers
where SECURITY_LEVEL = "ADMINISTRATOR"

return IsAnAdmin

I feel much more comfortable with checking for the string "ADMINISTRATOR" than the number 1. The number 1 will still give me the "ADMINISTRATOR" row yet now I can blow it away and re-add it (to get ID 3) without any ill affects.


Go to Top of Page

Apollois
Starting Member

USA
49 Posts

Posted - 05/09/2002 :  14:54:58  Show Profile  Reply with Quote
I must say that it is apparent that Mr. Volk is not well qualified in relational database design. He makes statements that are clearly at odds with established, professionally accepted guidelines for DB design.

The main purpose of a primary key is to provide a unique identifier for relating records in one table with records in another table. Good design dictates that the PK should never be exposed to the end-user. It is NOT designed to be used for helping the end-user identify the data. Mr. Volk clearly does not understand this.

This article has so many flaws that it should be withdrawn from this web site, as it is more hurtful than helpful, especially to DB designers with limited experience.

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 05/09/2002 :  18:57:37  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
quote:

I must say that it is apparent that Mr. Volk is not well qualified in relational database design.


Wow, do you really believe that, or are you just trolling? You follow up that statement with more opinion, so it sounds like you're sincere in your belief. I just have to jump in here and say that I believe Rob Volk to be one of the most knowledgeable RDBMS guys around who isn't out trying to pump up his own book.

Granted, the position I'm coming from is one of a definite preference toward application and a bit of disdain for theory. But, I am VERY interested in your "established, professionally accepted guidelines for DB design". I consider myself to be among the most professional in my approach to application development, including DB Design. Would you please post a reference, preferably a link to an authoritative source such as a highly respected book on relational design? If you could include specific chapters or page numbers, that would be a great help.

quote:
Good design dictates that the PK should never be exposed to the end-user.


Now granted, I haven't taken a lot of design classes, but I have worked with some excellent designers, and I have NEVER heard this claim. Are you saying that, in fact, we should go to effort to actually HIDE the PK from the end-user? It sounds that way when you use words like NEVER.

While I have a bit of an affinity toward using IDENTITY fields (yes, I'm actually disagreeing with Mr. Volk's article here) I do believe that Rob has put forth a good argument and people should seriously consider it. I'd say this is an excellent article and should be retained as a good counter-balance to the auto-number craze. And the longer I'm at this site, the more I hear people asking about "how to delete duplicates" which Rob points out in his article.

Edited by - AjarnMark on 05/09/2002 19:00:50
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 05/09/2002 :  19:21:30  Show Profile  Visit Merkin's Homepage  Reply with Quote
quote:

I must say that it is apparent that Mr. Volk is not well qualified in relational database design. He makes statements that are clearly at odds with established, professionally accepted guidelines for DB design.




This coming from a guy who did his website in Microsoft Word

Damian
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 05/09/2002 :  19:22:18  Show Profile  Reply with Quote
quote:

The main purpose of a primary key is to provide a unique identifier for relating records in one table with records in another table.



I suppose you also think a relation is your brother!

quote:

I must say that it is apparent that Mr. Volk is not well qualified in relational database design.



Hypocrite!

DavidM

"SQL-3 is an abomination.."
Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 05/10/2002 :  10:02:02  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
quote:

Good design dictates that the PK should never be exposed to the end-user.



Displaying primary key values to the end user is not recommended ONLY when database is using surrogate keys (such as identity). Surrogate keys are meaningless and confusing to the end user, so applications tend to hide them. However, if natural keys are used (like Rob Volk suggests in his article), the natural key can (and should) be displayed to the user, because it contains meaningful information, information that user needs to see and work with. There is nothing improper about showing those values to the user.
In fact, everything that is displayed to the end user by the application front end is simply business data. A user doesn't need to know and shouldn't care if some data elements play additional roles on the back end (physical implementation of the database should be completely transparent to the user application). I find it rather amusing that you slam Rob's database design expertise and then carry on about "exposing" something to the user, which is entirely a front end issue.

So getting back to the real database question: should you or should you not use surrogate keys... This debate is ages long... There are some very well respected database gurus with opposing views on the subject (hey, maybe Joe Celko will weigh in on this next time he stops by the site -- I believe he is a proponent of natural keys). Each method has its advantages. But there certainly is no single generally accepted "good design" as you put it. Rob has made an argument (a pretty good one IMO) for use of natural keys. If you feel that surrogate keys are better, please state your arguments instead of simply throwing an unsubstantiated opinion.

Edited by - izaltsman on 05/10/2002 10:20:03
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 05/11/2002 :  10:23:44  Show Profile  Reply with Quote
How pathetic it sounds when you tell it without showing your own credentials, Mr.Volk has done nothing but helping people on this site. As Ajarn Mark points out he can cut his own book on RDMBS and Sql Server. its pity you chose to pass such remarks with your misplaced information.
quote:

I must say that it is apparent that Mr. Volk is not well qualified in relational database design. He makes statements that are clearly at odds with established, professionally accepted guidelines for DB design.



Now if therez no relation involved wouldnt you have a primary key??.

quote:

The main purpose of a primary key is to provide a unique identifier for relating records in one table with records in another table









--------------------------------------------------------------
Go to Top of Page

sven erik
Starting Member

1 Posts

Posted - 09/09/2002 :  02:25:48  Show Profile  Reply with Quote
Hi there : I think this article is not a very clever one. The Author seem to be not comfortable with real world data. I have seen some databases designed like described in the article ("meaningfull" data as a primary key) and I think these databases are complete bullshit. If you have something like "Rose" as a primary key, you need to have very fast ways to search for "Rose" - also You need to have a way not to depend on the writing of "Rose" (someone may use "tulip" as another PK value - and inconsitency is there). Also : meaningfull for "Who"? Ithe one mostly dealing with this data is the database system - there is no meaning for the word "Rose" from it's point of view. Not to speak of normalization - most db designes done like described in the article need to run on very very expensive machines - because you need to search in a huge amount of strings - which is bad by definition.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 09/10/2002 :  16:46:42  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Congratulations sven_erik, I don't think you could have created a post with which I disagree more. I'll disect it here...
quote:

Hi there : I think this article is not a very clever one. The Author seem to be not comfortable with real world data.

Well, let's see... First, Rob Volk, the author, has quite a lot of experience (and I'm sure comfort-level) with "real world data". I have gotten to know a little about Rob through our time on this board together, and I've seen him do a lot of great work to help others with his real-world experience.
quote:
I have seen some databases designed like described in the article ("meaningfull" data as a primary key) and I think these databases are complete bullshit.

Ah yes, I have seen many databases designed with meaningful keys that were a real pain to deal with, which is why for so long I preferred all meaningless keys. But I have also seen a lot of databases with meaningless keys that were in even worse shape. My personal favorites are the ones where everything is stored in one table, and field names are really what should be in values, but the values are all Yes/No. Now THAT was a piece of art! Oh, and did I mention how much duplicate data there was because there was nothing that prevented duplicates?
quote:
If you have something like "Rose" as a primary key, you need to have very fast ways to search for "Rose"

You mean like an index? Can't see this is any different than with and IDENTITY field.
quote:
- also You need to have a way not to depend on the writing of "Rose" (someone may use "tulip" as another PK value - and inconsitency is there).

Well, yes, there are standards to be enforced, as I'm sure you would have with the data whether it was a key or not. Perhaps the data is entered from a lookup list, or some other technique where it doesn't have to be typed in each time.
quote:
Also : meaningfull for "Who"? Ithe one mostly dealing with this data is the database system - there is no meaning for the word "Rose" from it's point of view.

And numbers are more meaningful to the computer? Of course the meaning is for the end users of that particular data.
quote:
Not to speak of normalization - most db designes done like described in the article need to run on very very expensive machines - because you need to search in a huge amount of strings - which is bad by definition.


This seems to be almost complete hogwash. Let's see... normalization is all (or at least mostly) about data integrity, right? So what about the fact that a meaningful key may eliminate the possibility of duplicate rows, but an IDENTITY cannot. What about the fact that with meaningful values, you could totally eliminate some lookup reference tables and eliminate the need for joins in order to produce meaningful reports for your boss? What are you talking about searching in a huge amount of strings? It's not as if Rob's advocating string parsing here. If you have to put an index on the field to make it search faster, how is that different from an IDENTITY field? Are you under the impression that searching for value 8569 is any faster than searching for the value ROSE? Can you tell me about the explicit tests you have run to confirm this, or are you only talking theory here?

Now a personal story... I just wrapped up working on a project where my first inclination (old habit) was to build a lookup table of items that we were recording. Basically, it's a game quiz and we are recording whether the person got the answer right or wrong for every item in the game. Ultimately we wanted to be able to run a report of all items and how often they were answered correctly. With the old lookup table technique, we would first have to lookup the IDENTITY value of each item to enter it into our junction table. Then during reporting we'd have to lookup the description of the item again in order to report it back. Instead, I ditched the lookup table and just put the item description (typically 1 or 2 word phrase) into the junction table directly. Then, no lookups needed, and my report query has no joining to do. Much faster!

For more insight, check out Page47's and byrmol's responses to this article: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18676

So in conclusion, I'd have to say that Rob did a great job on this article. He isn't totally bashing IDENTITY keys. He in fact acknowledges there are some good uses for them, and lists some of their benefits. I think this is a fairly well-written and reasoned article. It's a shame we'll probably never see part 2.

Edited by - ajarnmark on 09/10/2002 16:53:49
Go to Top of Page

quazibubble
Starting Member

USA
25 Posts

Posted - 12/08/2002 :  17:16:02  Show Profile  Visit quazibubble's Homepage  Reply with Quote
Kudos to the experienced developers speaking up against this naive and dangerously uninformed article. I don't want any juniors on MY payroll reading it, that's for damn sure. I can't tell you many how many f'ed up systems I've paid to fix or scrap due to amateur developers making up their own rules of normalization they think at the time are pretty cool. Developers who say things like "I'd rather not dabble in arcane theory, I'm here to develop a real system"--I'd like to throttle people like that. In the case of normalized design, it's not "inconvenient theory", it's everyday, very real, very simple, and proven rules of design. Follow the simple rules and no one gets hurt. The basic forms of data normalization (and derived axioms) can be thought of as an etiquette for junior developers to follow and not ask why, until they figure out themselves the wisdom of the sages who've been there, done that, got the scars to prove it. Bold and youthful innovation is great, but not "innovating" back to the stone age just because you don't know the rest of the world has already been there and has moved on.

The fool that wrote this article is trying to convince you to break the 1st, 2nd, and 3rd forms of normalization. One entity's PK is another's FK (if not, you shouldn't be using an RDBMS). An FK that is actually a descriptive attribute of some other entity creates repeating groups, redundant data, and directly describes something other than the primary entity.

A solid, mature OLTP database architecture has entities with a single identity or GUID PK defined, usually as a clustered index, that by definition is totally meaningless except for building relational structures behind the scenes. That is it's ONLY purpose. Think of it as an undercover system column that has nothing to do with the entity it belongs to, you just happen to be able to see it as a developer. You'll use design systems in the future that don't even give YOU, the developer, access to this column or it's values, but it will be there (they've already existed as CASE tools and their ancestors, as well as their simpler, more useful prodigy). Your job should be designing ENTITIES AND RELATIONSHIP, not PKs and FKs. If you find yourself pondering what to use for a PK (beyond the question of "should it be an identity or GUID?"), you are doing something terribly wrong and will be costing a whole team a great deal of money and headache, even long after you a fired.

Furthermore, if a PK is not meaningless--e.g. it is any attribute remotely helping to add human meaning to the entity (SSN, network login, etc.), there WILL be cascading updates throughout the entire system, many people are going to be very pissed, and you will look like a jacka**. Users make typos, people change their names. Any data that was entered by or derived from a human at any point is subject to change. To be perfectly compliant with the forms of normalization, the value in a PK should not even be used as an account number. But giving a system-defined value that later becomes meaningful to a human is not nearly as harmful as using an existing value of meaning for a PK.

(continued...)
Go to Top of Page

quazibubble
Starting Member

USA
25 Posts

Posted - 12/08/2002 :  17:19:53  Show Profile  Visit quazibubble's Homepage  Reply with Quote
(continued from previous...)

This absurd notion that a PK solely defines a unique entity, which is being perpetuated in this thread, has got to stop. If you remember one thing, remember this: a PK is used to build relationships. Period. Nothing more, nothing less. Don't forget it. To meet that end, it must be unique, but DOES NOT IN ANY WAY define what makes a logical entity unique. If anybody tries to tell you that, they are a moron. If you just define a PK and move on, your system is BROKEN, human-readable PK or not. YOU, the developer, must take additional steps to define and enforce what combination of attributes define a unique entity, based on what your business rules and/or human logic require. THAT is what you are paid by the hour for, not for wasting my money pondering what column or columns you should use for a f'ing PK.

Lo and behold, RDBMSes allow you to define a unique constraint in addition to the PK! Now why would that be? MS-Access, while an awesome ad-hoc reporting tool, is not suitable for enterprise dev for many reasons--particularly it's lack of supporting arbitrary multi-column unique constraints. (Fortunately you can achieve a similar effect with a multi-field PK, while still using the autonumber or GUID field with it's own unique constraint as the row ID.)

The argument for meaningful multi-column PKs somehow magically needing fewer joins is the pinnacle of naivety and inexperience. Systems actually useful to humans are generally designed to return a richer set of data than a couple of obscure attributes, when communicating related entities. For example: to generate a report of subordinates along with their region and boss, are we to believe it would be useful to users to see something like this:

"Name", "Division", "Boss"
"John Q. Smith", "SWRGN", "456-78-9333"

I would fire the a**hole that designed that, on the spot. A system actually useable by humans will show the boss' full name at the very least, and more likely other related info as well. Guess what--even with a "meaningful" PK, this means you'll have to do many joins to other entities. Fortunately database engines were sort of designed for doing joins, and are actually kind of good at it. (Refresher course on OLTP vs. OLAP: a data warehouse typically contains denormalized tables, where one can do various ad-hoc aggregate queries without joins--and even then, you do NOT see the values contained in PKs, nor would it be useful.)

And to the person who absurdly thinks he would need to hard-code a number in order to use correct relational design--put your thinking cap on bud, you are overlooking painfully obvious design choices that would do that without requiring your ignorant approach.

In short, this article is ignorant, uninformed, misleading drivel and should be removed.

Sincerely,
BT

Go to Top of Page

Doug G
Constraint Violating Yak Guru

USA
329 Posts

Posted - 12/08/2002 :  20:02:04  Show Profile  Reply with Quote
quote:
In short, this article is ignorant, uninformed, misleading drivel and should be removed.

You have such a way with words :)

I do not give much weight to comments that include ad hominem attacks. If you can't make your point without insulting others you should consider a new way to use words.

$00000.02


======
Doug G
======
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 12/09/2002 :  08:32:30  Show Profile  Reply with Quote
quote:
The fool that wrote this article is trying to convince you to break the 1st, 2nd, and 3rd forms of normalization.


I'd love to hear further commentary on how choosing a Natural Key over a Surrogate Key de-normalizes ones schema. Is this in any of Codd's papers?

quote:
This absurd notion that a PK solely defines a unique entity, which is being perpetuated in this thread, has got to stop. If you remember one thing, remember this: a PK is used to build relationships. Period. Nothing more, nothing less.


I've read several (professional) authors suggest that an Identity or GUID typed surrogate key violates Codd's Information Priciple since they are not represented by regular relational attributes and cannot be accessed as such.

Jay White
{0}
Go to Top of Page
Page: of 8 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000