incluyendo constraints

8/15/2019 Incluyendo Constraints

1/28

Including Constraints


2/28

-2

Objectives

After completing this lesson, you should be able todo the following:• escribe constraints• Create and maintain constraints


3/28

-!

"hat are Constraints#

• Constraints enforce rules at the table level$• Constraints prevent the deletion of a table if there are

dependencies$• %he following constraint types are valid:

– NOT NULL– UNIQUE– PRIMARY KEY

– FOREIGN KEY– CHECK


4/28

-&

Constraint 'uidelines

• (ame a constraint or the Oracle server generates aname by using the SYS_C n format$

• Create a constraint either: – At the same time as the table is created, or – After the table has been created

• efine a constraint at the column or table level$• )iew a constraint in the data dictionary$


5/28

-*

efining Constraints

CREATE TABLE [ schema .] table ( column datatype [DEFAULT expr ]

[ column_constraint ],...

[ table_constraint ][,...]);

CREATE TABLE e !"#$ee%( e !"#$ee_&' NUMBER( ), &*%+_ - e ARCHAR/(/0), ... 1#2_&' ARCHAR/(30) NOT NULL,

CONSTRAINT e !_e !_&'_!4PRIMARY KEY (EMPLOYEE_ID));


6/28

-+

efining Constraints

• Column constraint level

•

%able constraint level

column [CONSTRAINT constraint_name ] constraint_type ,

column [CONSTRAINT constraint_name ] constraint_type ,

column,... [CONSTRAINT constraint_name ] constraint_type ( column , ...),

column,... [CONSTRAINT constraint_name ] constraint_type ( column , ...),


7/28-

%he NOT NULL Constraint

nsures that null values are not permitted for thecolumn:

NOT NULL constraint.(o row can containa null value for this column$/

Absence of NOT NULL constraint.Any row can containnull for this column$/

NOT NULL constraint

0


8/28-1

CREATE TABLE e !"#$ee%( e !"#$ee_&' NUMBER( ),

"-%+_ - e ARCHAR/(/5) NOT NULL, %-"-*$ NUMBER(6,/), 7# &%% _!7+ NUMBER(/,/), 8&*e_'-+e DATE

CONSTRAINT e !_8&*e_'-+e_ NOT NULL,...

%he NOT NULL Constraint

Is defined at the column level:

ystem named

3ser named


9/28


10/28-67

%he UNIQUE Constraint

efined at either the table level or the column level:


"-%+_ - e ARCHAR/(/5) NOT NULL, e -&" ARCHAR/(/5), %-"-*$ NUMBER(6,/), 7# &%% _!7+ NUMBER(/,/), 8&*e_'-+e DATE NOT NULL,...

CONSTRAINT e !_e -&"_94 UNIQUE(e -&"));


11/28-66

%he PRIMARY KEY Constraint

DEPARTMENTS PRIMARY KEY

