Advertisements
Sometimes you need to add by hand a primary key on a table that initially did not have any constraint.
Most of the time you will run into the issue of having duplicate rows, so first we must make sure there are no duplicate rows. Duplicate rows are the rows that have duplicates in the column that will be used as a primary key.
The following SQL is a quick solution to remove duplicate rows when the column that will be used as a criteria will be the column “id”:
DELETE FROM “mytable” WHERE rowid NOT IN (SELECT MAX(rowid) FROM “mytable” GROUP BY id);
Note that in Oracle we have to use “rowid” in place of “id” that is a reserved word.
Then we can simply alter the table and add the primary key:
ALTER TABLE “mytable” ADD PRIMARY KEY (“ID”) ENABLE;