02 - Constraints (DDL)

Hi,

 

Here we'll cover below –

 

-          Table

-          Constraints

-          SP

-          Functions

-          Index

-          Triggers

 

 

 

Performance Impact of Constraints

 

Before we discuss constraints, find the performance hiccups with constraint as below.

 

·          Constraints help maintain database integrity in a number of ways; however, such protections introduce performance drawbacks.

·          Adding constraints can slow down the process of bulk imports, as you may want to consider temporarily dropping constraints (and indexes too) as a means of decreasing the load time.

 

·          If you have a choice between triggers and constraints, choose constraints if the functionality will meet your application needs.

·          Constraints operate against the data before a change is made and are generally faster and less of a performance drain.

 

·          Triggers, generally should be used for more complex business logic and data-driven decision-making.

·          There are also cases where constraints cannot be used; for example, when you require referential integrity for cross-database or cross-server tables.

·           

·          Constraints usually run faster than a trigger

 

 

 

Primary Key Constraint

 

·          Only one primary key is allowed for each table.

·          When a primary key is chosen, an underlying table index is created, defaulting to a CLUSTERED index (index types are reviewed later); you can explicitly choose a NONCLUSTERED index instead.

 

CREATE TABLE <table_name> (

<column_name_1> <datatype_for_column_1> NOT NULL,

<column_name_2> <datatype_for_column_2> NOT NULL,

CONSTRAINT <table_constraint_name> PRIMARY KEY CLUSTERED | NONCLUSTERED (c1) )

 

 

Unique Constraint

 

·          You can only have one primary key on each table. If you wish to enforce uniqueness in other non-key columns, you can use a UNIQUE constraint.

·          You can choose multiple UNIQUE constraints for each table and are allowed to set a UNIQUE constraint for columns that allow NULL values (although only one NULL value is allowed per column).

·          Like primary keys, UNIQUE constraints enforce entity integrity by  ensuring that rows can be uniquely identified.

 

·          The UNIQUE constraint, like a primary key constraint, creates an underlying table index. This index can be CLUSTERED or NONCLUSTERED; however, you may not create the index as CLUSTERED if such an index already exists for the table.

 

CREATE TABLE <table_name> (

<column_name_1> <datatype_for_column_1> NOT NULL,

<column_name_2> <datatype_for_column_2> NOT NULL,

CONSTRAINT <table_constraint_name> UNIQUE CLUSTERED | NONCLUSTERED (c1) )

 

 

Foreign Key Constraint

 

·          Foreign key constraints maitains  Referential Integrity and Domain Integridty.

·          Referential integrity -  Which means that every value in the foreign key column must exist in the corresponding column for the referenced table.

·          Domain integrity - They define the range of potential and allowed values for a specific column or columns.

 

·          The referenced table column or columns must be part of a primary key or unique constraint.

·          The referenced and referencing columns should also be of the same data type.

·          Also, you cannot define foreign key constraints across databases or servers (for that, use triggers).

 

Recursive Foregin Key –

 

CREATE TABLE Employees

(iEmployeeId int NOT NULL PRIMARY KEY,

vchFirstName varchar(100) NULL,

iManagerId int NOT NULL,

CONSTRAINT fk_ManagerId FOREIGN KEY (iManagerId) REFERENCES Employees (iEmployeeId))

 

 

Cascading Change - ON DELETE / ON UPDATE

 

·          Once Foregin Key constraint is placed on a Table - If an attempt is made to delete a primary key, but a row referencing this specific key exists in the foreign key table, an error will be returned.

·          All referencing foreign key rows must be deleted prior to deleting the primary key or unique value in question.

·          The two types of cascading changes are ON DELETE and ON UPDATE.

·          When ON DELETE is used, if someone attempts to delete a primary key or unique value, referencing rows in the foreign key table will also be deleted.

·          When ON UPDATE is used, if someone attempts to update a primary key or unique value, referencing rows in the foreign key table will also be updated.

 

 

CREATE TABLE Employees

(iEmployeeId int NOT NULL PRIMARY KEY,

vchFirstName varchar(100) NULL,

iManagerId int NOT NULL,

CONSTRAINT fk_ManagerId FOREIGN KEY (iManagerId) REFERENCES Employees (iEmployeeId))

ON DELETE <CASCADE or NO ACTION>

 

 

Check Constraint

 

·          Maintains Domain integrity

·          Controls Data Format, Data Type & Values Allowed

·          CHECK constraints are defined by an expression.

 

·          The CHECK clause also works with the NOT FOR REPLICATION clause, which enables rows that are inserted in replication to bypass the CHECK constraint.

 

CREATE TABLE <table_name> (

<column_name_1> <datatype_for_column_1> Nullability,

<column_name_2> <datatype_for_column_2> Nullability,

CONSTRAINT <column_constraint_name> CHECK(<column_name_N> expression))

 

e.g.

 

CONSTRAINT chkGender CHECK (chGender = 'M' or chGender = 'F'))

 

 

Default Value Constraint

 

·          Used when we do not know the value of a column in a row when it is first inserted into a table.

 

CREATE TABLE <table_name> (

<column_name_1> <datatype_for_column_1> Nullability,

<column_name_2> <datatype_for_column_2> Nullability  DEFAULT <default_value>)

 

Disable and Enable Constraints

 

ALTER TABLE <table_name>

[NOCHECK or CHECK] CONSTRAINT [ALL or <constraint_name> [,] <constraint_name_n…>]

 

 

Hope this helps.

 

Thanks & Regards,

Arun Manglick || Tech Lead