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 <jkstill_at_cybcon.com> 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.
> > 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).
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: wisernet100_at_yahoo.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 - 19:03:39 CST
