Blog

Some sample commands for Add/Drop/Rename objects:

--Dropping the primary key:
ALTER TABLE "VAG"."TEST_TABLE" drop primary key;
drop index "VAG"."TEST_TABLE_PK";

--Adding primary key:
CREATE UNIQUE INDEX "VAG"."TEST_TABLE_PK" ON "VAG"."TEST_TABLE" ("TEST_TABLE_ID");
ALTER TABLE "VAG"."TEST_TABLE" ADD CONSTRAINT "TEST_TABLE_PK" PRIMARY KEY ("TEST_TABLE_ID") ENABLE;

--Renaming the primary key:
ALTER INDEX "VAG"."TEST_TABLE_PK" RENAME TO "TEST_TABLE_PK01";

--Renaming a constraint
ALTER TABLE "VAG"."TEST_TABLE" RENAME CONSTRAINT "SYS_C00108926" TO "TEST_TABLE_C01";
ALTER TABLE TEST_TABLE RENAME CONSTRAINT SYS_C00108926 TO TEST_TABLE_C01;

--Renaming a column:
ALTER TABLE TEST_TABLE RENAME COLUMN CATEGORY_ID TO TEST_CATEGORY_ID;

--Modifying a column to not null (add constraint with system name):
ALTER TABLE TEST_TABLE MODIFY (TEST_CATEGORY_ID NOT NULL);

--Modifying a column to another type:
ALTER TABLE TEST_TABLE MODIFY (TEST_CATEGORY_ID NUMBER(10,0));

--Adding a not-null constraint (with a non-system name):
ALTER TABLE "VAG"."TEST_TABLE" MODIFY ("TEST_CATEGORY_ID" CONSTRAINT "TEST_TABLE_NN01" NOT NULL ENABLE);

--Adding a new column:
ALTER TABLE TEST_TABLE ADD(TEST_DESC VARCHAR2(100));

--Adding a comment on a column:
COMMENT ON COLUMN "VAG"."TEST_TABLE"."TEST_CATEGORY_ID" IS 'Category Id';

--Adding a foreign key on column:
ALTER TABLE "VAG"."TEST_TABLE" ADD CONSTRAINT "TEST_TABLE_FK01" FOREIGN KEY ("TEST_CATEGORY_ID")
      REFERENCES "VAG"."TEST_CATEGORIES" ("CATEGORY_ID") ENABLE;