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).
