Enabling Oracle Constraint on Multiple Tables

1
votes

For my business app using Oracle 11.2.0 as the backend, we're going to have a series of tables Foo{0}, where {0} is a date, and there will be one table for each month end (long story on why this is required, but those are my marching orders). Each table will have a primary key constraint and an index, using the same columns for all tables. Instead of defining a constraint and index for all tables, is it possible to create one of each and apply those to all tables? Thanks for any help.

2
oracleindexingconstraints
Why don't you create a single table and partition that by the date? - a_horse_with_no_name
@a_horse_with_no_name - probably because Partitioning is a chargeable extra to the Enterprise Edition licence and hence expensive. - APC
The main problem right now is scale; we currently have all that data in one table, but due to the app requirements, it creates a lot of redundant data. We're migrating to SQL/Azure soon, so we're trying to find ways to reduce those redundancies. Each month, approximately 25,000 records are written with over 100 columns each in those tables. - Joe

2 Answers

2
votes

No. If you want 12 separate tables you would need to define 12 separate primary key constraints (and 12 separate indexes if you don't want Oracle to automatically create the index when you create the constraint).

1
votes

There is not a good reason to create those 12 tables in Oracle 11.2.

I suggest:

  • Create one table. partitioned into 12
  • Create a single single primary key constraint with associated global index
  • Create views over this table for Foo1, Foo2 ... Foo12 if required for compatability with existing code