10-4. Create the Department and Employee tables, as per the examples earlier in

Question

10-4. Create the Department and Employee tables, as per the examples earlier in the chapter,

with all the constraints (PRIMARY KEYs, referential and UNIQUE constraints, CHECK constraints). You can add the constraints at create time or you can use ALTER TABLE to add the constraints. Load the Department table first with departments 1, 2, and 3. Then load the Employee table.
Note: before doing the next few exercises, it is prudent to create two tables, called Deptbak

and Empbak, to contain the data you load, this is because you will be deleting, inserting, dropping,

recreating, and so on. You can create Deptbak and Empbak tables with the data we have been using with a command like this:
CREATE TABLE Deptbak

AS SELECT *

FROM Dept;

Then, when you have added, deleted, updated, and so on and you want the original table from

the start of this problem, you simply run the following commands:
DROP TABLE Deptbak;
CREATE TABLE Deptbak

AS SELECT *

FROM Dept;

a. Create a violation of insertion integrity by adding an employee to a nonexistent

department. What happens?

b. Create an UPDATE violation by trying to change”

(i) an existing employee to a nonexistent department,

(ii) a referenced department number.

c. Try to delete a department for which there is an employee. What happens? What happens

if you try to DELETE a department to which no employee has yet been assigned?

d. Redo this entire experiment (starting with Exercise 10-4a), except that this time create

the Employee table with the ON DELETE CASCADE. View the table definition of the Employee

table.

Explanation / Answer

CREATE TABLE Department(deptnoNUMBER(3),deptnameVARCHAR2(20),CONSTRAINT deptno_pk PRIMARY KEY (deptno));

Table created.

CREATE TABLE Employee(empnoNUMBER(4) CONSTRAINT empno_pk PRIMARY KEY,empnameVARCHAR2(20),deptNUMBER(3) CONSTRAINT dept_fk REFERENCES Department(deptno));

Table created

a.INSERT INTO Employee VALUES (200,'Debanjan',10);

INSERT INTO Employee VALUES*ERROR at line 1:ORA-02291: integrity constraint (MMD24.DEPT_FK) violated - parent key not found

b.i. UPDATE Employee SET dept = 46 WHERE empno = 100;

UPDATE Employee *ERROR at line 1:ORA-02291: integrity constraint (MMD24.DEPT_FK) violated - parent keynot found

ii.UPDATE Department SET deptno = 4;

UPDATE Department*ERROR at line 1:ORA-00001: unique constraint (MMD24.DEPTNO_PK) violated

c.DELETE FROM Department WHERE deptno = '46';

1 row deleted.

d. CREATE TABLE Empl(empnoNUMBER(3) CONSTRAINT empno_pk PRIMARY KEY,empnameVARCHAR2(20),deptNUMBER(3) REFERENCES department(deptno) ON DELETECASCADE);

Table created

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.