Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Changing column format

Re: Changing column format

From: Jared Still <>
Date: Thu, 07 Nov 2002 16:28:29 -0800
Message-ID: <>

Thank you Adam! I had given up hope that someone else would point this out.


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
> 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
> NCI Information Systems Inc.
> 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
Please see the official ORACLE-L FAQ:
Author: Jared Still

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 - 18:28:29 CST

Original text of this message