Hello world,

I want to add a new numeric column "id" into an existing table that is
intended to be the new primary key. As the table already contains data,
I need to fill the "id" column with distinct values before I can make
it the new primary key.

I have looked for SQL commands to add the new column and fill it with a
series of values like 1, 2, 3, ..., but have not found something like
that. As I am quite new to SQL, I have also read a number of tutorials,
but found no hint on the topic.

Now I am curious how this is "normally" done.
Can it be done with "plain" SQL or do I need extensions like PL/SQL?

Jens

"Jens Lenge" <spampot@gmx.net> writes:

> Hello world,
>
> I want to add a new numeric column "id" into an existing table that is
> intended to be the new primary key. As the table already contains data,
> I need to fill the "id" column with distinct values before I can make
> it the new primary key.
>
> I have looked for SQL commands to add the new column and fill it with a
> series of values like 1, 2, 3, ..., but have not found something like
> that. As I am quite new to SQL, I have also read a number of tutorials,
> but found no hint on the topic.
>
> Now I am curious how this is "normally" done.
> Can it be done with "plain" SQL or do I need extensions like PL/SQL?
>
First add the column

alter table yo add (
id number
);

Then use update statements to populate the column. This is left as
an exercise to the reader.

Then make it a primary key

alter table yo add constraint pk_yo
primary key (id)
using index tablespace IDX;

--
Dave Newman

Jens Lenge wrote:
> Hello world,
>
> I want to add a new numeric column "id" into an existing table that is
> intended to be the new primary key. As the table already contains data,
> I need to fill the "id" column with distinct values before I can make
> it the new primary key.
>
> I have looked for SQL commands to add the new column and fill it with a
> series of values like 1, 2, 3, ..., but have not found something like
> that. As I am quite new to SQL, I have also read a number of tutorials,
> but found no hint on the topic.
>
> Now I am curious how this is "normally" done.
> Can it be done with "plain" SQL or do I need extensions like PL/SQL?
>
> Jens

Normally we use a business column or set of column values in the table
to be the PK and do not use an artificial key since if a unique
business value exists there is no need for or real use of an artificial
key.

You can populate a numeric column with unique values by performing an
update statement that references the rownum for each row in the table.

UT1 > select * from marktest;

FLD1 FLD2 FLD3
---------- ---------- ---------
one 1 08-MAY-06
two 2 08-MAY-06
three 3 08-MAY-06
two 22 26-MAY-06
three 33 26-MAY-06
five 5
six 16-JUN-06
99 01-JAN-50

8 rows selected.

UT1 > update marktest set fld2 = rownum;

8 rows updated.

UT1 > select * from marktest;

FLD1 FLD2 FLD3
---------- ---------- ---------
one 1 08-MAY-06
two 2 08-MAY-06
three 3 08-MAY-06
two 4 26-MAY-06
three 5 26-MAY-06
five 6
six 7 16-JUN-06
8 01-JAN-50

8 rows selected.

HTH -- Mark D Powell --

David Newman wrote:

> [...]
> Then use update statements to populate the column. This is left as
> an exercise to the reader.
> [...]

I probably should have been more precise. I already know how to create
the new column and how to make it a primary key after it has been
filled, the only thing I have not found yet is how to populate it with
unique numbers.

Anyway, thanks for the reply.

Jens

Mark D Powell wrote:

> Normally we use a business column or set of column values in the table
> to be the PK and do not use an artificial key since if a unique
> business value exists there is no need for or real use of an artificial
> key.

In general, I would agree. In this specific case however, I think it
could be desirable to have an additional primary key column because:

a) The "old" primary key is composed of multiple columns (whose
specific combinations remain unique, which will still be enforced by a
unique constraint).

b) Unlike before, the table is now going to be referenced by several
other tables via foreign keys. Without the new artificial primary key,
this would require multiple new columns in all of these tables (along
with a foreign key that is composed of these columns).

I thought the new primary key would be a good idea to reduce redundancy
and simplify the structure, because now I only need to add one new
column to each of the other tables. Or is it better to use a different
approach?

> You can populate a numeric column with unique values by performing an
> update statement that references the rownum for each row in the table.
> [...]

Perfect solution. Thank you!

Jens

Jens Lenge wrote:
> Hello world,
>
> I want to add a new numeric column "id" into an existing table

Please don't.

SELECT keyword
FROM gv$reserved_words
WHERE keyword LIKE 'ID%;
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Jens Lenge wrote:
> Mark D Powell wrote:
>
> > Normally we use a business column or set of column values in the table
> > to be the PK and do not use an artificial key since if a unique
> > business value exists there is no need for or real use of an artificial
> > key.
>
> In general, I would agree. In this specific case however, I think it
> could be desirable to have an additional primary key column because:
>
> a) The "old" primary key is composed of multiple columns (whose
> specific combinations remain unique, which will still be enforced by a
> unique constraint).

