What are database constraints? [closed]

Question

What is a clear definition of database constraint? Why are constraints important for a database? What are the types of constraints?

Answer 1

Constraints are part of a database schema definition.

A constraint is usually associated with a table and is created with a CREATE CONSTRAINT or CREATE ASSERTION SQL statement.

They define certain properties that data in a database must comply with. They can apply to a column, a whole table, more than one table or an entire schema. A reliable database system ensures that constraints hold at all times (except possibly inside a transaction, for so called deferred constraints).

Common kinds of constraints are:

• not null - each value in a column must not be NULL
• unique - value(s) in specified column(s) must be unique for each row in a table
• primary key - value(s) in specified column(s) must be unique for each row in a table and not be NULL; normally each table in a database should have a primary key - it is used to identify individual records
• foreign key - value(s) in specified column(s) must reference an existing record in another table (via it's primary key or some other unique constraint)
• check - an expression is specified, which must evaluate to true for constraint to be satisfied

Answer 2

To understand why we need constraints, you must first understand the value of data integrity.

Data Integrity refers to the validity of data. Are your data valid? Are your data representing what you have designed them to?

What weird questions I ask you might think, but sadly enough all too often, databases are filled with garbage data, invalid references to rows in other tables, that are long gone... and values that doesn't mean anything to the business logic of your solution any longer.

All this garbage is not alone prone to reduce your performance, but is also a time-bomb under your application logic that eventually will retreive data that it is not designed to understand.

Constraints are rules you create at design-time that protect your data from becoming corrupt. It is essential for the long time survival of your heart child of a database solution. Without constraints your solution will definitely decay with time and heavy usage.

You have to acknowledge that designing your database design is only the birth of your solution. Here after it must live for (hopefully) a long time, and endure all kinds of (strange) behaviour by its end-users (ie. client applications). But this design-phase in development is crucial for the long-time success of your solution! Respect it, and pay it the time and attention it requires.

A wise man once said: "Data must protect itself!". And this is what constraints do. It is rules that keep the data in your database as valid as possible.

There are many ways of doing this, but basically they boil down to:

• Foreign key constraints is probably the most used constraint, and ensures that references to other tables are only allowed if there actually exists a target row to reference. This also makes it impossible to break such a relationship by deleting the referenced row creating a dead link.
• Check constraints can ensure that only specific values are allowed in certain column. You could create a constraint only allowing the word 'Yellow' or 'Blue' in a VARCHAR column. All other values would yield an error. Get ideas for usage of check constraints check the sys.check_constraints view in the AdventureWorks sample database
• Rules in SQL Server are just reusable Check Constraints (allows you to maintain the syntax from a single place, and making it easier to deploy your constraints to other databases)

As I've hinted here, it takes some thorough considerations to construct the best and most defensive constraint approach for your database design. You first need to know the possibilities and limitations of the different constraint types above. Further reading could include:

FOREIGN KEY Constraints - Microsoft

Foreign key constraint - w3schools

CHECK Constraints

Good luck! ;)