Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Changing column format

From: Tim Gorman <>
Date: Thu, 07 Nov 2002 14:18:55 -0800
Message-ID: <>

RE: Changing column formatExcellent advice!

Just a quick note...
  SELECT count(username)
  FROM your_table_name
  where username = Null;
is likely to return a COUNT of zero inaccurately. You might want to change it to:   SELECT count(*)
  FROM your_table_name
  where username is Null
Equating to NULL always returns FALSE, so the original query would return 0 rows, hence the need for the IS NULL operator.

Also, COUNT(expression) will not count the number of occurances where expression is NULL, so even if you corrected the WHERE clause you would still get 0 rows, since all returned USERNAME values would of course be NULL due to the WHERE clause...

...sometimes you're damned, sometimes you're doubly damed. C'est la vie...


  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
  NCI Information Systems Inc.

    -----Original Message-----
    From: Nguyen, David M []

    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.



Please see the official ORACLE-L FAQ:

Author: Tim Gorman

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 - 16:18:55 CST

Original text of this message