I should add that what we do is to keep the "old key" record and attach the "new key" children to it. The "new key" child information is more current. Things are even murkier here because we have: Peoplesoft whose design cannot handle the data we need to keep on our collaborators, nor all the data we need on employees; and a homegrown system which is not designed to pay employees nor keep track of their benefits. Collaborators are paid by their home institutions not us.
The homegrown system is built and maintained via Oracle Designer and uses the Web Server Generator to build the data entry and query screens Developing new systems is magnitudes faster than using Peoplesoft. The systems do have to share information and there has to be a database of record for each piece of information. What's ugly is that the database of record depends not only on the type of information, but also the "type" of person to which it belongs. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -----Original Message----- Sent: Thursday, November 07, 2002 5:04 PM To: Multiple recipients of list ORACLE-L we have systems where for various reasons (usually because someone can't type) a row is entered with differing names, but which refer to the same person. We use unique, non-semantic ids, with no meaning associated to the key. We STILL have the problem (and I'm facing having to design a way to do this in a new system) of merging the information in these two records and cascading the referential integrity OUCH --- Jared Still <[EMAIL PROTECTED]> wrote: > > Thank you Adam! I had given up hope that someone > else would point this out. > > Jared > > On Thursday 07 November 2002 14:24, Donahue, Adam wrote: > > I believe "username" here would be a unique identifier. In most > systems, > > username must be unique (at least within a particular domain). If > yours is > > a single domain system, David, then having two Jim Joneses would > not be the > > problem. > > > > There is another, more database-specific reason not to use the > username > > field as the primary key: username (I assume) has semantic meaning, > andm > > further, I assume, could change. For example, let's assume my > username is > > "adonahue". Later I get a promotion and I want a vanity username > of > > "adam". Let's also assume your database consists of several > tables, many > > of which reference the user table by username. > > > > In this case, updating the username will require updating ALL rows > in all > > tables to reflect the new name. (That is, the data structure > becomes > > denormalized if username is the primary key.) If you use userid, > you can > > simply update the user table referenced by the corresponding > userid, and no > > further changes would be required in child tables. > > > > Jerry's suggestion is best: userid as the primary (surrogate) key, > and a > > non-null unique constraint on username to prevent duplicate names > within > > the same system. > > > > Adam > > > > -----Original Message----- > > Sent: Thursday, November 07, 2002 4:24 PM > > To: Multiple recipients of list ORACLE-L > > > > > > > > David, > > > > I suggest that you don't. There are many "Jim Jones" in the world. > How are > > you going to handle that? Is this field really your primary key and > related > > to other tables or do you just need to make sure there are no > duplicate > > names? If so, create a unique constraint instead. > > > > If you must, first make sure that there is not already a duplicate > name. > > > > SELECT username, count(username) > > FROM your_table_name > > GROUP BY username > > HAVING count(username) >1; > > > > If you have any records returned, you need to fix your data before > creating > > the primary key. Same thing with null values. If the SQL below > returns a > > number other than zero, you need to put something in the null > values before > > creating the primary key. > > > > SELECT count(username) > > FROM your_table_name > > where username = Null; > > > > To drop the primary key: > > > > ALTER TABLE your_table_name > > DROP PRIMARY KEY CASCADE; > > > > To create a primary key: > > > > ALTER TABLE your_table_name > > ADD PRIMARY KEY (username); > > > > Personally, I think you are going to regret doing this. > > > > Jerry Whittle > > ACIFICS DBA > > NCI Information Systems Inc. > > [EMAIL PROTECTED] > > 618-622-4145 > > > > -----Original Message----- > > > > I create a table to store user account information and set > "userid" column > > to be primary key. I now want to set "username" to be primary key > instead > > of "userid", how do I change it? There are couple hundreds of > records in > > table. Please advise. > > > > Thanks, > > David > > ---------------------------------------- > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > Content-Transfer-Encoding: quoted-printable > Content-Description: > ---------------------------------------- > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L (or the > name of mailing list you want to be removed from). You may also send > the HELP command for other information (like subscribing). __________________________________________________ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
