Home > Solution Categories > Database > Oracle Database > Adding Primary key constraints Oracle
Question
No of visitors who read this post: 526
Category: Oracle Database
Type: Question
Author: Bracken
Your rating: None Average: 5 (22 votes)

Can any one help me altering an Oracle table? I have made a table and it has some data in it. But unfortunately, I forgot to make any primary key on that table. Is it possible to make a primary key now? I don't want to delete my previous data, that is already in the table.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

# (Solution Accepted)

Submitted by Aithley Simpson on Wed, 2011-05-18 19:43. Comment Id: 8863
Hi Bracken,
 
Yes, you can add primary key constraint after inserting data. You just have to alter the table to add a new constraint. The syntax will be like this:
 
" ALTER TABLE [table name] ADD CONSTRAINTS [constraint name] PRIMARY KEY (column name) "
 
Note: Here column name is the column which you want to assign as the primary key field. Hope this helps. Best of luck.
 
::||:: for programmers christmas==hallowen bcz 25DEC==31OCT ::||::

#

Submitted by Bracken on Thu, 2011-05-19 02:39. Comment Id: 8883
Well I’m trying to do something like this:
 
“alter table test_1 add constraints pk_test_1_t_id primary key (t_id); ”, but it says error: “ ERROR at line 1: ORA-01449: column contains NULL values; cannot alter to NOT NULL ” What's wrong?

#

Submitted by Aithley Simpson on Thu, 2011-05-19 02:55. Comment Id: 8886
I think the column “t_id” is containing some NULL values. Just fill up the NULL fields with valid values and it will be ready to work with.
 
::||:: for programmers christmas==hallowen bcz 25DEC==31OCT ::||::

#

Submitted by Bracken on Thu, 2011-05-19 03:44. Comment Id: 8892

Ok. Now it’s getting really confusing. I eliminated every null value with some valid number data as the t_id column is of number type. But still it is showing some error message. “ERROR at line 1: ORA-02437: cannot validate (SCOTT.PK_TEST_1_T_ID) - primary key violated ”. How is the primary key violated when I couldn’t even assign it?

# (Solution Accepted)

Submitted by Aithley Simpson on Thu, 2011-05-19 04:07. Comment Id: 8896
lol. It’s not like that. Primary key means unique and not null values and I think your table has some identical values in column “t_id” (I mean same values). You check out if there are any duplicate values in column “t_id” and change them if they are same.
 
::||:: for programmers christmas==hallowen bcz 25DEC==31OCT ::||::

#

Submitted by Bracken on Thu, 2011-05-19 12:20. Comment Id: 8975
Finally I managed to do it right. Thank you very much. It was really helpful.
 
Regards,
 
Bracken.
Previous Previous Page
226 of 234
Next Page Next