Sentry Page Protection
Proc SQL II [17-17]

Integrity Constraints
Integrity constraints are the restrictions on how the data can be captured in a table.

They can prevent invalid data from being added to the table.

Let's look at some examples.
Proc sql;
create table list
  (ID char(8),
   Age num,
   Gender char(2),
   Income num);
quit;

The code above creates an empty table with four columns:
  • ID
  • AGE
  • GENDER
  • INCOME

Before you insert data into the table, you want to set restrictions on the type of data that should not be inserted into the table.

For example, the subject’s age cannot be missing.

A restriction on the AGE column can be set using an integrity constraint.

Example
Proc sql;
create table list
  (ID char(8),
   Age num not null,
   Gender char(2),
   Income num);
quit;

The NOT NULL constraint is specified for the AGE column:

We are now going to insert a new subject into the LIST table whose age is missing.
Proc sql;
insert into list
  set id = "ID1001",
      age = .,
      Gender = "M",
      Income = 80000;
quit;

Note: the AGE column is missing as highlighted above.

Running the code above will result in an error:

ERROR: Add/Update failed for data set WORK.LIST because data value(s) do not comply with integrity constraint _NM0001_.

Since the integrity constraint specifies that the AGE column cannot be missing, the new subject cannot be inserted into the table without the age being specified.

Below are the four general integrity constraints:
  • CHECK
  • NOT NULL
  • UNIQUE
  • PRIMARY KEY

Note: there is also a referential integrity constraint called FOREIGN KEY. It is not covered in this training program.

Constraint #1: CHECK

The CHECK integrity constraint allows you to specify condition(s) where the data must meet.

For example, you can check and ensure the INCOME column must be positive in the table.

Constraint #2: NOT NULL

The NOT NULL integrity constraint ensures the table column must not be missing.

Constraint #3: UNIQUE

The UNIQUE integrity constraint ensures column values must be unique.

Constraint #4: PRIMARY KEY

The PRIMARY KEY integrity constraint combines the NOT NULL and UNIQUE constraints. It ensures the values in the columns are unique and not missing. It is often used in the ID column.​

​Let's look at an example.
Proc sql;
create table list
  (ID char(8) primary key,
   Age num ,
   Gender char(2),
   Income num);
quit;

The PRIMARY KEY constraint restricts the ID column to be unique and not missing:

We will now try inserting two subjects who have the same ID to the table.
Proc sql;
insert into list
  set id = "ID1001",
      age = 28,
      Gender = "M",
      Income = 80000
  set id = "ID1001",
      age = 33,
      Gender = "F",
      Income = 50000;
quit;

The Proc SQL step above will generate an error on the SAS log:

ERROR: Add/Update failed for data set WORK.LIST because data value(s) do not comply with integrity constraint _PK0001_.

The PRIMARY KEY integrity constraint prevents the same ID being inserted into the table.

Let's look at one more example.
Proc sql;
create table list
  (ID char(8) ,
   Age num ,
   Gender char(2),
   Income num check (income>=0));
quit;

The CHECK constraint tells SAS to ensure none of the INCOME values are negative.

We are now going to insert a new row where the income is 5,000:
Proc sql;
insert into list
  set id = "ID1001",
      age = 28,
      Gender = "M",
      Income = -5000;
quit;

As expected, another error message is generated:

ERROR: Add/Update failed for data set WORK.LIST because data value(s) do not comply with integrity constraint _CK0001_.

Sometimes, you will have to find out what integrity constraints are set for a particular table.

This can be done by using the DESCRIBE TABLE CONSTRAINT statement.

Example
Proc sql;
describe table constraints list;
quit;

The code in this example tells SAS to describe the integrity constraints from the LIST table.

The integrity constraint assigned earlier in this section is displayed in the output:
Exercise

Create an empty table that contains the following columns with the correct integrity constraints:
  • NAME: a character column that must be unique.
  • MODEL: a character column that must be unique and not missing.
  • PRICE: a numeric column that must be positive.
  • INVENTORY: a numeric column that must not be negative.

Name the table as PRODUCT. After you create the table, write a Proc SQL step to write the list of integrity constraints assigned to the table.
Need some help? 


HINT:
Use the DESCRIBE TABLE CONSTRAINTS statement to write the list of integrity constraints to the output.


SOLUTION:

Proc sql;
create table product
(Name char(40) unique,
Model char(200) primary key,
Price num check(price>0),
Inventory num check(inventory>=0));

describe table constraints product;
quit;

Fill out my online form.