99

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

La-comadreja
  • 5,627
  • 11
  • 36
  • 64
bala3569
  • 10,832
  • 28
  • 102
  • 146
  • 13
    This question has a clear answer. It is not "too broad." Its large number of upvotes and favorites reveal how useful it has been to many people. I've cleaned up the wording and nominated it for reopening. – La-comadreja Jul 26 '15 at 12:29
  • @BasilBourque this question is a _very_ poor fit for Programmers - it would be quickly voted down and closed over there, see http://meta.programmers.stackexchange.com/questions/6483/why-was-my-question-closed-or-down-voted/6490#6490 Recommended reading: **[What goes on Programmers.SE? A guide for Stack Overflow](http://meta.programmers.stackexchange.com/q/7182/31260)** – gnat Mar 28 '16 at 08:41
  • 1
    @gnat While that page leaves me still fuzzy-headed about what is or is not on-topic for Programmers Stack Exchange, I can understand and agree with a minimum threshold of the posting author having first read a [directly-relevant Wikipedia article](https://en.wikipedia.org/wiki/Relational_database#Constraints) that provides the same kind of condensed or summary overview being requested on the Stack Exchange. – Basil Bourque Mar 28 '16 at 20:10
  • 2
    The title is to broad, and the the sub questions is asking multiple questions at once -_- How does TomTom, Pரதீப், greg-449, bummi and Nit want it? Please explain. Is there anyway you can ask about constraints at all...? – Lealo Aug 16 '17 at 18:05
  • Although I understand the sentiments towards questions being too board and opinion-based, I don't agree with the requirement of first reading wikipedia article. It does NOT provide the "same kind of condensed or summary overview". The accepted answer provides a much more condensed and on-point answer than the wikipedia link above. – F.S. Jan 18 '20 at 01:51

9 Answers9

124

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
shA.t
  • 16,580
  • 5
  • 54
  • 111
Ziga Kranjec
  • 1,266
  • 1
  • 8
  • 4
  • 3
    +1 for including PK, not null, null – gbn Apr 03 '10 at 09:48
  • +1, still you could expand on FOREIGN KEY scenarios and provide some links. – Unreason Apr 03 '10 at 10:51
  • 2
    Check Constraints must not evaluate to false. They don't have to be true. Unknown is fine as well. – Martin Smith Nov 05 '11 at 23:03
  • 2
    Wouldn't a type constraint also be a common kind? – Martin Jun 15 '13 at 00:43
  • 1
    Although we don't usually call them constraints, I would argue that the data type is a form of constraint. If I define something as an Int or a datetime, that constrains the type of data that can be put into the field. Proper selection of data types is a critical part of ensuring data integrity. – HLGEM Sep 17 '15 at 14:38
37

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

Henning
  • 11,496
  • 5
  • 27
  • 25
Alex
  • 2,011
  • 3
  • 21
  • 27
7

Constraints are nothing but the rules on the data. What data is valid and what is invalid can be defined using constraints. So, that integrity of data can be maintained. Following are the widely used constraints:

  1. Primary Key : which uniquely identifies the data . If this constraint has been specified for certain column then we can't enter duplicate data in that column
  2. Check : Such as NOT NULL . Here we can specify what data we can enter for that particular column and what is not expected for that column.
  3. Foreign key : Foreign key references to the row of other table. So that data referred in one table from another table is always available for the referencing table.
Mp0int
  • 18,172
  • 15
  • 83
  • 114
user3107247
  • 71
  • 1
  • 1
3

Constraints can be used to enforce specific properties of data. A simple example is to limit an int column to values [0-100000]. This introduction looks good.

Anders Abel
  • 67,989
  • 17
  • 150
  • 217
3

Constraints dictate what values are valid for data in the database. For example, you can enforce the a value is not null (a NOT NULL constraint), or that it exists as a unique constraint in another table (a FOREIGN KEY constraint), or that it's unique within this table (a UNIQUE constraint or perhaps PRIMARY KEY constraint depending on your requirements). More general constraints can be implemented using CHECK constraints.

The MSDN documentation for SQL Server 2008 constraints is probably your best starting place.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
2

There are basically 4 types of main constraints in SQL:

  • Domain Constraint: if one of the attribute values provided for a new tuple is not of the specified attribute domain

  • Key Constraint: if the value of a key attribute in a new tuple already exists in another tuple in the relation

  • Referential Integrity: if a foreign key value in a new tuple references a primary key value that does not exist in the referenced relation

  • Entity Integrity: if the primary key value is null in a new tuple

Sheharyar
  • 73,588
  • 21
  • 168
  • 215
Ragu
  • 21
  • 2
2

  1. UNIQUE constraint (of which a PRIMARY KEY constraint is a variant). Checks that all values of a given field are unique across the table. This is X-axis constraint (records)

  2. CHECK constraint (of which a NOT NULL constraint is a variant). Checks that a certain condition holds for the expression over the fields of the same record. This is Y-axis constraint (fields)

  3. FOREIGN KEY constraint. Checks that a field's value is found among the values of a field in another table. This is Z-axis constraint (tables).

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Unique constraints and foreign key constraints can be written using `CHECK` constraints so why classify it as differently? i.e. "`Y`-axis" (whatever that means). – onedaywhen Oct 31 '11 at 09:24
  • 2
    @onedaywhen: how do you implement a `FOREIGN KEY` using a `CHECK` constraint? – Quassnoi Oct 31 '11 at 11:20
  • 1
    @onedaywhen: try creating a table with this constraint. – Quassnoi Oct 31 '11 at 14:26
  • Take 2: `CHECK ( NOT EXISTS ( SELECT ID FROM ReferencingTabke EXCEPT SELECT ID FROM ReferencedTabke ) );` -- the idea is the table expression should evaluate to the empty set when the constraint is satisfied. The constraint would be applied to the `ReferencingTabke` in this example. This is about as trivial as I can make it, you should be able to follow this, I feel. – onedaywhen Oct 31 '11 at 15:14
  • 1
    @onedaywhen: is it so hard to try? Hint: this won't work. – Quassnoi Oct 31 '11 at 15:32
  • I've poured over it quite a bit and, aside from spelling 'table' with a 'k', I can't see the flaw. – onedaywhen Oct 31 '11 at 16:14
  • `WITH ReferencedTable AS (SELECT * FROM (VALUES (1), (2)) AS T (ID)), ReferencingTable AS (SELECT * FROM (VALUES (1), (2)) AS T (ID)) SELECT 'Constraint satisfied' FROM ReferencingTable HAVING NOT EXISTS (SELECT ID FROM ReferencingTable EXCEPT SELECT ID FROM ReferencedTable);` Change one of the values in `ReferencedTable` and the constraint test is no longer satisfied. – onedaywhen Oct 31 '11 at 16:19
  • 3
    @onedaywhen: the query you wrote does not create a check constraint. It's a mere `SELECT` query. You cannot use subqueries (or any other constructs which refer to values outside the current record) in `CHECK` constraints in `SQL Server`. – Quassnoi Oct 31 '11 at 16:22
  • You were quoting chapter and verse from the Standard yesterday! And as a workaround for SQL Server this can be wrapped in a function, no? My point is that a `CHECK` constraint can involve more than one row/table so the x/y -axis and fields/rows/tables classifications have not been justified. – onedaywhen Nov 01 '11 at 06:42
  • @onedaywhen: interestingly, yes it can. The row order is not guaranteed and the scalar UDF will be called row-by-row (i. e. very slow), but it's possible. All other systems forbid it. – Quassnoi Nov 01 '11 at 11:01
2

A database is the computerized logical representation of a conceptual (or business) model, consisting of a set of informal business rules. These rules are the user-understood meaning of the data. Because computers comprehend only formal representations, business rules cannot be represented directly in a database. They must be mapped to a formal representation, a logical model, which consists of a set of integrity constraints. These constraints — the database schema — are the logical representation in the database of the business rules and, therefore, are the DBMS-understood meaning of the data. It follows that if the DBMS is unaware of and/or does not enforce the full set of constraints representing the business rules, it has an incomplete understanding of what the data means and, therefore, cannot guarantee (a) its integrity by preventing corruption, (b) the integrity of inferences it makes from it (that is, query results) — this is another way of saying that the DBMS is, at best, incomplete.

Note: The DBMS-“understood” meaning — integrity constraints — is not identical to the user-understood meaning — business rules — but, the loss of some meaning notwithstanding, we gain the ability to mechanize logical inferences from the data.

"An Old Class of Errors" by Fabian Pascal

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
-1

constraints are conditions, that can validate specific condition. Constraints related with database are Domain integrity, Entity integrity, Referential Integrity, User Defined Integrity constraints etc.

Prashant Kumbharkar
  • 37
  • 3