| adding a primary key column [message #372631] |
Thu, 01 March 2001 06:00  |
mb
Messages: 51
Registered: March 2001
|
Member |
|
|
Hello
is it possible to add a column to an already populated table that is to be the primary key. This key should take its value from a sequence.
I think it can be done by putting an index on one of the cols and then updating the table with a for loop.
Is there a better way?
Thanks
Mark
|
|
|
|
| Re: adding a primary key column [message #372635 is a reply to message #372631] |
Thu, 01 March 2001 08:46  |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
Hello,
If you know which way the Primary Key is to be added meaning say you have already a column called Order_id that has numeric values in ascending order and trying to add a new field say
Order_pk_col then you can try this way:
Suppose you have old_table with 10 columns starting from order_id. You want the new structure to have 11 columns.
Then create the new table similar to old structure + ur new column.
Then execute this.
insert into new_table(order_pk_col, order_id,....other columns as is )
values
(select pk_seq.nextval,order_id, ... other columns
from old_table
where......)
Now you can drop old_table and rename the new table using " rename new_table to old_table";
However you should be careful for any existing parent_child relationships between this old table
and other tables
Purely a try. :)
|
|
|
|
| Re: more on adding a primary key column [message #372637 is a reply to message #372635] |
Thu, 01 March 2001 10:02 |
mb
Messages: 51
Registered: March 2001
|
Member |
|
|
ok thanks for that Madhav
so far I have done -
t1 has cols x,y,z.
create table t2 as
(select sequence1.NEXTVAL alias, x,y,z
from t1);
this actually creates the table with the new col populated with the sequence numbers.
I then have to put a primary key constraint on the NEXTVAL col afterwards. I was trying to put the primary constraint on as part of the t2 table build.
Doubtful Oracle will allow this from what I know of it - anybody know different?
Thanks,
Mark
|
|
|
|
| Re: more on adding a primary key column [message #372638 is a reply to message #372637] |
Thu, 01 March 2001 10:09 |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
No,
Since you are going to create new table you can infact add a primary key while creating table itself and then execute this insert command it should work, remember our new table is empty structure before we populate with this new data along with sequence, hence Oracle doesn't care if you add/enable/disable a PK on empty table.
You can delete all the rows if you have in new table right now and then
execute
alter table new_table
add constraint Primary Key....etc
and then populate it again.
Should work
HTH
Madhav
|
|
|
|
|
|
| Re: more on adding a primary key column [message #372640 is a reply to message #372638] |
Thu, 01 March 2001 10:31 |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
Hey,
I wrote insert into new table values(select statement from old table).
So I was assuming you already have created a new table along with new structure.
Well otherwise too you can do this way.
You can execute
create table new_table
as
select * from old_table where 1=2;
this will create a empty new_table
then
alter new_table
add constraint primary key...;
then
insert into new_table
values(select seq.nextval,col1,col2,... from old_table);
HTH
Madhav
|
|
|
|
|
|
| Re: more on adding a primary key column [message #372642 is a reply to message #372638] |
Thu, 01 March 2001 10:39 |
mb
Messages: 51
Registered: March 2001
|
Member |
|
|
do you mean typing this line in as it is?
What is the 1=2?
select * from old_table where 1=2;
I tried
create t2 as
(select seq.NEXTVAL CONSTRAINT c_pk PRIMARY KEY,
x, y, z from t1)
but while it will put the sequence in with no constraint adding the CONSTRAINT ... won't compile.
Mark
ps what does HTH mean?
M
|
|
|
|
|
|
| Re: more on adding a primary key column [message #372644 is a reply to message #372638] |
Thu, 01 March 2001 10:49 |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
Hello,
create table new_table as
select * from old_table where 1=2;
will create an empty structure exactly the replica of old one as 1 is not equal to 2, it won't create new rows.
BTW(By the Way), HTH means Hope This Helps :)
Can we start afresh on this?
Please follow the steps as is:
Create a new table using
create table new_table
( col1 .., col2.., so on ); this will have all the columns of old_table while col1 will be ur new PK Column.
Now you have a new_table with 11 columns where 1st column is PK Column and 10 others will be old columns
Now alter new_table
add primary key to first column
then
execute this
insert into new_table
values
(select seq.nextval, 10 other columns from old_table)
I think u should be fine.
HTH :)
|
|
|
|
| Re: more on adding a primary key column [message #373789 is a reply to message #372638] |
Wed, 09 May 2001 09:44 |
Lance E Sloan
Messages: 1
Registered: May 2001
|
Junior Member |
|
|
I'm trying to do something similar. I have
this table:
SQL> desc person_unavailable
Name Null? Type
--------------------- -------- ----
PERSONIDNUM NOT NULL NUMBER
PERSONUNAVAILABLEDATE DATE
TIMESLOTTYPECD NOT NULL VARCHAR2(20)
UNAVAILABILITYCD VARCHAR2(20)
COMMENTS VARCHAR2(200)
That has PERSONIDNUM and TIMESLOTTYPECD as primary
keys. But PERSONUNAVAILABLEDATE also needs to be
a primary key.
I tried following your example, like this:
alter table person_unavailable
add constraint primary key (PERSONUNAVAILABLEDATE)
But I got this error:
add constraint primary key (PERSONUNAVAILABLEDATE)
*
ERROR at line 2:
ORA-00902: invalid datatype
What have I done wrong?
(I'm an Oracle/SQL newbie, BTW.)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
