Advertisement
ovalerio

Dropping a Primary Key using a named Unique Index in Oracle

Oct 2nd, 2019
974
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- TESTING THE BEHAVIOUR OF ORACLE WHEN DROPPING A PRIMARY KEY CONSTRAINT USING A NAMED UNIQUE INDEX
  2. -- ORACLE DOCUMENTATION SAYS THE FOLLOWING:
  3. -- When you drop a primary key constraint:
  4. --  If the primary key was created using an existing index, then the index is not dropped.
  5. --  If the primary key was created using a system-generated index, then the index is dropped.
  6.  
  7. -- CREATING A TABLE WITH A PK CONSTRAINT THAT USES A NAMED UNIQUE INDEX
  8. CREATE TABLE dummy_table (a int, b CHAR);
  9. CREATE UNIQUE INDEX dummy_ndx ON dummy_table(a);
  10. ALTER TABLE dummy_table ADD CONSTRAINT dummy_pk PRIMARY KEY (a) USING INDEX dummy_ndx;
  11.  
  12. -- POPULATING THE TABLE WITH VALUES
  13. INSERT INTO dummy_table VALUES (1, 'a');
  14. INSERT INTO dummy_table VALUES (2, 'b');
  15. INSERT INTO dummy_table VALUES (3, 'c');
  16.  
  17. -- ALTERING THE TABLE BY DROPPING THE PRIMARY KEY CONSTRAINT
  18. ALTER TABLE dummy_table DROP CONSTRAINT dummy_pk;
  19.  
  20. -- ATTEMPTING TO INSERT RECORDS AFTER THE PK HAS BEEN DROPPED
  21. INSERT INTO dummy_table VALUES (4, 'd'); -- this should work
  22. INSERT INTO dummy_table VALUES (2, 'm'); -- this triggers a unique constraint error
Advertisement
RAW Paste Data Copied
Advertisement