Showing posts with label Javascripts. Show all posts
Showing posts with label Javascripts. Show all posts

Wednesday, April 9, 2014

Constraints in a database maintain the integrity of the database.

Constraints in a database maintain the integrity of the database.

RDBMS Stands for (Relational dataBase management System)
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints:
--> PRIMARY KEY
 --> UNIQUE
 -->FOREIGN KEY
 --> CHECK
--> NOT NULL
A PRIMARY KEY constraint is a unique identifier for a row within a database table. 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.

A UNIQUE constraint enforces the uniqueness of the values in 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.

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.

You can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement
   
Example

CREATE TABLE employee(
   EmployeeId INT NOT NULL,
   LName VARCHAR(30) NOT NULL,
   FName VARCHAR(30) NOT NULL,
   Address VARCHAR(100) NOT NULL,
   HireDate DATETIME NOT NULL,
   Salary MONEY NOT NULL CONSTRAINT check_sale CHECK (salary > 0)
)

ALTER TABLE employee
ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeId)

ALTER TABLE employee
DROP CONSTRAINT pk_employee



Types of constraints
I focus on four types of constraints: primary key, foreign key, unique, and check. Here's a brief overview of each.

Primary key
This constraint is used to guarantee that a column or set of columns on a table contain unique values for every record in the given table. This lets you ensure data integrity by always being able to uniquely identify the record in the table.

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!
A table can have only one primary key constraint defined on it, and the rows in the primary key columns cannot contain null values. A primary key constraint can be defined when a table is created, or it can be added later.

This script creates a primary key constraint on a single field when the table is created:

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;
 GO
 CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL
)
 GO
The followings script creates the primary key constraint when the table is created. This method allows you to define a name for the constraint and to create the constraint on multiple columns if necessary.

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;
 GO
CREATE TABLE SalesHistory(
      SaleID int IDENTITY(1,1) NOT NULL,
      Product char(150) NULL,
      SaleDate datetime NULL,
      SalePrice money NULL,
      CONSTRAINT pk_SaleID PRIMARY KEY (SaleID)
 )
 GO
This script creates the primary key constraint on the table after it is created:

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;
 GO

 CREATE TABLE SalesHistory(
      SaleID  int IDENTITY(1,1) NOT NULL,
      Product  char(150) NULL,
      SaleDate  datetime NULL,
      SalePrice  money NULL
 )
 GO
 ALTER TABLE SalesHistory
 ADD CONSTRAINT pk_SaleID PRIMARY KEY (SaleID)
 GO
Foreign key
This constraint limits the values of columns in one table based upon the values of columns in another table. This link between the two tables requires the use of a "lookup table," which contains the accepted list of values; this list must contain a unique or primary key constraint. After the constraint is established between the two tables, any data modifications to the fields defined in the constraint on the foreign key table will cause a validation to ensure that the data being updated or inserted is contained in the lookup table.

The script in Listing A creates a ProductTypes table, which will serve as the lookup table and the SalesHistory table, which will reference the ProductID in the ProductTypes table. If I had excluded the constraint definition in the table declaration, I could go back later and add it. You can do this with the script in Listing B.

The previous script contains the WITH NOCHECK clause. I use it so that any existing values in the table are not considered when the constraint is added. Any records in the table that violate the newly added constraint will be ignored so that the constraint is created. The constraint will only be applicable to new records entered into the SalesHistory table.

Unique
This constraint guarantees that the values in a column or set of columns are unique. Unique and primary key constraints are somewhat similar because each provide a guarantee for uniqueness for a column or set of columns. A primary key constraint automatically has a unique constraint defined on it.

There are two differences between the constraints: (1) You may have only one primary key constraint per table, yet you may have many unique constraints per table; (2) A primary key constraint will not allow null values but a unique constraint will (although it will only allow one null value per field).

This script creates a unique constraint on the SaleID column when the table is created:

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;
 GO
CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int] NOT NULL UNIQUE,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL
 )
 GO
The following script creates a unique constraint on the table at creation, and it allows for constraint naming and for defining the unique constraint on multiple columns if necessary.

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;
 GO
 CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int]  NOT NULL,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL,
      CONSTRAINT uc_SaleID UNIQUE (SaleID)
 )
 GO
This script creates the unique constraint on the SalesHistory table by altering the table after it has been created:

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;
 GO
 CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int] NOT NULL,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL
 )
 GO
 ALTER TABLE SalesHistory
 ADD CONSTRAINT uc_SaleID UNIQUE(SaleID)
 GO
Check
This constraint limits the value range, or domain, in a column. Check constraints check the acceptable values against a logical expression defined in the constraint. These constraints are similar to foreign key constraints in that they both govern the acceptable values for a column or set of columns in a given row in a table. You can create a check constraint at the column or table level. A check constraint on a single column allows only certain values for those columns, while a table check constraint can limit values in certain columns based on values in other fields in the row.

The following script creates a check constraint on the SalePrice column in the SalesHistory table, limiting entries where the SalePrice must be greater than 4. Any attempt to enter a record with the SalePrice present and less than 4 will result in an error.

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;
 GO
 CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int]  NOT NULL,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL CHECK (SalePrice > 4)
 )

 GO