6175

Integrity constraints in SQL Oracle

Лабораторная работа

Информатика, кибернетика и программирование

Integrity constraints in SQL Oracle Purpose of the lab To study SQL Oracle integrity constraints possibilities. To acquire practical skills in describing integrity constraints. Theoretical backgrounds You can define integrity...

Английский

2012-12-30

182 KB

9 чел.

Integrity constraints in SQL Oracle

  1.  Purpose of the lab
  •  To study SQL Oracle integrity constraints possibilities.
  •  To acquire practical skills in describing integrity constraints.
  1.  Theoretical backgrounds

You can define integrity constraints to enforce business rules on data in your tables. Once an integrity constraint is enabled, all data in the table must conform to the rule that it specifies. If you subsequently issue a SQL statement that modifies data in the table, then Oracle ensures that the resulting data satisfies the integrity constraint. Without integrity constraints, such business rules must be enforced programmatically by your application.

There are the following types of integrity constraints in SQL Oracle:

[NOT] NULL  A null is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.

UNIQUE A unique value defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).

PRIMARY KEY A primary key value defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.

FOREIGN KEY A rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).

Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:

CHECK  Constraints for complex integrity rules.

There are two syntactical forms to define integrity constraint: column constraint and table constraint.

Column constraint specify restrictions with respect to the values of concrete column. Table constraint specify restrictions with respect to the group of columns and defines out of column definition.

  1.  Syntax

  1.  Purpose

To define an integrity constraint. An integrity constraint is a rule that restricts the values for one or more columns in a table. There  

  1.  Prerequisites

Constraint clauses can appear in either CREATE TABLE or ALTER TABLE statements. To define an integrity constraint, you must have the privileges necessary to issue one of these statements.

To create a referential integrity constraint, the parent table must be in your own schema, or you must have the REFERENCES privilege on the columns of the referenced key in the parent table.

  1.  Keywords and Parameters

table_
constraint  

The table_constraint syntax is part of the table definition. An integrity constraint defined with this syntax can impose rules on any columns in the table.  

The table_constraint syntax can appear in a CREATE TABLE or ALTER TABLE statement. This syntax can define any type of integrity constraint except a NOT NULL constraint.  

column_
constraint  

The column_constraint syntax is part of a column definition. Usually, an integrity constraint defined with this syntax can impose rules only on the column in which it is defined.

  •  The column_constraint syntax that appears in a CREATE TABLE or ALTER TABLE ADD statement can define any type of integrity constraint.
  •  Column_constraint syntax that appears in an ALTER TABLE MODIFY column_options statement can only define or remove a NOT NULL constraint.

CONSTRAINT  

identifies the integrity constraint by the name constraint.  If you do not specify NULL or NOT NULL in a column definition, NULL is the default.

UNIQUE  

designates a column or combination of columns as a unique key. To satisfy a UNIQUE constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls.

A composite unique key is made up of a combination of columns. To define a composite unique key, you must use table_constraint syntax rather than column_constraint syntax. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.

You cannot designate the same column or combination of columns as both a unique key and a primary key.

PRIMARY KEY  

designates a column or combination of columns as the table's primary key. A composite primary key is made up of a combination of columns. To define a composite primary key, you must use the table_constraint syntax rather than the column_constraint syntax.

A table can have only one primary key.

No primary key value can appear in more than one row in the table.

No column that is part of the primary key can contain a null.

You cannot designate the same column or combination of columns as both a primary key and a unique key

NULL

NOT NULL  

 

 

determines whether a column can contain nulls. You must specify NULL and NOT NULL with column_constraint syntax, not with table_constraint syntax.  

NULL  

specifies that a column can contain null values. The NULL keyword does not actually define an integrity constraint. If you do not specify either NOT NULL or NULL, the column can contain nulls by default.  

NOT NULL  

specifies that a column cannot contain null values. To satisfy this constraint, every row in the table must contain a value for the column.  

