Page 84 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 5: Integrity Constraints

2. a CHECK constant on name column of the client master so that the name is entered in Notes
upper case.
3. a CHECK constraint on the city column of the client_master so that only the cities
“BOMBAY”, “NEW DELHl “, “MAPRAS” and “CALCUTTA” are allowed.
CREATE TABLE client master
(client_no varchar2(6) CONSTRAINT ck_clientno
CHECK ( client_no like ‘C%’),
name varchar2(20) CONSTRAINT ck_cname
CHECK (name = upper(name»,
address I varchar2(30), address2 varchar2(30),
city varchar2( 15) CONSTRAINT ck _city
CHECK (city IN CNEWDELHI’, ‘BOMBAY’, ‘CALCUTTA’, ‘MADRAS’)),
state varchar2(l5), pin code number(6),
remarks varchar2(60), bal- due number(10,2));
When using CHECK constraints, consider the ANSI I ISO standard which states that a CHECK
constraint is violated only if the condition evaluates to False, True and unknown values do not
violate a check condition. Therefore, make sure that a CHECK constraint that you define actually
enforces the rule you need to enforce.

Example: Consider the following CHECK constraint for emp table:
CHECK ( sal > 0 or comm >= 0 )
At first glance, this rule may be interpreted as “do not allow a row in emp table unless the
employee’s salary is greater than 0 or the employee’s commission is greater than or equal to “0”.

Notes If a row is inserted with a null salary and a negative commission, the row does not
violate the CHECK constraint because the entire check condition is evaluated as unknown.
In this particular case, you can account for such violations by placing not null integrity
constraint on both the sal and comm columns.

Task Explain primary key constraints.

5.2 Authorization

After installing PL/SQL Developer all users can use all PL/SQL Developer functionality, within
the limits of the system privileges and object privileges that are granted to the Oracle user that
is connected to the database.

Example: If the Oracle user does not have the create user system privilege, the PL/SQL
Developer user can start the New user function in PL/SQL Developer, but will eventually get an
“ORA-01031, insufficient privileges” error message from Oracle.

LOVELY PROFESSIONAL UNIVERSITY 77