Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Oracle SQL into #11

Integrity constraints
by

Rodion Mironov

on 11 February 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL into #11

Introduction to Oracle SQL.
Integrity constraints Agenda not null
unique
primary key
foreign key
check INTEGRITY CONSTRAINTS - rules or limitations enforcing data accuracy and consistency. Types of constraints:
not null;
unique;
primary key;
foreign key;
check; NOT NULL:
prohibits NULL value in specific column; create table person(
person_id number(10) not null
,full_name varchar2(60) constraint person_fname_nn not null
,birth_date date default SYSDATE
,sex char(1)
);
-- not null after table creation
alter table person
modify (birth_date not null);

-- ORA-1400 can not insert NULL into...
insert into person (person_id) values (1);

UNIQUE:
prohibits duplicated values in column or set of columns;
NULL values are NOT considered duplicates;
table can have several UNIQUE constraints. create table person(
person_id number(10) constraint person_pid_un unique
,full_name varchar2(60)
,birth_date date default SYSDATE
,sex char(1)
,constraint person_name_date_un unique(full_name, birth_date)
);
-- add unique after creation
alter table person
add constraint ux_person_all_cols
unique (person_id,full_name,birth_date,sex); PRIMARY KEY (PK):
prohibits duplicated values in column or set of columns;
prohibits NULL values in that column(s);
one PK per table. create table person(
person_id number(10)
,full_name varchar2(60)
,birth_date date default SYSDATE
,sex char(1)
);
alter table person
add constraint pk_person
primary key (person_id); FOREIGN KEY (FK):
ensures that "child" value is present in "parent" table (or is null);
multiple FKs are allowed for table (and column);
FK requires primary key or unique constraint on the referenced set of columns on parent table. insert into person (person_id,full_name, birth_date) values (1, 'John Doe', TRUNC(SYSDATE));
-- ORA-00001 unique constraint violated
insert into person (person_id,full_name, birth_date) values (2, 'John Doe', TRUNC(SYSDATE));
insert into person (person_id) values (1);
-- ORA-00001unique constraint violated
insert into person (person_id) values (1); create table tasks(
person_id number(10)
,task_text varchar2(255));

alter table tasks
add constraint FK_tasks_person_id
foreign key (person_id)
references person(person_id); insert into tasks(person_id, task_text) values (1,'Valid task');
-- ORA-02293 parent key not found
insert into tasks(person_id, task_text) values (2,'No such person');
-- ORA-02292 child record found
delete from person where person_id = 1;
ON DELETE clause:
CASCADE - to delete all records from child table;
SET NULL - to update referencing column(s) with NULL; alter table tasks
add constraint FK_tasks_person_id
foreign key (person_id)
references person(person_id)
on delete cascade; Constraints description http://docs.oracle.com/cd/E11882_01/server.112/e25789/datainte.htm#CHDFHCIE Constraints syntax http://docs.oracle.com/cd/E11882_01/server.112/e26088/clauses002.htm#CJAEDFIB Useful links Practice Modify table COMPANIES created at the previous lesson:
make ID of company necessary and unique.
make company name necessary.
Enforce the following rule: if full name of company is present, it should be unique.
Enforce the following rule: date of company foundation should be JUST date, without time.
Enforce the following rule: country of residence code should be in UPPERCASE.

Modify EMPLOYEES table:
add necessary field COMPANY_ID.
ensure that value of this field is one of company id's from COMPANY table;
ensure that company can't be deleted if it has at least one employee. CHECK constraint:
condition that rows cannot break;
"cannot break" => condition is TRUE or is NULL;
several check constraints per table and even per column;
CHECK constraint can reference:
any column of the table;
deterministic SQL functions. alter table person
add constraint ck_person_sex
check (sex in ('M','F'));
insert into person(person_id, sex) values (1,null);
-- ORA-02290 check constraint violated
insert into person(person_id, sex) values (1,'Y');
Full transcript