Referential integrity constraints  

 

 

 

 

 

 

 

 

Referential integrity constraints designate a column or combination of columns as the foreign key and establish a relationship between that foreign key and a specified primary or unique key, called the referenced key. The table containing the foreign key is called the child table, and the table containing the referenced key is called the parent table. The foreign key and the referenced key can be in the same table. In this case, the parent and child tables are the same.  

From the table level, specify referential integrity using the foreign_key_clause with the table_constraint syntax. This syntax allows you to specify a composite foreign key, which is made up of a combination of columns.

From the column level, use the REFERENCES clause of the column_constraint syntax to specify a referential integrity constraint in which the foreign key is made up of a single column.  

You can designate the same column or combination of columns as both a foreign key and a primary or unique key.

You can define multiple foreign keys in a table. Also, a single column can be part of more than one foreign key.  

Restrictions: 

  •  The referenced UNIQUE or PRIMARY key constraint on the parent table must already be defined.
  •  The child and parent tables must be on the same database. 
  •  You cannot define a referential integrity constraint in a CREATE TABLE statement that contains an AS subquery clause. Instead, you must create the table without the constraint and then add it later with an ALTER TABLE statement.

foreign_key_clause  

designates a column or combination of columns as the foreign key from the table level. You must use this syntax to define a composite foreign key.

To satisfy a referential integrity constraint involving composite keys, either the values of the foreign key columns must match the values of the referenced key columns in a row in the parent table, or the value of at least one of the columns of the foreign key must be null.  

A composite foreign key must refer to a composite unique key or a composite primary key.

REFERENCES  

designates the current column or attribute as the foreign key and identifies the parent table and the column or combination of columns that make up the referenced key. If you identify only the parent table and omit the column names, the foreign key automatically references the primary key of the parent table. The corresponding columns of the referenced key and the foreign key must match in number and datatypes.  

ON DELETE

 

determines how Oracle automatically maintains referential integrity if you remove a referenced primary or unique key value. If you omit this clause, Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table.  

  •  CASCADE specifies that Oracle removes dependent foreign key values.
  •  SET NULL specifies that Oracle converts dependent foreign key values to NULL.

CHECK  

 

specifies a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). When Oracle evaluates a CHECK constraint condition for a particular row, any column names in the condition refer to the column values in that row.

If you create multiple CHECK constraints for a column, design them carefully so their purposes do not conflict. Oracle does not verify that CHECK conditions are not mutually exclusive.  

The condition of a CHECK constraint can refer to any column in the table, but it cannot refer to columns of other tables.  

  1.  Lab tasks

Create the following tables with integrity constraints.

NOTE. Our database has so cold recursive integrity constraint definitions/ It means the following: The TEACHER table references to the DEPARTMENT table and DEPARTMENT table references to the FACULTY table/ Ay the same time TEACHER and DEPARTMENT tables reference to the TEACHER table. That is why these tables are defined in such a way:

1) First of all the FACULTY and DEPARTMENT tables are defined. And at the same time the columns DeanFK HeadFK (it foreign keys))are defined without TEREFENCES clause

2) The table TEACHER is defined with all references keys

3) At last with the help of  ALTER TABLE statement the REFERENCES close is defined for columns DeanFK and HeadFK

NOTE. In terms of ER-modelling language our database has the following structure

Table name

Column name

Column

data type

Length

(precision)

Scale

Column constraint

FACULTY    

FacPK

integer

PRIMARY KEY

Name

varchar2

50

UNIQUE, cannot be NULL

DeanFK

integer

Dean of the faculty. It is a foreign key references to the TchPK column in the TEACHER table. If the dean is deleted from TEACHER table than the reference is set to NULL

Building

char

5

Building of the faculty. Takes the values: ’1’,  ’2’, ’3’, ’4’, ’5’, ’6’, ’7’, ’8’, ’9’, ’10’

Fund

number

9

2

fund cannot be less than 100000

table constraint

DEPARTMENT

DepPK

