Lecture 4

1. Lecture 4 Data Integrity: Primary and Foreign Keys

2. But first - table aliases • Table name prefixes prevent ambiguity in a query remember emp.deptno=dept.deptno • But they can be tedious to enter • So we can define temporary labels in the FROM clause and use them elsewhere in the query. These temporary labels are called table aliases

3. Example without aliases • To list information about all the employees in Chicago, enter: SELECT DEPT.DEPTNO, DNAME, LOC, ENAME, JOB FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO = 30;

4. Abbreviating a Table Name using aliases(Again to list information about all the employees in Chicago) Alias D.* retrieves all of the columns of the DEPT table SELECT D.*, ENAME, JOB FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = 30; Aliases E and D defined in the FROM clause The ‘join-condition’ defines the relationship between the two tables This produces the same results as the last query

6. Joining a table to itself • Aliasing allows you to join a table to itself as though it was two separate tables. • Useful when we want to join one row in a table to another row in the same table. EMP MANAGER WORKER

7. List employees whose salary exceeds their manager’s SELECT WORKER.ENAME, WORKER.SAL, MANAGER.ENAME, MANAGER.SAL FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO AND WORKER.SAL > MANAGER.SAL;

8. Worker Manager

9. Result is ENAME SAL ENAME SAL SCOTT 3000 JONES 2975 FORD 3000 JONES 2975 • SCOTT’s row is joined to JONES’s because SCOTT’s MGR is 7566 and JONES’s EMPNO is 7566

10. Topics • Referential Integrity • Table constraints: • Primary Keys • Foreign Keys • On delete cascade • Check • Default

11. Referential Integrity • Oracle 7 onwards allows the use of table constraints to enforce referential integrity.That is, the data in tables must be consistent. Imagine inserting an employee with department 60! The table constraints allow us to enforce that this cannot happen. • (reading chapter 5 Dowling)

12. Definition • Referential Integrity; rules in a database that ensure that data is valid and consistent. • Whereas individual constraints can be used to ensure the validity of data in a single column or a table, referential integrity constraints ensure the validity of data between tables. • N.Dowling Database Design and Management (1998)

13. Primary Key • Column(s) can have the primary key constraint placed on them, this has the following affect: • No duplicate values • Unique index created for column(s) • Cannot have NULL values

14. Primary Key • Examples from “our” tables are empno in myemp and deptno in mydept. • Only one Primary key is allowed per table but the key can consist of several columns

15. Foreign Key • References a primary key in another table, example deptno in myemp (foreign key) references deptno in mydept (primary key) and is thus used to join the tables. • Foreign Keys can also be specified in table constraints.

16. MYDEPT TABLE loc deptno dname primary key MYEMP TABLE empno ename job mgr hiredate deptno. primary key foreign key

17. Referential Integrity • To examine this let us look at an example • Currently (as there are no table constraints) we can insert an employee with department number 60.INSERT INTO myemp (empno, deptno)VALUES (1234, 60);

19. Table Constraints • We can use alter table to add constraints:ALTER TABLE mydept ADD CONSTRAINT tab_mydept_pk PRIMARY KEY (deptno); • This defines deptno as a primary key in table mydept. This means NULLs are no longer allowed and every value must be unique.

20. Table Constraints • In the previous example, we named the constraint tab_mydept_pk. The name is arbitrary but using a name like tab_mydept_pk tells us a lot about the constraint. • Oracle will use this name in any error messages it generates (thus helping us to identify the cause of the problem).

21. Table Constraints • Now we add the foreign key constraint to the myemp tableALTER TABLE myemp ADD CONSTRAINT tab_myemp_fk FOREIGN KEY (deptno) REFERENCES mydept (deptno);

22. Table Constraints • Now try to insert an employee with department number 60. • INSERT INTO myemp (empno, deptno)VALUES (1234, 60); • ERROR at line 1:ORA-02291: integrity constraint (SPB001.TAB_MYEMP_FK) violated - parent key not found

23. Table Constraints • We can also ensure that if a master or primary key row is deleted all the foreign or detail records get deleted with the ON DELETE CASADE constraint

24. Table Constraints • ALTER TABLE myemp ADD CONSTRAINT tab_emp_fk FOREIGN KEY (deptno) REFERENCES mydept (deptno) ON DELETE CASCADE;

25. Table Constraints • Checks can also be applied to data or defaults supplied with table constraints. These are used to define conditions that every row must fulfil. • CONSTRAINT salary_check CHECK (sal > 2000 and deptno =10) • This condition can ONLY refer to columns within the same table

26. Table Constraints • The default constraint defines a value for a column that will be assigned whenever a row is inserted that does not have an explicit value for the column • CREATE TABLE little_example(city VARCHAR2(20) DEFAULT (‘Stirling’), text VARCHAR2(5) DEFAULT (‘#####’));

27. Table Constraints • The unique constraint ensure that one or more columns are unique within that table. Unlike a primary key, NULLs are allowed. • CREATE TABLE another_example (city VARCHAR(20) UNIQUE);

28. Table Constraints • Can dropped, disabled and enabledALTER TABLE <table> DROP CONSTRAINT <constraint name>;ALTER TABLE <table> DISABLECONSTRAINT <constraint name>;ALTER TABLE <table> ENABLECONSTRAINT <constraint name>;

29. Table Constraints • These can be placed on a table when it is created.CREATE TABLE new_emp(a_empno NUMBER(4) , …,CONSTRAINT tab_new_emp_pk PRIMARY KEY(a_empno) ,…);

30. Summary • Referential Integrity • Table constraints: • Primary Keys • Foreign Keys • On delete cascade • Check • Default