Adding Primary key constraints Oracle

Asked By Bracken 220 points N/A Posted on - 05/18/2011

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.

• Status: Open
• Question Views: 1620
• Answer Count: 6
• Vote Up 22 Vote Down

• Answer Accepted: Yes
• Question Category: Oracle Database

Best Answer by Aithley Simpson

Go To Solution

Answered By Aithley Simpson 0 points N/A #99931

Adding Primary key constraints Oracle

 

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 ::||::

About Aithley Simpson

Questions
0

Answers
13

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 05/18/2011
• Question Category: Oracle Database

Answered By Bracken 220 points N/A #99933

Adding Primary key constraints Oracle

 

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?

About Bracken

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/18/2011
• Question Category: Oracle Database

Answered By Aithley Simpson 0 points N/A #99934

Adding Primary key constraints Oracle

 

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 ::||::

About Aithley Simpson

Questions
0

Answers
13

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 05/18/2011
• Question Category: Oracle Database

Answered By Bracken 220 points N/A #99935

Adding Primary key constraints Oracle

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?

About Bracken

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/18/2011
• Question Category: Oracle Database

Best Answer

Best Answer

Answered By Aithley Simpson 0 points N/A #99936

Adding Primary key constraints Oracle

 

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 ::||::

About Aithley Simpson

Questions
0

Answers
13

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 05/18/2011
• Question Category: Oracle Database

Answered By Bracken 220 points N/A #99937

Adding Primary key constraints Oracle

 

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

 

Regards,

 

Bracken.

About Bracken

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/19/2011
• Question Category: Oracle Database