INSERT INTO(ot allowed.(ull value/

(ot allowed.*7 already e5ists/

0


12/28-62

CREATE TABLE 'e!-*+ e +%(

'e!-*+ e +_&' NUMBER(:), 'e!-*+ e +_ - e ARCHAR/( 0)CONSTRAINT 'e!+_ - e_ NOT NULL,

- -


13/28-6!

%he FOREIGN KEY ConstraintDEPARTMENTS

EMPLOYEESFOREIGNKEY

INSERT INTO (ot allowed.4 does note5ist/

AllowedAllowed

PRIMARYKEY

0

0


14/28-6&

%he FOREIGN KEY Constraint

efined at either the table level or the column level:


"-%+_ - e ARCHAR/(/5) NOT NULL, e -&" ARCHAR/(/5), %-"-*$ NUMBER(6,/), 7# &%% _!7+ NUMBER(/,/), 8&*e_'-+e DATE NOT NULL,...

'e!-*+ e +_&' NUMBER(:), CONSTRAINT e !_'e!+_ 4 FOREIGN KEY ('e!-*+ e +_&') REFERENCES 'e!-*+ e +%('e!-*+ e +_&'), CONSTRAINT e !_e -&"_94 UNIQUE(e -&"));


15/28-6*

FOREIGN KEY Constraint8eywords

• FOREIGN KEY : efines the column in the child tableat the table constraint level

• REFERENCES: Identifies the table and column in the

parent table• ON DELETE CASCADE : eletes the dependent rows

in the child table when a row in the parent table isdeleted$

•

ON DELETE SET NULL: Converts dependent foreign

9ey values to null


16/28-6+

%he CHECK Constraint

• efines a condition that each row must satisfy• %he following e5pressions are not allowed:

– eferences to CURR AL, NE=T AL , LE EL , and RO>NUM

pseudocolumns – Calls to SYSDATE , UID , USER , and USEREN functions – ;ueries that refer to other values in other rows

..., %-"-*$ NUMBER(/)

CONSTRAINT e !_%-"-*$_ &CHECK (%-"-*$ ? 0),...


17/28-6

Adding a Constraint ynta5

3se the ALTER TABLE statement to:• Add or drop a constraint, but not modify its

structure•

nable or disable constraints• Add a NOT NULL constraint by using the MODIFY

clause

ALTER TABLE table ADD [CONSTRAINT constraint ] type ( column );

ALTER TABLE table ADD [CONSTRAINT constraint ] type ( column );


18/28-61

Adding a Constraint

Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already e5ist asa valid employee in the EMPLOYEES table$

ALTER TABLE e !"#$ee% ADD CONSTRAINT e !_ - -


19/28-64

ropping a Constraint

• emove the manager constraint from theEMPLOYEES table$

• emove the PRIMARY KEY constraint on theDEPARTMENTS table and drop the associated

FOREIGN KEY constraint on theEMPLOYEES.DEPARTMENT_ID column$

ALTER TABLE e !"#$ee%

DROP CONSTRAINT e !_ - -


20/28-27

isabling Constraints

• 5ecute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity constraint$

• Apply the CASCADE option to disable dependent

integrity constraints$

ALTER TABLE e !"#$ee%DISABLE CONSTRAINT e !_e !_&'_!4 CASCADE;T-2"e -"+e*e'.T-2"e -"+e*e'.

ALTER TABLE e !"#$ee%DISABLE CONSTRAINT e !_e !_&'_!4 CASCADE;T-2"e -"+e*e'.T-2"e -"+e*e'.


21/28-26

nabling Constraints

• Activate an integrity constraint currently disabledin the table definition by using the ENABLE clause$

• A UNIQUE or PRIMARY KEY inde5 is automaticallycreated if you enable a UNIQUE 9ey or PRIMARYKEY constraint$

ALTER TABLE e !"#$ee%

ENABLE CONSTRAINT e !_e !_&'_!4;T-2"e -"+e*e'.T-2"e -"+e*e'.

ALTER TABLE e !"#$ee%ENABLE CONSTRAINT e !_e !_&'_!4;T-2"e -"+e*e'.T-2"e -"+e*e'.


22/28


23/28

-2!

Cascading Constraints

5ample:

ALTER TABLE +e%+3DROP (!4) CASCADE CONSTRAINTS;T-2"e -"+e*e'.T-2"e -"+e*e'.

ALTER TABLE +e%+3DROP (!4) CASCADE CONSTRAINTS;

T-2"e -"+e*e'.T-2"e -"+e*e'.

ALTER TABLE +e%+3DROP (!4, 4, 7#"3) CASCADE CONSTRAINTS;T-2"e -"+e*e'.T-2"e -"+e*e'.

ALTER TABLE +e%+3DROP (!4, 4, 7#"3) CASCADE CONSTRAINTS;T-2"e -"+e*e'.T-2"e -"+e*e'.


24/28

-2&

SELECT 7# %+*-& +_ - e, 7# %+*-& +_+$!e,

%e-*78_7# '&+FROM 9%e*_7# %+*-& +% >HERE +-2"e_ - e @ EMPLOYEES ;

)iewing Constraints

;uery the USER_CONSTRAINTS table to view allconstraint definitions and names$

0


25/28

-2*

SELECT 7# %+*-& +_ - e, 7#"9 _ - eFROM 9%e*_7# %_7#"9 %

>HERE +-2"e_ - e @ EMPLOYEES ;

)iewing the Columns Associated withConstraints

)iew the columns associated with the constraintnames in the USER_CONS_COLUMNS view$

0


26/28


27/28

-2

>ractice 67 Overview

%his practice covers the following topics:• Adding constraints to e5isting tables• Adding more columns to a table• isplaying information in data dictionary views


28/28

incluyendo constraints

Documents