Re: Inserting a new PK into an existing table


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

.

Relevant Pages

  • Re: can my query be optmised
    ... Here is one way to get rid of extrema in a numeric column. ... this is faster than you would think because the stats include ... CREATE TABLE Foobar(i INTEGER NOT NULL PRIMARY KEY); ... FROM Foobar AS F1, Foobar AS F2 ...
    (microsoft.public.sqlserver.programming)
  • Inserting a new PK into an existing table
    ... I want to add a new numeric column "id" into an existing table that is ... intended to be the new primary key. ... As I am quite new to SQL, I have also read a number of tutorials, ... Can it be done with "plain" SQL or do I need extensions like PL/SQL? ...
    (comp.databases.oracle.misc)
  • Re: Inserting a new PK into an existing table
    ... I want to add a new numeric column "id" into an existing table that is ... intended to be the new primary key. ... As I am quite new to SQL, I have also read a number of tutorials, ... alter table yo add constraint pk_yo ...
    (comp.databases.oracle.misc)
  • Re: SQL Express - Identity specification property - how to change
    ... FOO_ID int identity, ... Tony Rogerson, SQL Server MVP ... They are making unfortunately frequent use of the IDENTITY column in conjunction with Primary Key. ... IDENTITY has none of the properties of a data type because it is not a data type at all. ...
    (comp.databases.ms-sqlserver)
  • Re: Binding and adding rows with SQL identity column
    ... Each time you call AddNew then the DataTable generates a new ... Each time an update is done then the real primary key ... > generated by the built-in wizard in VS2003. ... > identity column on the sql table, perhaps this would all the fine and I ...
    (microsoft.public.dotnet.framework.windowsforms)