integer

PRIMARY KEY

FacFK

integer

FOREIGN KEY referenced to FacPK of the FACULTY. It is not possible to delete faculty if it has at least one department.

Name

varchar2

50

Department name. Cannot be NULL

HeadFK

integer

Head of the department. It is a foreign key references to the TchPK column in the TEACHER table. If the head is deleted from TEACHER table then the reference is set to NULL

Building

char

5

Building of the department. Takes the values: ’1’,  ’2’, ’3’, ’4’, ’5’, ’6’, ’7’, ’8’, ’9’, ’10’

Fund

number

8

2

Fund take the values in the interval 20000.00- 100000

table constraints

pair of columns (FacFK,name)is UNIQUE (that is the faculty cannot have different departments with identical names)

TEACHER

TchPK

integer

PRIMARY KEY

DepFR

integer

FOREIGN KEY referenced to DepNo of DEPARTMENT. If referenced department is deleted the key set to NULL.

Name

varchar2

50

Name of the teacher, cannot be NULL

Post

varchar2

20

Post of the teacher. Take the values from the list: assistant, counselor, assistant professor, professor

Tel

char

7

Hiredate

date

Hire date. should be more than 01.01.1950; cannot be NULL

Salary

number

6

2

Must be more than 1000, cannot be NULL

Commission

number

6

2

cannot be negative. DEFAULT value is 0

ChiefFK

Integer

Chief of the teacher. It is the foreign key that references to the primary key TchPK of the TEACHER table. If a teacher is deleted all references to it is set to NULL

table constraint

a) Commission should be at least twice less than salary

b) Salary + Commission is in interval 1000-3000

c) ChiefFK cannot be equal to TchPK (that is teacher cannot be chief of of himself)

SGROUP

GrpPK

integer

PRIMARY KEY

DepFK

integer

FOREIGN KEY referenced to DepPK of DEPARTMENT. If referenced department is deleted the key set to NULL.

Course

number

1

take the values from the list: 1, 2, 3, 4, 5, 6

Num

number

3

Group numbe. Takes the values in the interval 0-700

Quantity

number

2

Number of students in the group/ Take the value in the interval 1 - 50

Curator

integer

FOREIGN KEY referenced to TchPK of TEACHER. If referenced department is deleted the key set to NULL.

Rating

number

3

take the values in the interval 0 – 100. DEFAULT value is 0.

table constraints

Pair of values (DepFK, Num) is UNIQUE (that is any department cannot have groups with identical numbers)

Pair of foreign keys (DepFK, Curator) is UNIQUE. That is any teacher may be curator in only one group in any department.

SUBJECT

SbjPK

integer

PRIMARY KEY

Name

varchar2

50

cannot be NULL

table constraint

ROOM

RomPK

integer

PRIMARY KEY

Num

number

4

Room number, cannot be NULL

Seats

number

3

Number seats in the room, is in interval 1 - 300

Floor

number

2

Floor of the room, is in interval 1 – 16

Building

char

5

Building of the room/ cannot be NULL. Take values from the following list: ’1’,  ’2’, ’3’, ’4’, ’5’, ’6’, ’7’, ’8’, ’9’, ’10’

table constraint

pair of values (Num, Building) is UNIQUE

LECTURE

TchFK

integer

FOREIGN KEY references to TchPK of TEACHER. If references teacher is deleted the key is set to NULL.

GrpFK

integer

FOREIGN KEY references to GrpPK of SGROUP. If referenced group is deleted the corresponding lecture is deleted too (cascade deletion).

SbjFK

integer

FOREIGN KEY references to SbjNo of SUBJECT. It is not possible to delete subject if there exists at least one lecture that is referenced to this subject.

RomNo

integer

FOREIGN KEY referenced to RomNo of ROOM. If referenced room is deleted the key set to NULL.

Type

varchar2

25

Type of the lecture. Take the values from the list: lecture, lab, seminar, practice. Cannot be NULL

Day

