Re: Changing column format

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

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