Menu
If I know the correct format of fields, should I create check constraints for all of those fields, or will this affect the performance of inserts/updates too much? Would it be a good idea to use regular expressions for complex rules, or should I only use simple constraints like case and length?
The fields are already being validated at the application level.
884
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
Limitations of CHECK ConstraintsIf you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error. A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table. A CHECK constraint works at the row level.
You can define CHECK constraints at the column level, where the constraint applies only to a single column, and at the table level. You can also add CHECK constraints to a table using ADD CONSTRAINT .
In short: Rules is a feature that executes some validation functions based on some other data or systems. CHECK constraint is used to restrict the values in a column to allow only if it meets the condition based on this particular value.
In general it is best not to trust the application and use the check constraints. The data must maintain integrity (who knows what rogue script may run, or what program bug may slip through).
However, if you have many complex check constraints and you notice an insert/update slowdown, you may want to reevaluate. Is it really necessary to have one on every field? No. The column data type and length act as constraints too.
103
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
