DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: no constraints

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    no constraints

    Hi, i did a backup and recovery plan on one of my client database, cuse he did not have any. He lost 2 weeks of data because of this.

    And i found something that give me chils!
    They dont have any primary, foreign key, check constraints!!!!
    They have about 100 tables.
    I want to make them use constraints, but they told me they did not have any data lost because of not having constarints. But still why use a expensive database and not using basic security?

    They lost already time because of bad backup and recovery plan i dont want to see them lose data because someone delete a parent record and letting the child record in the database.

    Do you have suggestion on arguments to give on why use constraints?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Maybe they enforce RI through the front end. They might even do a great job on enforcing RI in a clear and consistant way. I still would never want to work on a database like this. But on the plus side DML run against the database should be fast. Especially inserts. Do they believe in indexes? They should be doing daily backups if they want to keep their data. and running in archive log mode is worth looking at.

    IMHO if it was my database I would start at the top and add the RI a little at a time. I can't imagine why you would want to rely on the front end to enforce ri. One programming bug could corrupt your entire database.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: no constraints

    Originally posted by steeve123

    I want to make them use constraints, but they told me they did not have any data lost because of not having constarints. But still why use a expensive database and not using basic security?
    From the 20,000 foot view, he's correct. Constraints had nothing to do with losing data. I would educate the client on the benefits of constraints and data integrity. If they're not worried about it, I wouldn't lose sleep over it.
    Jeff Hunter

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Firstly, they should be running in archivelog mode, no question. Relying on Export/Import is a disaster waiting to happen.

    Secondly, constraints can help performance -- never mind about the very modest impact of validating pk's, not nulls, and fk's, because you probably SELECT data many many more times than you INSERT or UPDATE it. Constraints help the optimizer produce a better execution plan, and can be created as "deferrable" anyway should they need the ability to temporarily suspend them during a transaction.

    It sounds like they deserved to lose some data.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width