A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.
Data integrity comes in the following forms:
- Entity Integrity
- Domain Integrity
- Referential integrity
- User-Defined Integrity
Entity Integrity can be defined by defining
- Primary Key Constraint
- Unique Key Constraint
- or by building an Unique Indexes
Domain Integrity ensures that data values meet certain criteria; i.e. by choosing an appropriate data type.
- Other approaches include defining CHECK constraints or FOREIGN KEY constraints, or writing a trigger.
- Default Constraint, Null Constraint, NOT null Constraint also an aspect of domain integrity.
Referential integrity Enforces relationships between two tables, a referenced table, and a referencing table. It ensures that rows cannot be deleted or updated in the referenced, when used in referencing table.
- Foreign Key
User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories. Each of these categories of the data integrity can be enforced by the appropriate constraints.
Microsoft SQL Server supports the following constraints:
- PRIMARY KEY
- Composite Key or Compound key or Concatenated Key or Aggregate Key
- Single-Column Key
- Natural Key
- Surrogate Key
- Super Key
- Candidate Key
- Alternate Key or Secondary Key
- UNIQUE
- FOREIGN KEY
- CHECK
- NOT NULL
A PRIMARY KEY is a column or a combination of columns that uniquely identify a record. 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. NULL values are not allowed. A table can have only one primary key. A primary key adds a clustered index to the table The key can created for single column or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Not Null Constraint is automatically enforced. SQLPrimaryKeys
A Single-Column Key defined with an ever-increasing identity column.
Surrogate Key
It’s also Primary Key. If there no suitable natural key exists then we have to create surrogate key. It is a single column primary key that created automatically (example: Identity key column in SQL Server) or system generated values (like generated via a table in the schema).
Candidate Key
A column or a set of columns can be called as candidate key if they identify each row of a table uniquely. A table can have multiple candidate keys. One of them is specified as Primary key and rest of them can be called as alternate key.
Composite Key
A key formed by combining at least two or more columns is called Composite Key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.
Rules
- At all times it holds in the relation assigned to that variable that there are no
two distinct tuples (rows) with the same values for these attributes and - There is not a proper subset of this set of attributes for which (1) holds.
Natural Key
It’s also Primary Key. If it is reference by foreign key or by defining an unique index on it.
Super Key
A Super Key is a combination of attributes that can be uniquely used to identify a database record. A table might have many Super Keys. A primary key is therefore a minimum Super Key.
Alternate Key
A candidate key that is not the primary key is called an alternate key.
A UNIQUE constraint enforces the uniqueness of the values in a single column or a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints. Not Null Constraint is not automatically enforced. So it allows only one null value.
A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints. We can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement.
Default constraint is used to fill column with default value defined during creation of table if nothing is supplied while inserting data.
Super Key - Any combination of keys that make it unique.
Candidate Key - Minimum SK is called candidate key.
Primary Key - Any CK, which does not take null value.
Foreign Key - Reference to PK of another table with no nulls
Unique Key - Same feature is PK but can take null values. Also, in a table there can be many UK.
Any attribute that is uniquely identifying a row in a table is candidate key for the table. We select one of the candidate keys as Primary key.
All candidate keys, which are not chosen as primary key, are Alternate keys.
The key which uniquely identify the rows of the table and which is made up of more than one attribute is called Composite key. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key one of them will become the primary key and the rest are called alternate keys.
Reference
Natural vs. Surrogate Keys in SQL Server
Different Types of SQL Keys
Surrogate Keys, Natural Keys, Candidate Keys, Composite Keys and Super Keys?
No comments:
Post a Comment