PROGRAMMING: Section 10

DEFINING NOT NULL AND UNIQUE CONSTRAINTS

A table can have more than one UNIQUE key constraint. True or False?
True


Which of the following is not a valid Oracle constraint type?
EXTERNAL KEY


A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?
False


A unique key constraint can only be defined on a not null column. True or False?
False


If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False?
True


A table must have at least one not null constraint and one unique constraint. True or False? 
False


PRIMARY KEY, FOREIGN KEY, AND CHECK CONSTRAINTS

Foreign Key Constraints are also known as:

Referential Integrity Constraints

A Primary Key that is made up of more than one column is called a:
Composite Primary Key
The employees table contains a foreign key column department_id that references the id column in the departments table. Which of the following constraint modifiers will NOT allow the deletion of id values in the department table?
Neither A nor B

Which line of the following code will cause an error:
1. CREATE TABLEMclients
2. (client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number),
3. first_name VARCHAR2(14),
4. last_name VARCHAR2(13),
5. hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
6. department_id VARCHAR(3),
7. CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));         
Line 2

The table that contains the Primary Key in a Foreign Key Constraint is known as:
Parent Table
If a Primary Key is made up of more than one column, one of the columns can be null. True or False?
False
An example of adding a check constraint to limit the salary that an employee can earn is:
ALTER TABLE employees ADD CONSTRAINT emp_salary_ck CHECK (salary < 100000)
A composite primary key may only be defined at the table level. True or False?
True


To automatically delete rows in a child table when a parent record is deleted use:

ON DELETE CASCADE


The main reason that constraints are added to a table is:

Constraints ensure data integrity


Which of the following pieces of code will NOT successfully create a foreign key in the CDS table that references the SONGS table

None of the above


The number of check constraints that can be defined on a column is:

There is no limit
MANAGING CONSTRAINTS
What mechamisn does Oracle use in the background to enforce uniqueness in Primary and Unique key constraints?
Unique indexes are created in the background by Oracle when Primary and Unique constraints are created or enabled
Once constraints have been created on a table you will have to live with them as they are unless you drop and re-create the table. True or False?
False

You can drop a column in a table with a simple ALTER TABLE DROP COLUMN statement, even if the column is referenced in a constraint. True or False?
False
All of a user's constraints can be viewed in the Oracle Data Dictionary view called:
USER_CONSTRAINTS
The command to 'switch off' a constraint is:
ALTER TABLE DISABLE CONSTRAINT