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