INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

New PK in existing table

New PK in existing table

(OP)
Imagine you have a table in the data base for a few years with lots of records and finally you want to introduce a Primary Key field to that table (I know, it should be done from the start).

Is there an easy way to Update this PK field with some simple, easy Update statement? Something with the ROWNUM maybe...?
You know, each record should get its own, unique number, order does not matter, as long as 1000 records get the numbers from 1 to 1000.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: New PK in existing table

(OP)
I just tried:

UPDATE MyTable
SET SomePKField = ROWNUM

and it seams to work.
I just don't know how reliable this syntax is. And is there a better way to do it?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: New PK in existing table

Do it right, make a sequence that you can use for any later row inserts and if you don't want to change your code to set the value do the following. Please replace the name MY_TABLE and MY_KEY with the real table name and the name of the column that you want to add.

CREATE SEQUENCE MY_TABLE_S1
START WITH 0
INCREMENT BY 1
MINVALUE 0
CACHE 20
NOCYCLE
NOORDER;

THEN ADD YOUR NEW PRIMARY KEY COLUMN or use and existing column if you have to.

ALTER TABLE MY_TABLE ADD (MY_KEY NUMBER(38));

Now add a trigger to always maintain the column on new inserts

CREATE TRIGGER MY_TABLE_T1
BEFORE INSERT
ON MY_TABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
N NUMBER;
BEGIN
-- For Toad: Highlight column REFUND_BALANCE
SELECT MY_TABLE_S1.NEXTVAL INTO n FROM dual;
:NEW.MY_KEY := N;
END MY_TABLE_T1;
/


Then set the new primary key column

UPDATE MY_TABLE
SET MY_KEY = MY_TABLE_S1.NEXTVAL;

COMMIT;

Now enforce and index the primary key

ALTER TABLE MY_TABLE ADD
CONSTRAINT MY_TABLE_PK
PRIMARY KEY (MY_KEY)
ENABLE
VALIDATE;

********* Your all set ***********








Bill
Lead Application Developer
New York State, USA

RE: New PK in existing table

(OP)
Thank you Beilstwh,

I was working under the assumption that I would:
1. Add a field (that eventually will become a PK)
2. Populate it with unique values (that's why the question)
3. Set it as PK
4. introduce a sequence
5. Set the next value of a sequence to the last PK + 1
6. And set up a trigger on Insert

Now I see a better / faster way to do it:
1. Add a field (that eventually will become a PK)
2. introduce a sequence
3. Run this: UPDATE MY_TABLE SET MY_KEY = MY_TABLE_S1.NEXTVAL
4. Set it as PK
6. And set up a trigger on Insert

As a side question - is it a good idea to have a sequence / trigger on a small, look-up table that does not get (any or very seldom) Inserts? Is it 'over-kill' to do that?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: New PK in existing table

The only reason that I added the trigger was because I assumed that you didn't want to modify the code. If you are always going to go through the same code you can change the insert to

insert into my_table(my_key,col1,col2) values(my_table_s1.nextval,1,2);

if you do that you will not need the trigger. Either way is valid.

Bill
Lead Application Developer
New York State, USA

RE: New PK in existing table

Sticking with your original approach, this should work...

CODE

UPDATE  MyTable
SET     SomePKField = ROWID
WHERE   ROWNUM <= 1000; 

ROWIDs are unique to each row.

But Beilstwh's approach is more correct.


RE: New PK in existing table

Remember that the actual rowid's for a table may change. I would not use a rowid, I would use a sequence. As a general rule never store and then expect to use a rowid.

Bill
Lead Application Developer
New York State, USA

RE: New PK in existing table

(OP)
I do want to have the tables set up right – PK, sequence, trigger on Insert, done. But in many cases I need to ‘catch-up’ first with the tables that do not have PKs (bad idea, I know, not my idea). That’s why I want to fix it wherever I can.

Thanks for the input.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close