So the pseudoKey gains you nothing here (in fact it causes more
overhead for this table).

>
> b) Unlike before, the table is now going to be referenced by several
> other tables via foreign keys. Without the new artificial primary key,
> this would require multiple new columns in all of these tables (along
> with a foreign key that is composed of these columns).

So? That is what relational data models do.
>
> I thought the new primary key would be a good idea to reduce redundancy
> and simplify the structure, because now I only need to add one new
> column to each of the other tables. Or is it better to use a different
> approach?

The Only benefit fo using the pseudoKey is the space savings, not
reduced redundancy.
All you are really doing is replacing a compound PK with a pointer. As
long as you are aware of the risks and benefits involved it is okay.
Where possible I prefer to maintain the compound Key in both parent and
child tables. But the pseudoKey sometimes wins out if for example the
number of columns in the compound key is many or the total space is
large. In these days of really cheap disc space, most often it's the
number of columns that tips the scale in favor of the pseudoKey.

>
> > You can populate a numeric column with unique values by performing an
> > update statement that references the rownum for each row in the table.
> > [...]
>
> Perfect solution. Thank you!
>
> Jens

I also hate rownum, but this is a good example of some usefulness from
it.
HTH,
Ed

"Mark D Powell" <Mark.Powell@eds.com> writes:

> Jens Lenge wrote:
>
> Normally we use a business column or set of column values in the table
> to be the PK and do not use an artificial key since if a unique
> business value exists there is no need for or real use of an artificial
> key.
>

This is an interesting subject to me. Normally I would fully agree with
you. But I have been learning the Hibernate persistence library lately
and they refer to this type of model, one in which actual business
columns are used in the primary key, as "legacy" and not designed well
and that an artificial primary key is always preferred. I've always
felt that if you are going to have something in the table, like an
employee id or SSN, that is going to have a unique constraint on it anyway
you might as well make it the primary key. Is this some new way of
thinking?

--
Dave Newman

David Newman wrote:
> "Mark D Powell" <Mark.Powell@eds.com> writes:
>
>> Jens Lenge wrote:
>>
>> Normally we use a business column or set of column values in the table
>> to be the PK and do not use an artificial key since if a unique
>> business value exists there is no need for or real use of an artificial
>> key.
>>
>
> This is an interesting subject to me. Normally I would fully agree with
> you. But I have been learning the Hibernate persistence library lately
> and they refer to this type of model, one in which actual business
> columns are used in the primary key, as "legacy" and not designed well
> and that an artificial primary key is always preferred. I've always
> felt that if you are going to have something in the table, like an
> employee id or SSN, that is going to have a unique constraint on it anyway
> you might as well make it the primary key. Is this some new way of
> thinking?
>
> --
> Dave Newman

Java is sustained by people whose knowledge of relational databases is
at about the level at which philosophy is taught to 4th graders.

Essentially you have a choice between natural keys and surrogate keys.
If you use a natural key, for example a tax identification number, it
must conform to the rules for a primary key in the database in which you
are working. If there is no natural key, and I would argue ONLY if there
is no natural key, would I resort to using a surrogate key which is
usually an incremented integer (in Oracle generated by a sequence
object). One of the biggest problems with surrogate keys is that they
are incapable, without natural keys, of eliminating duplicates in column
other than their own.

I would give the opinions of those coming from Hibernate all the time it
deserves: None!
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

David Newman wrote:
> "Mark D Powell" <Mark.Powell@eds.com> writes:
>
> > Jens Lenge wrote:
> >
> > Normally we use a business column or set of column values in the table
> > to be the PK and do not use an artificial key since if a unique
> > business value exists there is no need for or real use of an artificial
> > key.
> >
>
> This is an interesting subject to me. Normally I would fully agree with
> you. But I have been learning the Hibernate persistence library lately
> and they refer to this type of model, one in which actual business
> columns are used in the primary key, as "legacy" and not designed well
> and that an artificial primary key is always preferred. I've always
> felt that if you are going to have something in the table, like an
> employee id or SSN, that is going to have a unique constraint on it anyway
> you might as well make it the primary key. Is this some new way of
> thinking?

Agree with natual/surrogate post by Daniel - except, SSN is not
guaranteed unique! (The problem really being insufficient analysis of
natural keys).

jg
--
@home.com is bogus.
http://dizwell.com/migforum/index.php?topic=189.0