1

I have sql Upgrade script which has many sql statements(DDL,DML). When i ran this upgrade script in SQL developer, it runs successufully.I also provide in my script at the bottom commit. I can see all the changes in the database after running this upgrade script except the unique index constraints. When i insert few duplicate records it says unique constraint violated. It means the table has unique constraints. But i dont know why i cant view this constraints in oracle sql developer. The other DDL changes made i can view.I dont know is there any settings to view it in oracle sql developer.

CREATE UNIQUE INDEX "RATOR_MONITORING"."CAPTURING_UK1" ON "RATOR_MONITORING"."CAPTURING" ("DB_TABLE"); 
CREATE UNIQUE INDEX "RATOR_MONITORING_CONFIGURATION"."BRAND_UK1" ON "RATOR_MONITORING_CONFIGURATION"."BRAND" ("NAME");
CREATE UNIQUE INDEX "RATOR_MONITORING_CONFIGURATION"."BRAND_BUSINESS_PROCESS_UK1" ON "RATOR_MONITORING_CONFIGURATION"."BRAND_BUSINESS_PROCESS" ("BRAND_ID", "BP_ID");
CREATE UNIQUE INDEX "RATOR_MONITORING_CONFIGURATION"."BRAND_ENGINE_UK1" ON "RATOR_MONITORING_CONFIGURATION"."BRAND_ENGINE" ("BRAND_ID", "ENGINE_ID");
Andrew
  • 3,632
  • 24
  • 64
  • 113
  • I may be missing a point in your explanation, but creating a `UNIQUE INDEX` does not automatically create the unique constraint. You have to explicitly create it. – A Hocevar Aug 17 '15 at 08:50
  • Yes but I assume unique constraint and unique index are same. – Andrew Aug 17 '15 at 11:04
  • you can query USER_INDEXES table to check if the indexes stated in your file are created or not and since they are DDL you are not required to explicitly mark them as COMMIT. Also could you please let us know the full error that you are getting when inserting duplicate records. – Sandeep Aug 17 '15 at 13:37

3 Answers3

2

As A Hocevar noted, if you create an index

 create unique index test_ux on test(id);

you see it in the Indexes tab of the table properties (not in the Constraints tab).

Please note that COMMIT is not required here, it is done implicitely in each DDL statement. More usual source of problems are stale metadata in SQL Developer, i.e. missing REFRESH (ctrl R on user or table node).

If you want to define the constraint, add following statement, that will reuse the index defined previously

 alter table test add constraint test_unique unique(id) using index test_ux;

See further discussion about the option in Documentation

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • Ohh now i see it should be seen in the Index tab not in the constraint tab. But assume unique constraint and unique index are same. – Andrew Aug 17 '15 at 11:01
  • They serve similar purpose but are two different concepts for the DBMS. See this for more information http://stackoverflow.com/questions/7521817/oracle-unique-constraint-and-unique-index-question – A Hocevar Aug 17 '15 at 11:12
1

I am assuming you are trying to look for index on a table in the correct tab in sql developer. If you are not able to see the index there, one reason could be that your user (the one with which you are logged in) doesn't have proper rights to see the Index.

mehta
  • 735
  • 1
  • 11
  • 26
1

If you not obtain any error, the solution is very simple and tedious. SQL Developer doesn't refresh his fetched structures. Kindly push Refresh blue icon (or use Ctrl-R) in Connections view or disconnect and connect again (or restart SQL Developer) to see your changes in structures.

hariprasad
  • 555
  • 11
  • 20