Integrity Constrains

1
Integrity Constrains

• Zheng Liu, Robert
• zliu_at_se.cuhk.edu.hk

2
Data Integrity

• Data integrity allows to define certain data
  quality requirements that the data in the
  database needs to meet. If a user tries to insert
  data that doesn't meet these requirements,
  database will not allow so.
• Constraint types, there are five integrity
  constraints in Oracle Not Null, Unique Key,
  Primary Key, Foreign Key, Check

3
Not Null

• A column in a table can be specified not null.
  It's not possible to insert a null in such a
  column. The default is null. So, in the following
  create table statement, a null can be inserted
  into the column named c.

4
Not Null

• The first two records can be inserted, the third
  cannot, throwing a ORA-01400 cannot insert NULL
  into ("RENE"."RI_NOT_NULL"."A").
• The not null/null constraint can be altered with
• After this modification, the column a can contain
  null values.

5
Unique Key

• The unique constraint doesn't allow duplicate
  values in a column. If the unique constraint
  encompasses two or more columns, no two equal
  combinations are allowed.

6
Unique Key

• However, if a column is not explicitly defined as
  not null, nulls can be inserted multiple times

7
Unique Key

• Now trying to insert the number 2 again into a
• This statement issues a ORA-00001 unique
  constraint (RENE.SYS_C001463 violated). Every
  constraint, by the way, has a name. In this case,
  the name is RENE.SYS_C001463.

8
Unique Key

• In order to remove that constraint, an alter
  table ... drop constraint ... is needed
• Of course, it is also possible to add a unique
  constraint on an existing table

9
Unique Key

• A unique constraint can be extended over multiple
  columns
• It is possible to name the constraint. The
  following example creates a unique constraint on
  the columns a and b and names the constraint
  uq_ri_3.

10
Primary Key Definition

• The primary key of a relational table uniquely
  identifies each record in the table. It can
  either be a normal attribute that is guaranteed
  to be unique (such as Social Security Number in a
  table with no more than one record per person) or
  it can be generated by the DBMS.
• Primary keys may consist of a single attribute or
  multiple attributes in combination.
• it must have a non-null value for each instance
  of the entity
• the value must be unique for each instance of an
  entity
• the values must not change or become null during
  the life of each entity instance

11
Primary Key
12
Primary Key
13
Primary Key

• On a technical level, a primary key combines a
  unique and a not null constraint. Additionally, a
  table can have at most one primary key. After
  creating a primary key, it can be referenced by a
  foreign key.

14
Primary Key

• Primary keys can explicitly be named. The
  following create table statement creates a table
  with a primary key whose name is pk_name.

15
Foreign Key

• A foreign key constraint (also called referential
  integrity constraint) on a column ensures that
  the value in that column is found in the primary
  key of another table.

16
Referential Integrity

• Create table customer
• (cust-name char(20) not null,
• cust-street char(30),
• cust-city char(30),
• primary key (cust-name)
• )
• Create table branch
• (branch-name char(15) not null,
• branch-city char(30),
• assets number,
• primary key (branch-name),
• check (assets ?0))

• Create table account
• (account-no char(10) not null,
• branch-name char(15),
• balance number,
• primary key (account-no),
• foreign key (branch-name) references branch,
• check (balance ?0))
• Create table depositor
• (cust-name char(20) not null,
• account-no char(10) not null,
• primary key (cust-name),
• foreign key (cust-name) references customer,
• foreign key (account-no) references account)

17
Referential Integrity

• Database modifications may violate referential
  integrity
• Insertion inserting a value into the referencing
  relation that is not in the referenced relation
• Deletion deleting the last example of a given
  value in the referenced relation and leaving that
  value in the referencing one
• proper handling may lead to cascading deletions
• Update to the referencing relation (constraints
  as Insertion)
• Update to the referenced relation (constraints as
  Deletion)

18
Check

• A check constraint allows to state a minimum
  requirement for the value in a column. If more
  complicated requirements are desired, an insert
  trigger must be used.
• The following table allows only numbers that are
  between 0 and 100 in the column a

19
Check

• Check constraints can be added after a table had
  been created
• It is also possible to state a check constraint
  that check the value of more than one column. The
  following example makes sure that the value of
  begin_ is smaller than the value of end_.

20
Assertions

• An assertion is an arbitrary expression that the
  database must always satisfy
• e.g., student GPA gt 2.8, or sum(all-charges) lt
  credit-line
• Domain constraints and referential integrity
  constraints are special forms of assertion that
  are easy to test
• SQL supports assertions as follows
• create assertion ltassertion-namegt check
  ltpredicategt
• When an assertion is made the system checks it
  for validity. If it is validated, every future
  modification of the database is checked against
  the assertion and allowed only if it is not
  violated.
• This can be very expensive if assertions are
  complex or numerous

21
Triggers

• A trigger is a statement that the system executes
  automatically as a side effect of an update to
  the database.
• A trigger has two parts
• condition under which it is executed
• actions to be taken if it is executed
• Example instead of having an assertion balance
  ?0 for a checking account, use a trigger on
  negative balances that sets the balance to zero
  and creates a new loan for the amount of the
  overdraft
• Triggers make the system reactive
• Triggers are also called active rules
• Like Assertions, Triggers can be very expensive.

22
Trigger Example

• When you create a trigger, you specify the table
  and the data modification commands that should
  "fire" or activate the trigger. Then, you specify
  the action(s) the trigger is to take.
• For example, this trigger prints a message every
  time anyone tries to insert, delete, or update
  data in the titles table

23
Trigger Example

• Or, using the if update clause

24
Trigger Example

• The create clause creates and names the trigger
• The on clause gives the name of the table that
  activates the trigger. This table is sometimes
  called the trigger table.
• The for clause specifies which data modification
  commands on the trigger table activate the
  trigger. In the earlier example, an insert,
  update, or delete to titles makes the message
  print.

25
Trigger Example

• Since triggers execute as part of a transaction,
  the following statements are not allowed in a
  trigger
• All create commands, including create database,
  create table, create index, create procedure,
  create default, create rule, create trigger, and
  create view
• All drop commands
• alter table and alter database
• truncate table