Adding Primary key constraints Oracle

Asked By 220 points N/A Posted on -
qa-featured

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.

SHARE
Best Answer by Aithley Simpson
Answered By 0 points N/A #99931

Adding Primary key constraints Oracle

qa-featured

 

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 ::||::
Answered By 220 points N/A #99933

Adding Primary key constraints Oracle

qa-featured

 

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?
Answered By 0 points N/A #99934

Adding Primary key constraints Oracle

qa-featured

 

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 ::||::
Answered By 220 points N/A #99935

Adding Primary key constraints Oracle

qa-featured

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?

Best Answer
Best Answer
Answered By 0 points N/A #99936

Adding Primary key constraints Oracle

qa-featured

 

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 ::||::
Answered By 220 points N/A #99937

Adding Primary key constraints Oracle

qa-featured

 

Finally I managed to do it right. Thank you very much. It was really helpful.
 
Regards,
 
Bracken.

Related Questions