char

3

Day of the week. Take the values from the list: sun, mon, tue, wed, thu, fri, sat.

Cannot be NULL

Week

number

1

Week of the lesson Take the values 1 or 2. Cannot be NULL

Lesson

number

1

Take the values in the interval 1 – 8.

Cannot be NULL

table constraint

Values of columns (GrpFK, Day, Week, Lesson) is UNIQUE. That is one group cannot has two or more different lectures in the same lesson, day and week.

Values of columns (TchFK, Day, Week, Lesson) is UNIQUE. That is the same condition is true for any teacher

  1.  Control questions

Please, give answers to the following questions:

  1.  What is the purpose of integrity constraints?
  2.  What type of integrity constraints do you know?
  3.  What syntactical forms of integrity constraints do you know? What are differences of these forms?
  4.  What does UNIQUE constraint means? What does composite unique key means? What syntax must be used (column constraint syntax, table constraint syntax) to describe composite unique key? Do NULL values allow in the columns with UNIQUE constraint? Is it possible to set UNIQUE and PRIMARY KEY constraints to the same column (combination of columns)?
  5.  What does PRIMARY KEY constraint mean? What is composite primary key? What syntax must be used (column constraint syntax, table constraint syntax) to describe composite primary key? How many primary keys may be defined in a table? Is it possible to set NULL value for the primary key column?
  6.  What does NULL (NOT NULL) constraint mean? What is a default value of this constraint? Is it possible to define this constraint with table constraint syntax?
  7.  What does FOREIGN KEY (referential integrity constraint) mean? Please, define the following terms: referenced key, foreign key, parent table, child table. What integrity constraint must have referenced column (combination of columns)? Is it possible to define foreign key and referenced key in the same table? What is composite foreign key? What syntax must be used (column constraint syntax, table constraint syntax) to describe composite foreign key? Is it possible to describe single column foreign key with table constraint syntax? Is it possible to use the same column (combination of columns) as foreign key and primary key? Is it possible to use the same column (combination of columns) as foreign key and unique key? Is it possible to define many foreign keys in a table? Is it possible to set NULL values of foreign key (composite foreign key)? Can you define a referential integrity constraint in a CREATE TABLE statement that contains an AS subquery clause? Is it possible to omit column names of parent table in foreign key clause? What does ON DELETE clause mean? What does absence of ON DELETE clause mean?
  8.  What does CHECK integrity constraint mean? When the CHECK condition is satisfied? Is it possible to define check constraint on multiple columns in column constraint syntax? Is it possible to define many CHECK constraints for the one column? Does Oracle verify that many CHECK conditions are mutually excusive? Is it possible to define CHECK conditions on the columns from different tables?
  9.  Appendices
    1.  Appendix. Conditions in SQL Oracle

A condition specifies a combination of one or more expressions and logical operators that evaluates to either TRUE, FALSE, or unknown. You must use this syntax whenever condition appears in SQL statements.

You can use a condition in the WHERE clause of these statements:

  •  DELETE
  •  SELECT
  •  UPDATE

You can use a condition in any of these clauses of the SELECT statement:

  •  WHERE
  •  START WITH
  •  CONNECT BY
  •  HAVING

You can use a condition in CHECK clause of CREATE TABLE statement.

A condition could be said to be of the "logical" datatype, although Oracle does not formally support such a datatype.

Conditions can have several forms, as shown in the following syntax. The complete description of each statement will be discussed in the following labs. The sections that follow describe in general the various forms of conditions.

condition::= 

  1.  Simple Comparison Conditions 

A simple comparison condition specifies a comparison with expressions or subquery results.

simple_comparison_condition::= 

Pay attention that SQL Oracle allows to compare not only two expressions but also two lists of expressions (in the second case the right hand list is created with the help of subquery).

  1.  Group Comparison Conditions 

A group comparison condition specifies a comparison with any or all members in a list or subquery.

group_comparison_condition::= 

  1.  Membership Conditions 

