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