Creating primary key on loaded table [message #257971] |
Thu, 09 August 2007 12:48  |
kham2k
Messages: 34 Registered: May 2007
|
Member |
|
|
HI All
I want to know that is there any way in "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production " to create primary key on a table which is already loaded with data, and to change the data type of a certain column again loaded with data?
Thanks for your help.
kham2k
|
|
|
|
|
Re: Creating primary key on loaded table [message #257975 is a reply to message #257971] |
Thu, 09 August 2007 12:58   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Just to clarify, as it might not seem obvious to a novice...
You can create the Primary Key if there are no duplicates, or else you will have to create it with the deferrable keyword.
Like Michel said, you can change the length in the case given, but in most cases, you will not be able to change the datatype, even if all the data conforms to the new datatype.
|
|
|
|
Re: Creating primary key on loaded table [message #258009 is a reply to message #257975] |
Thu, 09 August 2007 16:38   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
joy_division wrote on Thu, 09 August 2007 18:58 | Just to clarify, as it might not seem obvious to a novice...
[snip]... or else you will have to create it with the deferrable keyword.
|
Unless I am misunderstanding you, this is wrong:
SQL> drop table b1;
Table dropped.
SQL> create table b1 (x int, y int);
Table created.
SQL> insert into b1 select 1, 1 from dual
2 union
3 select 1, 2 from dual
4 union
5 select 2, 1 from dual;
3 rows created.
SQL>
SQL> alter table b1 add constraint b1pk primary key (x) deferrable;
alter table b1 add constraint b1pk primary key (x) deferrable
*
ERROR at line 1:
ORA-02437: cannot validate (HR.B1PK) - primary key violated
SQL> alter table b1 add constraint b1pk primary key (x) enable novalidate;
alter table b1 add constraint b1pk primary key (x) enable novalidate
*
ERROR at line 1:
ORA-02437: cannot validate (HR.B1PK) - primary key violated
However, you can eithe create the pk and use the disable keyword, or you can pre-create a non-unique index on the column(s) and enable novalidate the constraint
SQL> create index b1pk on b1(x);
Index created.
SQL>
SQL> alter table b1 add constraint b1pk primary key (x) enable novalidate;
Table altered.
Apologies if I misunderstood the point you are making.
|
|
|
Re: Creating primary key on loaded table [message #259219 is a reply to message #258009] |
Tue, 14 August 2007 12:39  |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Correct Pabolee. I don't know what I was thinking. I have been having a really bad couple of weeks. It started out as physical, but now it has spread to the mental stages.
[edit]
ok, ok, maybe I was thinking this:
FOO SCOTT>select * from EMPLOYEE;
DEPRATMENT_ID EMP_ID
------------- ----------
1 10
1 20
1 30
1 40
2 50
2 60
2 70
2 80
3 90
3 11
4 13
5 14
5 15
5 16
5 17
5 18
16 rows selected.
FOO SCOTT>alter table EMPLOYEE add constraint foo11 primary key (DEPRATMENT_ID) deferrable novalidate;
Table altered.
[Updated on: Tue, 14 August 2007 12:52] Report message to a moderator
|
|
|