A membership condition tests for membership in a list or subquery.

membership_condition::= 

  1.  Range Conditions 

A range condition tests for inclusion in a range.

range_condition::= 

  1.  NULL Conditions 

A NULL condition tests for nulls.

NULL_condition::= 

  1.  EXISTS Conditions 

An EXISTS condition tests for existence of rows in a subquery.

EXISTS_condition::= 

  1.  LIKE Conditions 

A LIKE condition specifies a test involving pattern matching.

LIKE_condition::= 

  1.  Compound Conditions 

A compound condition specifies a combination of other conditions.

compound_condition::= 

  1.  Example of the tables creation

CREATE TABLE FACULTY

(FacNo INTEGER CONSTRAINT prk_fac PRIMARY KEY,

 name VARCHAR2(50)  CONSTRAINT nnl_nam NOT NULL

  CONSTRAINT unq_nam UNIQUE,

 dean VARCHAR2(50) CONSTRAINT unq_den UNIQUE,

 building char(5),

 fund number(7,2) CONSTRAINT chk_fnd CHECK (fund > 0));

CREATE TABLE DEPARTMENT

(DepNo INTEGER CONSTRAINT prk_dep PRIMARY KEY,

 FacNo INTEGER CONSTRAINT frk_dep REFERENCES FACULTY(FacNo) ON DELETE SET NULL,

 name VARCHAR2(50) CONSTRAINT nnl_nam,

 head VARCHAR2(50),

 building char(5),

 fund number(7,2) CONSTRAINT chk_fnd CHECK (fund > 0),

 CONSTRAINT unq_nmh UNIQUE (name, head));

CREATE TABLE TEACHER

(TchNo INTEGER CONSTRAINT prk_tch PRIMARY KEY,

 DepNo INTEGER CONSTRAINT frk_tch REFERENCES DEPARTMENT(DepNo) ON DELETE SET NULL,

 name VARCHAR2(50) CONSTRAINT nnl_nam NOT NULL,

 post VARCHAR(20) CONSTRAINT chk_pst CHECK (post IN ('assistant', 'counselor',

  'assistant professor', 'professor')),

 tel char(7),

 hiredate date CONSTRAINT nnl_hdt NOT NULL

  CONSTRAINT chk_hdt CHECK (hiredate >=

        TO_DATE('01.01.1950', 'DD.MM.YYYY')),

 salary NUMBER(6,2) CONSTRAINT nnl_sal NOT NULL

  CONSTRAINT chk_sal CHECK (salary > 0),

 Commission NUMBER(6,2) DEFAULT 0 CONSTRAINT chk_ink CHECK (Commission > 0),

 CONSTRAINT chk_sal_ink1 CHECK (2*Commission < salary),

 CONSTRAINT chk_sal_ink2 CHECK (salary + Commission < 2000));

CREATE TABLE SGROUP

(GrpNo INTEGER CONSTRAINT prk_grp PRIMARY KEY,

 DepNo INTEGER CONSTRAINT frk_grp REFERENCES DEPARTMENT(DepNo) ON DELETE SET NULL,

 course number(1) CONSTRAINT chk_crs CHECK (course IN (1, 2, 3, 4, 5)),

 num number(3),

 quantity number(2) CONSTRAINT chk_qty CHECK (quantity BETWEEN 1 AND 50),

 Curator integer CONSTRAINT frk_grp_tch REFERENCES TEACHER(TchNo),

 rating number(3) DEFAULT 0 CONSTRAINT chk_rat CHECK (rating BETWEEN 0 AND 100),

 CONSTRAINT unq_DepNo_num UNIQUE(DepNo, num));

CREATE TABLE SUBJECT

(SbjNo INTEGER CONSTRAINT prk_sbj PRIMARY KEY,

 name varchar2(50) CONSTRAINT nnl_nam NOT NULL);

CREATE TABLE ROOM

