How to Create Table and define data integrity

In this post I will show you how you can create table and define data integrity on the table’s data.

To explain you with example I create a new database on my SQL Server instance with the name “sampleDB”. Here is the code to create this database.

 

Use master;

IF DB_ID(‘ sampleDB’) IS NULL

  CREATE DATABASE sampleDB;

 

 

Here “Use master” command brings you under the context of master database. I use DB_ID function to check if “sampleDB” database already exists or not and if it not exists, only then I am creating smapleDB database.

 

Creating Tables

The following code creates a table called Employee in the sampleDB database:

USE sampleDB;

 

IF OBJECT_ID(‘dbo.Employee’, ‘U’) IS NOT NULL

  DROP TABLE dbo.Employee;

 

CREATE TABLE dbo.Employee

(

  empid     INT         NOT NULL,

  firstname VARCHAR(30) NOT NULL,

  lastname  VARCHAR(30) NOT NULL,

  hiredate  DATE        NOT NULL,

  mgrid     INT         NULL,

  ssn       VARCHAR(20) NOT NULL,

  salary    MONEY       NOT NULL

);

 

Here I am changing my database context to sampleDB by using “USE sampled” command. Then using OBJECT_ID function I am checking if dbo.Employee table already exists or not (OBJECT_ID function returns NULL if object not exists) and if this table already exists in sampleDB database then I am dropping that table using “DROP TABLE” command.

After that I am creating the table using “CREATE TABLE” command. Here you specify the name of the table and, in parentheses, the definition of its attributes (columns). For each column you specify the column name, datatype, and NULLability.

In our Employee table the attributes empid (employee ID) and mgrid (manager ID) are defined as INT (four byte integer); firstname, lastname, and ssn (social security number) are defined as VARCHAR (variable length character string with the specified max supported number of characters); hiredate is defined as DATE and salary is defined as MONEY.

In our Employee table all columns are defined as NOT NULL except for the mgrid column. A NULL in the mgrid attribute would represent the fact that the employee has no manager, as in the case of the CEO of the organization.

 

Defining Data Integrity

One of the great benefits in the relational model is that data integrity is an integral part of it. Data integrity that is enforced as part of the model—namely, as part of the table definitions—is considered declarative data integrity. Data integrity that is enforced with code—such as with stored procedures or triggers—is considered procedural data integrity.

Now I will describe examples for declarative constraints, including primary key, unique, foreign key, check, and default constraints. You can define such constraints when creating a table as part of the CREATE TABLE statement, or after the table was already created using an ALTER TABLE statement.

 

Primary Key Constraints

A primary key constraint enforces uniqueness of rows and also disallows NULLs in the constraint attributes. An attempt to define a primary key constraint on a column that allows NULLs will be rejected by the RDBMS. Each table can have only one primary key.

Here’s an example of defining a primary key constraint on the empid column in the Employee table that you created earlier:

ALTER TABLE dbo.Employee

  ADD CONSTRAINT PK_Employee

  PRIMARY KEY(empid);

 

With this primary key in place, you can be assured that all empid values will be unique and known.

 

Unique Constraints

A unique constraint enforces uniqueness of rows. Unlike primary keys, multiple unique constraints can be defined in the same table. Also, a unique constraint is not restricted to columns defined as NOT NULL. You can have a single NULL value on a column on which you have created unique Constraint.

The following code defines a unique constraint on the ssn column in the Employee table:

ALTER TABLE dbo.Employee

  ADD CONSTRAINT UNQ_Employee_ssn

  UNIQUE(ssn);

 

 

Foreign Key Constraints

A foreign key enforces referential integrity. This constraint is defined on a set of attributes in what’s called the referencing table, and points to a set of candidate key (primary key or unique constraint) attributes in what’s called the referenced table. Note that the referencing and referenced tables can be one and the same. The foreign key’s purpose is to restrict the domain of values allowed in the foreign key columns to those that exist in the referenced columns.

The following code creates a table called Orders with a primary key defined on the orderid column:

 

IF OBJECT_ID(‘dbo.Orders’, ‘U’) IS NOT NULL

  DROP TABLE dbo.Orders;

 

CREATE TABLE dbo.Orders

(

  orderid   INT         NOT NULL,

  empid     INT         NOT NULL,

  custid    VARCHAR(10) NOT NULL,

  orderts   DATETIME    NOT NULL,

  qty       INT         NOT NULL,

  CONSTRAINT PK_Orders

    PRIMARY KEY(OrderID)

);

 

Suppose you want to enforce an integrity rule that restricts the domain of values supported by the empid column in the Orders table to the values that exist in the empid column in the Employee table. You can achieve this by defining a foreign key constraint on the empid column in the Orders table pointing to the empid column in the Employee table like so:

 

ALTER TABLE dbo.Orders

  ADD CONSTRAINT FK_Orders_Employee

  FOREIGN KEY(empid)

  REFERENCES dbo.Employee(empid);

 

Note that NULLs are allowed in the foreign key even if there are no NULLs in the referenced candidate key columns.

Foreign Key also establishes a rule that says that any attempt to delete rows from the referenced table or update the referenced candidate key attributes will be rejected if related rows exist in the referencing table. For example, if you try to delete an employee row from the Employee table when there are related orders in the Orders table, the RDBMS will reject such an attempt and produce an error. This is the default behavior of foreign key constraint.

You can change this default behavior of Foreign Key constraint by using some options with Foreign Key and applying actions based on those options. You can define the options ON DELETE and ON UPDATE with actions like CASCADE, SET DEFAULT, and SET NULL as part of the foreign key definition. CASCADE means that the operation (delete or update) will be cascaded to related rows. For example, ON DELETE CASCADE means that when you delete a row from the referenced table, the RDBMS will delete the related rows from the referencing table. SET DEFAULT and SET NULL mean that the compensating action will set the foreign key attributes of the related rows to the column’s default value or NULL respectively. 

 

Check Constraints

A check constraint allows you to define a predicate that a row must meet to enter the table or to be modified. For example, the following check constraint ensures that the salary column in the Employee table will support only positive values:

 

ALTER TABLE dbo.Employee

  ADD CONSTRAINT CHK_Employee_salary

  CHECK(salary > 0);

 

An attempt to insert or update a row with a nonpositive salary value will be rejected by the RDBMS. Note that a check constraint rejects an attempt to insert or update a row when the predicate evaluates to FALSE. The modification will be accepted when the predicate evaluates to either TRUE or UNKNOWN.

 

Default Constraints

A default constraint is associated with a particular attribute. It is an expression that is used as the default value when an explicit value is not specified for the attribute when you insert a row. For example, the following code defines a default constraint for the orderts attribute (representing the order’s timestamp):

 

ALTER TABLE dbo.Orders

  ADD CONSTRAINT DFT_Orders_orderts

  DEFAULT(CURRENT_TIMESTAMP) FOR orderts; 

 

The default expression invokes the CURRENT_TIMESTAMP function, which returns the current date and time value. Once this default expression is defined, whenever you insert a row in the Orders table and do not explicitly specify a value in the orderts attribute, SQL Server will set the attribute value to CURRENT_TIMESTAMP.

­­­

Advertisements

About Ashish Jain

I am Ashish Jain, a software engineer by profession. My goal of creating this blog is to share my knowledge of SQL server with all other SQL enthusiasts and also to learn from them.
This entry was posted in T-SQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s