I'm not sure I understand your problem, exactly. Are you saying someone adding a user accidentically adds a new record where one already exists, and thus you have some records in subsidiary tables pointing to the first user record, and others the second?
Adam -----Original Message----- Sent: Thursday, November 07, 2002 8: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: Donahue, Adam 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).