(RomNo INTEGER CONSTRAINT prk_rom PRIMARY KEY,

 num number(4) CONSTRAINT nnl_num NOT NULL,

 seats number(3) CONSTRAINT chk_set CHECK (seats BETWEEN 1 AND 300),

 floor number(2) CONSTRAINT chk_flr CHECK (floor BETWEEN 1 AND 16),

 building char(5) CONSTRAINT nnl_bld NOT NULL,

 CONSTRAINT unq_num_bld UNIQUE (num, building));

CREATE TABLE LECTURE

(TchNo INTEGER CONSTRAINT frk_lec_tch REFERENCES TEACHER(TchNo) ON DELETE SET NULL,

 GrpNo INTEGER CONSTRAINT frk_lec_grp REFERENCES SGROUP(GrpNo) ON DELETE CASCADE,

 SbjNo INTEGER CONSTRAINT frk_lec_sbj REFERENCES SUBJECT(SbjNo) ON DELETE SET NULL,

 RomNo INTEGER CONSTRAINT frk_lec_rom REFERENCES ROOM(RomNo) ON DELETE SET NULL,

 type varchar2(25) CONSTRAINT chk_typ CHECK (type in ('lecture', 'lab',

    'seminar', 'practice'))

   CONSTRAINT nnl_typ NOT NULL,

 day char(3) CONSTRAINT chk_day CHECK (day IN ('sun', 'mon', 'tue',

    'wed', 'thu', 'fri', 'sat'))

  CONSTRAINT nnl_day NOT NULL,

 week number(1) CONSTRAINT chk_wek CHECK (week IN (week = 1 or week = 2))

  CONSTRAINT nnl_wek NOT NULL,

 lesson number(1) CONSTRAINT chk_les CHECK (lesson BETWEEN 1 AND 8),

 CONSTRAINT unq_lec UNIQUE (GrpNo, day, week,lesson));

PAGE  11


 

А также другие работы, которые могут Вас заинтересовать

61205. Внутрішня будова Землі. Літосфера 71.5 KB
  Мета: Дати учням поняття земна кора і літосфера формувати систему знання про внутрішню будову Землі дати відомості про літосферні плити; розвивати просторову уяву уміння створювати образ обєкта; виховувати інтерес до пізнання природи Землі.
61206. Классно-урочная система обучения 35 KB
  Урок – это динамическая и вариативная форма организации процесса целенаправленного взаимодействия определенного состава учителей и учащихся включающая содержание формы методы средства обучения и систематически применяемая для решения задач образования развития воспитания в процессе обучения. История развития классноурочной формы обучения. Возникают формы массового обучения детей.
61207. Звуки [н], [н´]. Позначення їх буквою «ен». Написання імен з великої букви 237 KB
  Написання імен з великої букви. Написання імен з великої букви. Обладнання: картка з великою та малою буквою ен каса букв вирізані букви малюнки Незнайка синиця снігур часник виноград банан клена ножиці ніс нитки. Діти отримують букви які вирізані з цупкого картону.
61210. Г.С. Сковорода. «Сад Божественних пісень», «Всякому городу нрав і права», «De Libertate» 113 KB
  Охарактеризувати збірку його поетичних творів пісень; розкрити ідейнохудожній зміст програмових віршів митця; розвивати увагу пам’ять спостережливість уміння глибоко мислити надавати власну оцінку відповідний коментар...
61212. Структура ІС. Апаратна та програмна складові ІС 133.5 KB
  Учитель: С поняттям €œсистема ви багаторазово зустрічалися як в навчальних предметах так й в повсякденному житті: Сонячна система; періодична система хімічних елементів...
61213. Основні етапи розв’язання прикладної задачі з використанням комп’ютера. Поняття інформаційної моделі. Побудова моделі 264.5 KB
  Навчальні цілі: визначити поняття інформаційної моделі реального обєкта; розвинути вміння описувати інформаційні моделі шляхом виділення суттєвих (відповідно до мети моделювання) властивостей обєктів...