Advertisement
ovalerio

Dropping and Flashback a Table with a Foreign Key in Oracle

Oct 8th, 2019
1,033
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Dropping a table for which a foreign key exists
  2. CREATE TABLE DEPT (DEPTNO NUMBER(2) PRIMARY KEY, DEPTNAME VARCHAR2(2));
  3. CREATE TABLE EMP (EMPNO NUMBER(1) PRIMARY KEY, ENAME VARCHAR2(4), DEPTNO NUMBER(2) REFERENCES DEPT(DEPTNO));
  4.  
  5. -- Checking FK and PK on EMP table.  Also have a view at the indexes.
  6. SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP';
  7. SELECT * FROM USER_INDEXES WHERE TABLE_NAME IN('EMP','DEPT');
  8.  
  9. -- Inserting data into DEPT and EMP tables
  10. INSERT INTO DEPT VALUES (10, 'IT');
  11. INSERT INTO DEPT VALUES (20, 'HR');
  12.  
  13. INSERT INTO EMP VALUES (1, 'KING', 10);
  14. INSERT INTO EMP VALUES (2, 'HARI', 20);
  15.  
  16. COMMIT;
  17.  
  18. -- Dropping the EMP table and then recovering it back using a flashback
  19. DROP TABLE EMP;
  20. FLASHBACK TABLE EMP TO BEFORE DROP;
  21.  
  22. -- Reading table EMP contents after the flashback
  23. SELECT * FROM EMP;
  24.  
  25. -- Finding which constraints where recovered after the flashback operation (FK is gone)
  26. SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP';
  27.  
  28. -- Attempting to insert data into EMP table
  29. INSERT INTO EMP VALUES (2, 'COTT', 10); -- PK violation
  30. INSERT INTO EMP VALUES (3, 'ING', 55);  -- should work because no FK constraint after flashback
  31.  
  32. -- Reading table EMP contents after the above insert operations
  33. SELECT * FROM EMP;
  34.  
  35. -- Reading the indexes on EMP table
  36. SELECT * FROM USER_INDEXES WHERE TABLE_NAME IN('EMP','DEPT');
Advertisement
RAW Paste Data Copied
Advertisement