What are database constraints?

sql sql-server constraints rdbms
bala3569 picture bala3569 · Apr 3, 2010 · Viewed 161k times · Source

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

Answer

Ziga Kranjec picture Ziga Kranjec · Apr 3, 2010

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

Related questions

Add primary key to existing table

I have an existing table called Persion. In this table I have 5 columns: persionId Pname PMid Pdescription Pamt When I created this table, I set PersionId and Pname as the primary key. I now want to include one more column …

Read More
Turn off constraints temporarily (MS SQL)

I'm looking for a way to temporarily turn off all DB's constraints (eg table relationships). I need to copy (using INSERTs) one DB's tables to another DB. I know I can achieve that by executing commands in proper order (to …

Read More
SQL Server 2005 How Create a Unique Constraint?

How do I create a unique constraint on an existing table in SQL Server 2005? I am looking for both the TSQL and how to do it in the Database Diagram.

Read More