August 9, 2012 at 11:25 pm
sqlfriends (8/9/2012)
Thanks, so this is to create a unique index, but not a unique constraint.It seems not GUI way to create unique constraint.
You may choose "Unique Key" instead of "Index".
That will create a constraint.
_____________
Code for TallyGenerator
August 10, 2012 at 9:11 am
Nice post, Mr. Celko!
Lowell
August 10, 2012 at 9:22 am
Some people will tell you that you should always use natural keys (JC the master of them :-)) and others will tell you that you should always use surrogate keys.
All these people constantly prove to be wrong!
They're doing little more than sharing the prejudices of their "data religion" with you (again JC is a prophet of natural ones).
But the reality is that natural and surrogate keys each have their advantages and disadvantages, and there is no solution which is perfect for all situations. In better words:
You need to know what you're doing if you want to get it right!
August 10, 2012 at 9:36 am
Eugene Elutin (8/10/2012)
Some people will tell you that you should always use natural keys (JC the master of them :-)) and others will tell you that you should always use surrogate keys.All these people constantly prove to be wrong!
They're doing little more than sharing the prejudices of their "data religion" with you (again JC is a prophet of natural ones).
But the reality is that natural and surrogate keys each have their advantages and disadvantages, and there is no solution which is perfect for all situations. In better words:
You need to know what you're doing if you want to get it right!
I like the idea of having a sid (a surrogate id) on my tables, even if I do have a natural key that can serve as a primary key. the problem I have run into with natural keys is when the business rules change and those changes cause the natural key to no longer be unique. The sid gives you an alternate key that uniquely identifies each row in the table.
August 10, 2012 at 9:52 am
...
I like the idea of having a sid (a surrogate id) on my tables, even if I do have a natural key that can serve as a primary key. the problem I have run into with natural keys is when the business rules change and those changes cause the natural key to no longer be unique. The sid gives you an alternate key that uniquely identifies each row in the table.
Agree!
However there are cases when business rule are rules of life and very unlikely to change.
For example:
codes for gender: F and M, (and maximum X which is new in Australia :-))
There are a lot of cases where Natural Keys are just fine and bring significant benefits in many aspects including performance...
As I said, it's all depends. And I guess most of profs will not argue with that one.... Hopefully :hehe:
August 10, 2012 at 10:31 am
Eugene Elutin (8/10/2012)
...
I like the idea of having a sid (a surrogate id) on my tables, even if I do have a natural key that can serve as a primary key. the problem I have run into with natural keys is when the business rules change and those changes cause the natural key to no longer be unique. The sid gives you an alternate key that uniquely identifies each row in the table.
Agree!
However there are cases when business rule are rules of life and very unlikely to change.
For example:
codes for gender: F and M, (and maximum X which is new in Australia :-))
There are a lot of cases where Natural Keys are just fine and bring significant benefits in many aspects including performance...
As I said, it's all depends. And I guess most of profs will not argue with that one.... Hopefully :hehe:
Can't see codes for gender being used as a primary key for anything except maybe a GenderCode table. Also, since when have any business rules been so solidly established that they would never change? 😀
I guess I like hedging my bets. 😉
Sort of like what I do before deleting the BUILTIN\Administrators group in SQL Server. I make sure I have three different ways to log in to SQL Server with admin privledges, not including the built-in backdoor.
August 10, 2012 at 11:07 am
Eugene Elutin (8/10/2012)
But the reality is that natural and surrogate keys each have their advantages and disadvantages, and there is no solution which is perfect for all situations. In better words:You need to know what you're doing if you want to get it right!
exactly.
The probability of survival is inversely proportional to the angle of arrival.
August 10, 2012 at 2:18 pm
Edit: I overstepped myself and I apologize. I am removing my inapprpriate comment and will refarin from such in the future.
August 10, 2012 at 3:39 pm
...
Removed...
:
Don't forget to buy and read all his books, otherwise you will be left in unknowing state for ever!
:hehe:
August 11, 2012 at 3:37 pm
CELKO (8/11/2012)
...
Yes, Mr. Celko, you are the all knowing, always right individual and we should all bow to your wisdom and knowledge and kiss your ring.
Don't forget to buy and read all his books, otherwise you will be left in unknowing state for ever!
:hehe:
Hey, you have to play by the semi-official “Beat up on Celko” rules. Before you lapse into personal attacks, you have to add something to the thread. This is how you score points on the Dijkstra Scale.
Finding an error in one of my books = 20 points and credit in the next edition
Finding an error in the posting
misquoting Codd or Date = 10 points
misquoting ANSI or ISO Standard = 10 points
Providing an improvement in dialect SQL = 5 points
Providing an improvement in ANSI/ISO Standard SQL = 10 points
Providing a good “war story” that undermines or disproves by premise = 10 points
It is possible to score multiple points at once. Recently, a posting in a thread removed a redundant REPLACE() statement added a dialect collation clause to an expression to sum the digits in an integer for a total of 20 points.
If you do not know who Dijkstra was and what he was like, take off 35 points. 😉
I apologized, I would appreciate it if you would just leave it at that. Thank you.
August 11, 2012 at 6:36 pm
CELKO (8/11/2012)
I apologized, I would appreciate it if you would just leave it at that.
But I do not want you to quit sniping at me. I like adversarial content and mock feuds. Chris Date and I had separate columns in the old DBMS and DATABASE PROGRAMMING & DESIGN magazines. He would do a column on why NULLs are the work Satan, then I wold present the ANSI X3H2 reasoning for them. Both magazines were owned by the same company and we boosted each others sales. It was great fun! People still ask me why I hate Chris Date 😀
Nope, it was unprofessional of me and I was wrong. I know I can behave in a more mature and professional manner than I did and that is my intention in the future.
August 12, 2012 at 9:40 am
CELKO (8/11/2012)
I apologized, I would appreciate it if you would just leave it at that.
But I do not want you to quit sniping at me. I like adversarial content and mock feuds. Chris Date and I had separate columns in the old DBMS and DATABASE PROGRAMMING & DESIGN magazines. He would do a column on why NULLs are the work Satan, then I wold present the ANSI X3H2 reasoning for them. Both magazines were owned by the same company and we boosted each others sales. It was great fun! People still ask me why I hate Chris Date 😀
I for one appreciate your posts, they are always on target and contain facts that many people forget about or don't really understand 100%. While its not appropriate to be harsh or condescending to some newbies who post here, the rest of us professionals should be able to deal with it. I have many years in this business and know how to get things right the first time but I'm still learning stuff every day, whether from these forums or dozens of other websites I've visited or books I've read.
The probability of survival is inversely proportional to the angle of arrival.
August 12, 2012 at 8:56 pm
CELKO (8/10/2012)
like the idea of having a sid (a surrogate id) on my tables, even if I do have a natural key that can serve as a primary key.the problem I have run into with natural keys is when the business rules change and those changes cause the natural key to no longer be unique.
With 20+ years of repairing bad SQL, I have not found that to be true.
Oh, that's easy.
Person registar, for example in health care.
I know, just like books, all people should have a unique registration number issued by the National Registar.
They should.
But some of them don't.
For example, newborns. It yakes some time to issue a number for a newborn. Several weeks usually.
But medical facts must be recorded right now, and sometimes immediate actions must be taken based on those actions.
And a sergeant in a hospital possibly in another city must receive those medical facts and updates to it (after additional analysis are completed) right now, when they are revealed, for immediate action. Not weeks later when the unique number is issued and the baby is dead.
Same applies for new immigrants. Even if we forget about illegal ones (they still show up in hospitals time to time), legal immigrants have to wait for a number for some time.
And what are we gonna do with tourists?
Those bloody tourists don't even think about applying for a unique registration number!
Shoud we just shoot them all the the border not to break the perfect data model? 🙂
So, we have to use names.
Unique Code remains NULL, so it's ruled out of the PK.
Now, names. First name may be not given for several days or even weeks.
Last name may also go "unknown" - if the baby was left on a porch, or the mother refuses to keep it and does not know (does not want to know) the father.
So, names by themselves are not good candidates for the PK.
But even if it's all right with the names - do you know how many Abrahams Lincolns are born every year in the USA?
So, we need another participant in our key - date of birth.
Will it guarantee uniqueness? Still not.
Even in such a comparatively small country as Australia (whole country is slightly bigger by population than LA with its satellite towns), in a single city, a single health care organisation database serving only several suburbs of the city held 4 records of clients having the same sets of names and dates of birth. All of them were actually different people, living at different addresses and having different medical records.
And if we add tourists to the equation, especially Korean ones, 20% of which have a last name “Kim” :-), then we can state that names + DOB don't guarantee uniqueness of the key.
So, we need to add something else to our key to make it unique. What would it be?
Residential address? Place of birth? Eye color? Height? AKA names?
These are the items checked by the register staff when they try to resolve possible duplicates.
Now, even if we manage to make the key unique and populate all the fields.
How will a record about a visit to a doctor look like?
It must identify a patient, so it must refer to the PK on the Person table. All fields of it.
Some of them will contain NULLs for most of patients and required to be included only because they help resolving identification problem for 2 Person records.
And when we are searching for a history of events relevant to a patient we must run joins on tens of columns. Taking special care of matching NULLs correctly.
OK, assume we don’t care about the overhead.
We have powerful systems with unlimited disk space, unimaginable amount of memory and can accommodate whatever amount of redundant data.
But there is another problem – PEOPLE CHANGE NAMES!:w00t:
I don’t know why it is legal, but it is, and I don’t have any contacts in Congress to influence the situation.
So, eventually, we need to change a name on a person’s record.
Can we do it?
Not so easy. It’s a part of our PK. And records in other tables reference this key. So, database just won’t allow us to do it because of referential integrity.
We need to drop all FK’s first, update the name in all the tables and then restore the FK’s.
For that we need to have somewhere a list of all the tables which reference Person PK and need to be updated, including those which will be added in future without creating a FK (bloody developers!!!) to table Person. Using the list we’ll build set of dynamic SQL’s for each of the tables and execute them one by one.
For the duration of the process our database will be in transitional state, having inconsistent data, and any attempt from another user to add or edit a record for the patient will with high probability end up in losing some of the patient’s records.
All these troubles can be easily avoided by creating a surrogate PK on the Person table and let all other tables to reference Person by this unique auto-incremental number.
We can then program the front-end application to identify a person uniquely by a set of data available to it at the moment. And if there is a situation when the available data are not sufficient for auto identification then Application can ask user for a resolution. User can revise the person’s profile and make a decision if it’s an existing patient or a new one based on some facts probably not recorded in the database or recorded in a form of a free text or an image in some BLOB field.
And changing names becomes a simplest task of updating a field in a single table with saving replaced names in “AKA names” records.
_____________
Code for TallyGenerator
August 13, 2012 at 2:48 am
Eugene Elutin (8/10/2012)
codes for gender: F and M, (and maximum X which is new in Australia :-))
Turns out New Zealand is way more sophisticated than Australia.
About by 1/3 more sophisticated. 🙂
NATIONAL HEALTH INDEX
DATA DICTIONARY
Version 5.3
.....
Sex
Administrative status
Reference ID:A0028Version:1.0Version date:14-Oct-2003
Identifying and defining attributes
Name:Sex
Name in database:gender_code
Other names:Sex type code
Element type:Data element
Definition:The person's biological sex.
Context:Required for demographic analyses.
Relational and representational attributesMandatory
Data type:charField size:1Layout:A
Data domain:
M Male
F Female
U Unknown
I Indeterminate
Guide for use:Stored as Gender code.
Verification rules:Must be a valid code in the Gender code table.
Collection method:'I' codes are for use in cases, usually newborns, where it is not possible to determine the sex of the
healthcare user.
The term sex refers to the biological differences between males and females, while the term
gender refers to a person's social role (masculine or feminine).
Information collected for transsexuals and transgender people should be treated in the same
manner, ie, their biological sex reported.
_____________
Code for TallyGenerator
August 13, 2012 at 7:22 am
Sergiy (8/12/2012)
CELKO (8/10/2012)
like the idea of having a sid (a surrogate id) on my tables, even if I do have a natural key that can serve as a primary key.the problem I have run into with natural keys is when the business rules change and those changes cause the natural key to no longer be unique.
With 20+ years of repairing bad SQL, I have not found that to be true.
Person registar, for example in health care.
...
That what really surprises me, 20+ years of experience and still believing in existence of natural keys for everything...
Look, even GUID's are not guaranteed to be unique, and the unique codes from different organisations all the more! Tax file numbers in Australia - not unique, there were cases of duplication. CIS numbers in different countries - not unique, Vehicle numbers (eg. engine and frame) are not unique! What else?
I would really appreciate if someone would suggest natural unique keys for addresses and people. And explain why they would be better than surrogate ones...
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply