Home » SQL & PL/SQL » SQL & PL/SQL » Creating primary key on loaded table
icon5.gif  Creating primary key on loaded table [message #257971] Thu, 09 August 2007 12:48 Go to next message
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 #257972 is a reply to message #257971] Thu, 09 August 2007 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. Yes
2. Yes if you enlarge the column, for instance varchar2(10)->varchar2(20)

Regards
Michel
icon5.gif  Re: Creating primary key on loaded table [message #257974 is a reply to message #257972] Thu, 09 August 2007 12:57 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
What about constraints? Can we make one column which is null-able to not null-able?

Thanks
Re: Creating primary key on loaded table [message #257975 is a reply to message #257971] Thu, 09 August 2007 12:58 Go to previous messageGo to next message
joy_division
Messages: 4641
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 #257976 is a reply to message #257974] Thu, 09 August 2007 12:58 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
kham2k wrote on Thu, 09 August 2007 13:57
What about constraints? Can we make one column which is null-able to not null-able?



Yes, but again, only if you do not have any null rows.

Just try these things and see what happens.

[Updated on: Thu, 09 August 2007 12:59]

Report message to a moderator

Re: Creating primary key on loaded table [message #258009 is a reply to message #257975] Thu, 09 August 2007 16:38 Go to previous messageGo to next message
pablolee
Messages: 2834
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 Go to previous message
joy_division
Messages: 4641
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

Previous Topic: sun of bytes
Next Topic: Missing Lines In A Spool File...
Goto Forum:
  


Current Time: Tue Dec 06 14:26:24 CST 2016

Total time taken to generate the page: 0.10823 seconds