Webdevelopment Blog

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
);

4 Responses to “Why use Constraints in SQL ?”

1. Ed Lecky-Thompson says:

   January 27, 2009 at 6:02 am

   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.
2. Loïc Hoguin says:

   January 27, 2009 at 6:03 am

   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.
3. water softening systems says:

   January 29, 2009 at 8:50 am

   Does anyone use database constraints *instead* of app logic to enforce business rules?
4. Heartburn Home Remedy says:

   April 15, 2009 at 6:59 am

   Hey, nice tips. I’ll buy a bottle of beer to that person from that forum who told me to go to your blog