FAQ
I just started at a new company about 4 months ago and I noticed that
about 4800 of the 7900 tables ( 60% ) do not have primary keys.

Is this a problem that I should bring to management or is it more based on
the business/application needs?

Thanks

Brian Lock
DBA - East Campus
ex: 22341

Search Discussions

  • Allan Nelson at May 31, 2007 at 5:15 pm
    This is very bad news. What you are in effect saying is that it is just
    fine with the database for 60% of the tables to silently accept duplicate
    rows. Even if the application running against the data today enforces no
    duplicates, or even if the business logic today says duplicates are fine you
    are violating one of the key rules for the relational model. This is
    significant because the relational model is based on the mathematics that
    guarantees that applying relational operators to relational tables produces
    relational result sets.

    Further, duplicate data will produce all the well known problems sooner or
    later.

    Finally, even if you have no duplicated data today because of the
    applications sooner or later someone will access the data with another
    application or tool and then you will have duplicated rows.

    I would suggest that you figure out what this means in application or
    business terms, inform your management, and if the problem is not fixed that
    you provide yourself with CYA documentation.

    Allan
    On 5/31/07, BLock_at_dvfs.com wrote:


    I just started at a new company about 4 months ago and I noticed that
    about 4800 of the 7900 tables ( 60% ) do not have primary keys.

    Is this a problem that I should bring to management or is it more based on
    the business/application needs?


    Thanks

    Brian Lock
    DBA - East Campus
    ex: 22341
    --
    http://www.freelists.org/webpage/oracle-l
  • Michael McMullen at May 31, 2007 at 5:20 pm
    7900 tables. This sounds like a canned app. Is it? If so, you can't change
    it.
    ----- Original Message -----
    From: BLock_at_dvfs.com
    To: oracle-l_at_freelists.org
    Sent: Thursday, May 31, 2007 12:08 PM
    Subject: Need for primary keys

    I just started at a new company about 4 months ago and I noticed that about
    4800 of the 7900 tables ( 60% ) do not have primary keys.

    Is this a problem that I should bring to management or is it more based on
    the business/application needs?

    Thanks

    Brian Lock
    DBA - East Campus
    ex: 22341

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at May 31, 2007 at 5:29 pm

    On 5/31/07, BLock_at_dvfs.com wrote:

    I just started at a new company about 4 months ago and I noticed that
    about 4800 of the 7900 tables ( 60% ) do not have primary keys.

    Is this a problem that I should bring to management or is it more based on
    the business/application needs?
    Do these tables at least have unique indexes?

    The lack of PK's would indicate that your new employer does
    not put much value on relational integrity, or is just ignorant
    of the importance of it.

    Or possibly even knows about it, but doesn't think it matters.
    (assuming these are custom apps of course - not much control
    over COTS apps )

    In any case, if you just started there, it would probably be best
    to learn the local political climate first. The new guy making waves
    is often not popular. Unless you have the blessing of someone
    in management that matters, it is a usually good idea to determine
    wait awhile and start with something small.

    Best learn what the apps are, how they work and who uses them,
    and look for places to help out - ask which processes are slow,
    help improve performance, that kind of thing.

    Improve DBA things that need improved - backups, testing restores,
    anything else that needs immediate attention.

    If you decide to pursue the PK issue, you may find that the
    database design is so haphazard that there isn't much point
    in worrying about PK's.

    If you do pursue it, pick an app (those 7900 tables are not
    all one app I hope) whose owner welcomes the help.
    ( see getting to know apps above)

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • JApplewhite_at_austinisd.org at May 31, 2007 at 5:54 pm
    Most of us believe strongly in maximum use of Declared Referential
    Integrity, so I think I would be joined by many in saying that your
    application is severly flawed and very probably contains trash data.
    However, some apps (a couple of our very own COTS systems here, actually)
    use Unique Indexes, but few declared Primary or Unique Keys. Unique
    Indexes are better than no uniqueness constraints whatsoever, but they
    don't support the necessary Foreign Key constraints on "child" tables.

    Unless your management is more enlightened than most, you'll probably need
    to present a business case of the extra costs incurred from problems the
    unconstrained dirty data causes. You can't just say "Sixty percent of our
    tables have no Primary Keys." They'll just say "So what?" Help them
    understand the business-related problems caused by bad data. If you can
    relate it to extra labor to correct mistakes, lost sales, etc., then
    you'll get their attention.

    Good luck!

    Jack C. Applewhite - Database Administrator
    Austin (Texas) Independent School District
    512.414.9715 (wk) / 512.935.5929 (pager)

    I'll just sit back in the shade while everyone gets laid.
    That's what I call Intelligent Design. -- God ("Origin of Species": Chris
    Smither)

    BLock_at_dvfs.com
    Sent by: oracle-l-bounce_at_freelists.org
    05/31/2007 11:58 AM
    Please respond to
    BLock_at_dvfs.com

    To
    oracle-l_at_freelists.org
    cc

    Subject
    Need for primary keys

    I just started at a new company about 4 months ago and I noticed that
    about 4800 of the 7900 tables ( 60% ) do not have primary keys.

    Is this a problem that I should bring to management or is it more based on
    the business/application needs?

    Thanks

    Brian Lock
    DBA - East Campus
    ex: 22341
  • Powell, Mark D at May 31, 2007 at 5:55 pm
    Well, if there are no PK constraints defined in the database for 60% of
    your tables then there are no FK constraints defined to 60% of your
    tables. This could be a problem or you could just have a huge number of
    transition/interface tables used to load or extract data from your
    database. You probably need to look a little closer at what the tables
    are used for.


    Generally speaking it is good form to define PK constraints on every
    table that has a suitable PK candidate. Tables used only as part of a
    load or extract process often have no need for a PK since it will never
    be used, but this should be a small percentage of the total.


    Mark D Powell --
    Phone (313) 592-5148

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of BLock_at_dvfs.com

    Sent: Thursday, May 31, 2007 12:08 PM
    To: oracle-l_at_freelists.org
    Subject: Need for primary keys

    I just started at a new company about 4 months ago and I noticed

    that about 4800 of the 7900 tables ( 60% ) do not have primary keys.


    Is this a problem that I should bring to management or is it
    more based on the business/application needs?



    Thanks


    Brian Lock
    DBA - East Campus
    ex: 22341
  • Allan Nelson at May 31, 2007 at 5:56 pm
    Yes, it is true that E-Business does that, but that doesn't mean it is a
    good idea. Oracle has lots of bad code and bad practices in E-Business. No
    need to perpetuate it to innocent bystanders.

    Allan
    On 5/31/07, John Clarke wrote:

    Absence of primary keys does not necessarily equate to lack of
    uniqueness. You can just as easily have unique indexes enforcing integrity
    on your tables (Oracle E-Business Applications for the most part does just
    this � very few primary/foreign keys, lots of unique indexes and application
    logic maintaining integrity).



    Best to assess the true risk before raising a red flag.


    ------------------------------

    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Allan Nelson
    *Sent:* Thursday, May 31, 2007 10:16 AM
    *To:* BLock_at_dvfs.com
    *Cc:* oracle-l_at_freelists.org
    *Subject:* Re: Need for primary keys



    This is very bad news.

    --
    http://www.freelists.org/webpage/oracle-l
  • Dennis Williams at May 31, 2007 at 8:50 pm
    Brian,

    Are these 7,900 tables from a single application or from a broad array of
    home-grown applications?
    Aside from the insightful comments provided so far, some vendor applications
    use their own data dictionary and maintain integrity themselves. Usually
    this concerns the local Oracle DBA.

    Dennis Williams
  • Amar kumar padhi at Jun 1, 2007 at 12:09 pm
    Hi Brain,
    we have some applications that do not make use of PKs. The design of
    these applications were done years back (oracle x/6/7 era). The data
    integrity is maintained using other type of constraints and triggers. I
    did an analysis some time back and came up with the following. This may
    not be applicable to your application but may give you some pointers why
    the design was so done in the past. Comparatively new projects make use
    of PK/FK, but then we do base our decision on the normalization level
    and whether the application is meant for Data warehousing or online/dss.
    Today what we consider as a wrong design may have provided an advantage
    in the past. For instance one 10 years old designed application (not
    using PK at all) is live at nearly 50 sites and we have not had a single
    instance of data integrity problem. Today, the only benefit we see in
    using PK in this application is to make use of new dependent features.

    Disadvantages that were notices in the older versions:-
    - In earlier releases, FK constraints resulted in performance issues.
    This was the key reason for not using it then.
    - Maintenance issue. It is difficult to copy/delete/refresh data for
    test/training setup, ordering of tables always required. We do this
    quite frequently.
    - indexes are controlled by constraints, disabling a pk means dropping
    the index. Be careful of this, re-enabling the constraint might change
    the index tablespace causing space and disk spread issues. This has been
    taken care of in 9i.
    - Constraints require Normalization and there are deviations in design
    methodologies. For performance and data accessibility reasons, some
    projects prefer not to normalize above 2F, some go till 3F. We
    identified some batch processing modules do duplicate data to provide
    reporting and performance gain.


    For new projects, we realised the importance of integrity as below.
    - Oracle has improved on constraints. Performance is not an issue,
    haven't heard anyone reporting so. If on 10g, I do not see any issues in
    using it, except if the design is not being satisfied or maintenance is
    a concern.
    - re-engineering is possible and table relationships can be pulled out
    from the system, easy to understand complex designs.
    - Some features in Oracle rely on PK/FK to be present. Can't use without
    these.
    - Strict control can be maintained on the design as well as the scripts
    being used for maintenance purpose. It will require some extra effort in
    the design stage though.

    Thanks!
    amar

    BLock_at_dvfs.com wrote:
    I just started at a new company about 4 months ago and I noticed that
    about 4800 of the 7900 tables ( 60% ) do not have primary keys.

    Is this a problem that I should bring to management or is it more
    based on the business/application needs?


    Thanks

    Brian Lock
    DBA - East Campus
    ex: 22341
    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Jun 1, 2007 at 6:08 pm

    On 6/1/07, amar kumar padhi wrote:
    Hi Brain,
    - Constraints require Normalization and there are deviations in design
    methodologies. For performance and data accessibility reasons, some
    projects prefer not to normalize above 2F, some go till 3F.
    Non 3nf will lead to data errors.

    For that matter, not going to 4nf will lead to data errors.
    There are alternatives for reporting.

    Materialized views for instance.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Derya Oktay at Jun 8, 2007 at 6:41 am
    Hi Brian,

    What about use of unique indexes? Maybe, in your db there exists unique
    indexes instead.

    And I wonder if this is a datewarehouse or an OLTP database. We have a DWH
    for example. We got no PKs in partitioned tables, we prefer using local
    unique indexes, instead of using global PKs in partitioned tables. It has
    lots of advantages.

    Also 3nf mostly suits with OLTP systems. Discussion of this topic may help
    to envision our thoughts.

    Regards,
    Derya.
    On 6/1/07, Jared Still wrote:
    On 6/1/07, amar kumar padhi wrote:

    Hi Brain,
    - Constraints require Normalization and there are deviations in design
    methodologies. For performance and data accessibility reasons, some
    projects prefer not to normalize above 2F, some go till 3F.
    Non 3nf will lead to data errors.

    For that matter, not going to 4nf will lead to data errors.
    There are alternatives for reporting.

    Materialized views for instance.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 31, '07 at 4:08p
activeJun 8, '07 at 6:41a
posts11
users9
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase