Great article, Gaurav.
One thing I do like about constraints and triggers is that by keeping an additional layer of responsibility for referential integrity within the RDBMS (as opposed to the application) it means that any third party scripts or applications which access the database automatically enjoy the same level of protection as the web application.
RI can be destroyed in a second by an errant PERL script that somebody quickly knocked up to solve a problem late on a Friday afternoon; using constraints and triggers gets around this.
One thing you might be interested in looking at is writing some PHP classes which can read the database constraints and trigger schema and use it to automatically build validation logic in your application; that way you don’t have to constantly ‘match’ your application validation to the database schema, because it’s pulled out automatically on the fly.
Why use Constraints in SQL ?
Posted by Gaurav Jassal | on 10/01 at 02:19 PM | MySQL • PostgresSQL •
Constraints enables business rules to be enforced by the database instead of via application code. Through the judicious use of constraints, application and SQL coding can be minimized and data integrity can be maximized. Constraints may be applied to columns in the form of uniqueness requirements, relational integrity constraints to other tables/rows, allowable values and data types.For example, a column containing a product price should probably only accept positive values. But there is no standard data type that accepts only positive numbers. Another issue is that you might want to constrain column data with respect to other columns or rows. For example, in a table containing product information, there should be only one row for each product number.
With constraints you can have much control over the data and you can have it the way you want to be. You can have same rules in your web applications that you are writing in whatever language like PHP, ASP.NET, Perl, Python but constraints can save your alot of valuable time.
Constraints, in SQL Server, can be used to:
- # Enforce the range of data values that can be stored in a column (check constraints)
- # Enforce the uniqueness of a column or group of columns within a table (unique / primary key constraints)
- # Eenforce referential integrity (primary key and foreign key constraints)
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
CREATE TABLE products ( product_no integer UNIQUE NOT NULL, name text, price numeric );
A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, ... ); CREATE TABLE order_items ( product_no integer REFERENCES products, order_id integer REFERENCES orders, quantity integer, PRIMARY KEY (product_no, order_id) );
In the above example the foreign keys disallow creation of orders that do not relate to any products. But what if a product is removed after an order is created that references it? SQL allows you to handle that as well. Intuitively, we have a few options:
# Disallow deleting a referenced product
# Delete the orders as well
# Something else?
To illustrate this, In the second example implemented the following policy on the many-to-many relationship example above: when someone wants to remove a product that is still referenced by an order (via order_items), we disallow it. If someone removes an order, the order items are removed as well. This is a very important feature of contraints. Without this implementation you wil be writing additional SQL statements and conditional statement in your server side code.
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, ... ); CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (product_no, order_id) );
A Check constraint is a mechanism for allowing predicates to be defined on a column. The predicate is attached to the column as DDL and performs automatic edit checking of values as they are presented for insert or update to the table.
CREATE TABLE tblproducts ( product_id integer, product_name text, price numeric CHECK (price > 0) );
Another Example
CREATE TABLE tblproducts ( product_id integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) );
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric );
Enjoy this post? Share it with others.
I agree, great post. I would be glad if more people used constraints in their projects, they’re really useful in all parts of your project, including development, and prevent problems. Most people don’t know about them unfortunately.
Ed: great idea, it would prove quite powerful. It would require caching to be efficient but that should not be a problem since the schema usually don’t change often.
Some great MySQL features that haven’t been getting enough attention. Thank your for the writeup Gaurav.
Does anyone actually use database constraints *instead* of application code?
I’ve never seen a code that (for example) tries to add an order item, then if a database exception from a violated constraint (it’d have to parse the error to know) would notify the user that a business rule was violated.
No, you have to code the constraints into your application code also.
Constraints are useful (as mentioned) for protecting your database from errant scripts, and for catching bugs in your code while testing… but they don’t allow you to write less code.
Does anyone have an app in production that regularly violates database constraints (and it’s not a bug)?
I have an app in production with part of the code that violates regularly the database constraints. Most concerns the importing of data automatically from a CSV source, data is directly sent to the database and if an error occurs the code logs the rows it couldn’t insert so they can get fixed in the data source directly.
The thing is that you often want to catch the error before trying, either because you want to write a meaningful (or localized) error message or because if you were going to do 3 queries and the problem occur on the last query, the operation will be incomplete. So you think it’s better to catch the error early to prevent inconsistencies.
The problem with that last point is that it’s not the correct way to look at it. If you need to be sure 3 queries are executed successfully you need to use transactions. Constraints are not the only ones who can make your query fail.
When the data can be either good or bad (like with my imports), constraints (and proper escaping, of course) are enough. When you need to notify the user with what exactly is bad in the data, you have to write more code.
@Loïc—I imagine your imports are being run internally, right? If developers are reviewing the constraint violations of a bulk data import, that’s fine; they can sort out what the error messages mean.
I’ve written a few applications that took data uploads from non-developers, though—and we had to write application code to validate the data completely before loading it, to give useful error messages and explain the actual problem, not give them a response like “line 634 violated constraint X_PURC_ORD_ID_FKEY”.
It’s not just about where you catch the problem—I do use transactions—but database constraints just aren’t set up to be a validation scheme. Many things just don’t make sense as constraints (like the example of negative prices), constraint violation error messages aren’t useful for anyone but developers, and (probably the most important part) databases are far less flexible than code is.
Particularly if you have a series of applications accessing the same database, making changes is quite difficult, particularly as the complexity of the database, the size of the data, and the number of disparate accesses grow… it can become extremely difficult to know what processes will be disrupted when you change your business rules (surprise, we want to scan this gift card as a product with a negative price) and you need to change your database because of that. ...I’d rather avoid that pain when it’s easily avoidable.
I’m not against constraints, but they’re mostly a sanity-check and safety barrier… I’ve been saved by them (when I wrote bad code or a bad update…), but they’ve never saved me writing extra code that I can think of.
@Rob: Yes I mentioned they’re not enough if you need to display some meaningful error message. Maybe I expressed myself poorly, and I’m sorry if that’s the case.
I agree with you on the business rules part too. Constraints should be used as safety checks, not business rules. They’re here to help you protect data’s consistency, not validate data.
The imports were indeed internally, so I guess constraints help reduce the code size only when performing internal operations.
Next entry: Filter Array into Categorized format
Previous entry: Google's Project10tothe100
