dotConnect Oracle: Same foreign key constraint name for multiple references to the same table

0
votes

I have a rather complex database model (around 100 entities) in EF6 code first. One of these entities is called 'Base', another 'BaseAssociation'. 'BaseAssociation' establishes a many-to-many relation between 'Base' objects. Attached to the 'BaseAssociation' objects are 'BaseAssociationAttribute' objects, which provide meta data for the associations.

'BaseAssociation' contains two references to the 'Base' table, 'AssociatedBy' and 'AssociatedWith'. When creating the database model on an Oracle database with Devart dotConnect (8.4.215), the foreign key constraints for both references have the same name. Usually, the naming consists of the entity type and the property name. But somehow, this seems broken in my case. dotConnect produces the following sql:

ALTER TABLE "BaseAssociations"
  ADD CONSTRAINT "FK_Base_BaseAssociation" FOREIGN KEY ("AssociatedByRef") REFERENCES "Base" ("Id")
ALTER TABLE "BaseAssociations"
  ADD CONSTRAINT "FK_Base_BaseAssociation" FOREIGN KEY ("AssociatedWithRef") REFERENCES "Base" ("Id")

Somehow, both foreign keys are given the same constraint name.

Whereas executed on a sql server setup, everything works fine. Somehow, I was not able to reproduce this with a simpler model and all the other constraint names follow the default schema. I did not touch any of the code first conventions, but enabled 'truncate long default names', 'ignore schema name' and 'column type casing convention compatibility'.

I'm using EF6.1.1, dotconnect 8.4.215

Anyone got an idea? Thanks

1
c#oracleentity-framework-6devartdotconnect

1 Answers

0
votes

There is no way to do it. But anyway constraint names are not so relevant, you could add a sort of suffix to each name like FK_1, FK_2 ... FK_N. If this is so important, you must create the tables under different schemas.

The official Oracle documentation states:

Within a namespace, no two objects can have the same name.

The following schema objects share one namespace:

  • Tables
  • Views
  • Sequences
  • Private synonyms
  • Stand-alone procedures
  • Stand-alone stored functions
  • Packages
  • Materialized views
  • User-defined types

Each of the following schema objects has its own namespace:

  • Indexes
  • Constraints
  • Clusters
  • Database triggers
  • Private database links
  • Dimensions

Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.