Know More about SQL Constrains

SQL Constrains 

Constraints are used to apply business rules for the database tables.Also uses constraint definitions to build high-performance query execution plans.

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

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

Constraints can be defined in two ways 

1. The constraints can be specified immediately after the column definition. This is called column-level definition.
2. The constraints can be specified after all the columns are defined. This is called table-level definition. 

Background

To know the usage of constrains and its all types in SQL server. 

Common kinds of constraints are: 

Not Null

• value in a column must not be NULL this: 

Unique

• value(s) in specified column(s) must be unique for each row in a table, it inclues one NULL
• enforce the uniqueness of the values in a set of column

Check

• an expression is specified, which must evaluate to true for constraint to be satisfied.
• You can apply multiple CHECK constraints to a single column.
• there must be at least one row in table CheckTable
• not validated during DELETE statements, may produce unexpected results.
• the Database Engine inserts NULL and does not return an error when condition fails.

Primary Key

• value(s) in specified column(s) must be unique for each row in a table and not be NULL
• a column or combination of columns that contain values that uniquely identify each row in the table.
• a table can have only one PRIMARY KEY
• 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)
• can reference columns in tables in the same database or within the same table. These are called self-referencing tables.
• SQL Server does not have a predefined limit on either the number of FOREIGN KEY constraints a table, recommend that a table contain no more than 253 FOREIGN KEY constraints.

Rules

• are created as separate objects and then bound to the column. 
• Avoid using this feature in new develop ment work, and plan to modify applications that currently use this feature. Use CHECK constraints instead, Since This feature will be removed in a future version of Microsoft SQL Server.

Default

• Defaults specify what values are used in a column if you do not specify a value for the column when you insert a row.
• Query To Create Default Constraint Over Table Column

ALTER TABLE Customers ADD CONSTRAINT DF_Customers_Address2 DEFAULT 'UNKNOWN' FOR Address2

Candidate key

• A column, or set of columns that can uniquely identify a row in a table.
• can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Candidate keys that follow all the three rules

1. Not Null
2. Unique Value in Table
3. Static

Alternate key

• Any candidate key that has not been selected as the primary key.
• the column may not be primary key but still it is the unique key of the column.

Composite key

• A key that is composed of more than one column.

Super Key

• Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.
• Example : Primary key, Unique key, Alternate key are subset of Super Keys.

List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database 

GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO 

Points of Interest

A key is a single or combination of multiple fields in a table. Its is used to fetch or retrieve records/data-rows from data table according to the condition/requirement. Keys are also used to create relationship among different database tables or views.

History 

10 Dec  2014, Initial version