locked
Is it possible to have too many check constraints on a table? RRS feed

  • Question

  • I have a data modeling question.

    I've searched the web and found a couple of topics stating there is no limit on the number of constraints you can have on either a column or a table.  Or a limit so high it's like having no limit.  But I'm wondering if you can add so many that you affect performance.  Like having too many indexes. 

    I have a table with 33 fields that are basically "switches".  Either it's true, false or unknown.  They are all defined as SMALLINT.  I want to add constraints to all these fields to edit for 0 or 1 so the application programs won't have to bother (0 is false; 1 is true; null is unknown).  I'm just not sure if this is too many. 

    Does having 33 check constraints on a single table give anyone heartburn?  :-) 

    note: The data gets into this table via OLTP.

    Thank you! 
    Wednesday, November 11, 2009 3:10 PM

Answers

  • As you have said it correctly having too many constraints on the table impacts performance.
    This is the reason why usually the constraints are disabled during Bulk UPLOADS and then enabled post upload.

    Constraints are good for data integrity and accuracy, but dont over use it.

    hope this helps.
    Regards
    Suds
    Tuesday, November 17, 2009 2:34 PM

  • Hi,


    A CHECK constraint takes longer to execute as compared to NOT NULL, PRIMARY KEY, FOREIGN KEY or UNIQUE constraints. Thus CHECK constraint must be avoided if the constraint can de defined using NOT NULL, PRIMARY KEY, FOREIGN KEY constraints.


    Hope this clarifies

    Regards
    Azhar

    Mark as answer if this helps


    Thanks and Regards Azhar Amir
    Thursday, November 19, 2009 3:54 AM

All replies

  • As you have said it correctly having too many constraints on the table impacts performance.
    This is the reason why usually the constraints are disabled during Bulk UPLOADS and then enabled post upload.

    Constraints are good for data integrity and accuracy, but dont over use it.

    hope this helps.
    Regards
    Suds
    Tuesday, November 17, 2009 2:34 PM

  • Hi,


    A CHECK constraint takes longer to execute as compared to NOT NULL, PRIMARY KEY, FOREIGN KEY or UNIQUE constraints. Thus CHECK constraint must be avoided if the constraint can de defined using NOT NULL, PRIMARY KEY, FOREIGN KEY constraints.


    Hope this clarifies

    Regards
    Azhar

    Mark as answer if this helps


    Thanks and Regards Azhar Amir
    Thursday, November 19, 2009 3:54 AM