Oracle-L: RE: Changing column format

From: Donahue, Adam <adam.donahue_at_hcmny.com>
Date: Thu, 07 Nov 2002 14:24:44 -0800
Message-ID: <F001.004FE6E1.20021107142444@fatcity.com>

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.
jerome.whittle_at_scott.af.mil
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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Donahue, Adam
INET: adam.donahue_at_hcmny.com

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: ListGuru_at_fatcity.com (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). Received on Thu Nov 07 2002 - 16:24:44 CST