Theory Practice Methodology of Relational Database Design and

Theory, Practice & Methodology of Relational Database Design and Programming Copyright © Ellis Cohen 2002 -2008 Relational State Constraints These slides are licensed under a Creative Commons Attribution-Non. Commercial-Share. Alike 2. 5 License. For more information on how you may use them, please see http: //www. openlineconsult. com/db © Ellis Cohen 2001 -2008

Overview of Lecture Relational State Constraints Check Constraints Non Null Constraints Simple Uniqueness Constraints Composite Primary Keys Composite Uniqueness Constraints Simple Foreign Key Constraints Deletion Integrity Composite Foreign Key Constraints Foreign Keys for Unique Attributes Defining and Changing Constraints © Ellis Cohen 2001 -2008 2

Relational State Constraints © Ellis Cohen 2001 -2008 3

State Constraints State Constraint Specifies an invariant property of the database state (something that must always be true and must be able to be checked at ANY ARBITRARY TIME!) Conceptual State Constraint A state constraint written informally in terms of the conceptual model (about entity classes, not tables!) Relational State Constraint A state constraint written formally, in SQL, in terms of the relational model. The SQL standard includes built-in support for relational state constraints. © Ellis Cohen 2001 -2008 4

Relational State Constraints Check Constraints (CHECK, NOT NULL) Ensures that each tuple in a table satisfies a condition Uniqueness Constraints (UNIQUE, PRIMARY KEY) Ensures that all values in a column are unique Foreign Key Constraints (REFERENCES) Ensures that a value in one column (or group of columns) matches a value in a referenced unique column (or group of columns) Relational State Assertions (ASSERTION) Ensures that an arbitrary condition (possibly involving multiple tables) remains satisfied © Ellis Cohen 2001 -2008 5

Examples of Relational Constraints CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) not null); CREATE TABLE Emps( empno number(4) primary key, ename varchar(30) not null, street varchar(40), city varchar(20), state char(2) check (state <> 'CA'), zip char(5), job varchar(9), deptno number(3) references Depts, mgr number(4) references Emps, sal number (11, 2), check (empno <> mgr) ) © Ellis Cohen 2001 -2008 6

Brief Textual Relational Model SHORTHAND LIST OF CONSTRAINTS • Parenthesized list of all attributes, with PK underlined • Followed by bulleted list of constraints Depts( deptno, dname ) • dname not null Emps( empno, ename, street, city, state, zip, deptno, mgr, sal ) • ename not null • check (state <> 'CA') • deptno references Depts • mgr references Emps • check (empno <> mgr) © Ellis Cohen 2001 -2008 7

Check Constraints © Ellis Cohen 2001 -2008 8

Checking a Tuple at a Time Check constraints can be used for enforcing any state constraint that can be checked by considering – a single tuple at a time – of a single table Examples – Every employee makes more than $600 – No employee has the job PEON or JESTER – Every DEPTMGR makes more than $2000 © Ellis Cohen 2001 -2008 9

Check as Column Constraint CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) ); CREATE TABLE Emps( Column empno number(4) primary key, constraint ename varchar(30), street varchar(40), city varchar(20), state char(2) check (state <> 'CA'), zip char(5), job varchar(9), deptno number(3) references Depts, mgr number(4) references Emps, sal number (11, 2) ) Associate constraint with the one column it names © Ellis Cohen 2001 -2008 10

Check as Table Constraint CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) ); CREATE TABLE Emps( empno number(4) primary key, ename varchar(30), street varchar(40), No city varchar(20), constraint state char(2), zip char(5), job varchar(9), Move deptno number(3) references Depts, constraint mgr number(4) references Emps, to end of sal number (11, 2), table check( state <> 'CA' ) ) definition Table constraints are not associated with a particular column. They are just placed at the end of the table definition © Ellis Cohen 2001 -2008 11

Required Table Constraints CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) ); CREATE TABLE Emps( empno number(4) primary key, Column ename varchar(30), constraint street varchar(40), city varchar(20), state char(2) check (state <> 'CA'), zip char(5), job varchar(9), deptno number(3) references Depts, mgr number(4) references Emps, sal number (11, 2), Table check (empno <> mgr) ); constraint A constraint that names multiple columns MUST be a table constraint! © Ellis Cohen 2001 -2008 12

WHERE vs CHECK Both WHERE clauses and CHECK constraints use boolean expressions – when applied to a tuple, the result can be TRUE or FALSE WHERE clauses are used in queries to filter the tuples returned WHERE state <> 'CA' CHECK constraint are used in table definitions. The DB makes sure that every tuple in the table satisfies the expression CHECK( state <> 'CA' ) © Ellis Cohen 2001 -2008 13

Upgrading Attributes to Classes Employee empno ename college. Name Employee empno ename College college. Name … It can be useful to upgrade attributes to classes. WHY? © Ellis Cohen 2001 -2008 14

Upgrading adds Referential Integrity Visual CONCEPTUAL Model (Crow Magnum) Employee empno ename college. Name Visual Relational Model (Relational Schema) Emps empno ename college. Name College empno ename college. Name … Also allows adding college-specific attributes without redundancy or a surrogate PK Colleges college. Name … The Colleges table can contain a list of all known colleges, with standard spellings for the college. Names. It ensures that an employee didn’t go to a "fake" college, and that all employees who went to the same college have their college. Name spelled the same way. How would this checking be done without Colleges? © Ellis Cohen 2001 -2008 15

Referential Integrity vs Checking CONCEPTUAL Model Employee empno ename college. Name + Conceptual State Constraint: college. Name must be one of Amherst, Bates, Bowdoin, … Relational Model Emps empno ename college. Name Emps empno primary key ename not null college. Name CHECK( college. Name IN ('Amherst', 'Bates', 'Bowdoin', … ) ) For large # of values, this approach is slower than using referential integrity. Also, making changes requires altering the CHECK constraint, which is more complicated than modifying the Colleges table © Ellis Cohen 2001 -2008 16

Complex Check Constraints Check constraints can be used to enforce any constraint that only involves checking a tuple at a time. What's the CHECK constraint for Every DEPTMGR makes $1500 or more (Assume that every employee has a job and a salary) Remember that every tuple in the table (not just those for DEPTMGR's) must satisfy the CHECK constraint © Ellis Cohen 2001 -2008 17

Check Constraint Solution Think about how you'd look at a specific tuple to see if the constraint is satisfied 1. Check if the job is DEPTMGR. If not, OK 2. Otherwise, check that sal ≥ 1500 CHECK( job != 'DEPTMGR' OR sal >= 1500 ) © Ellis Cohen 2001 -2008 18

Alternate Check Constraint Solution Which tuples should be excluded 1. Those in which the job = 'DEPTMGR' 2. And where sal < 1500 CHECK( NOT ( job = 'DEPTMGR' AND sal < 1500 ) ) Equivalent by De. Morgan's Law CHECK( job != 'DEPTMGR' OR sal >= 1500 ) © Ellis Cohen 2001 -2008 19

Not Null Constraints © Ellis Cohen 2001 -2008 20

Not Null Constraints in Relational Schemas CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) not null ) The database will not allow dname to be NULL Solid underline always indicates a primary key, which implies not null Depts deptno dname ! NOTE: Most non-reference attributes in actual applications will be non-null! We use an exclamation mark to denote non-null attributes (not needed for primary keys) © Ellis Cohen 2001 -2008 21

Relational Mapping of Required Attributes Visual CONCEPTUAL Model (Crow Magnum ER Diagram) Dept deptno dname ! Means that every department must have a name Visual RELATIONAL Model (Relational Schema) Depts deptno dname ! Means that the dname attribute is never NULL © Ellis Cohen 2001 -2008 22

NOT NULL dname varchar(20) not null IS JUST AN ABBREVIATION FOR dname varchar(20) check(dname IS NOT NULL) But the not null constraint is more efficient © Ellis Cohen 2001 -2008 23

Check Constraints and NULL The DB does not ensure that Check Constraints are TRUE The DB does ensure that Check Constraints are not FALSE Consider CHECK( sal > 100 ) If sal is 50 (sal > 100) If sal is 200 (sal > 100) If sal is NULL (i. e. (sal > 100) is FALSE NOT ALLOWED is TRUE OK UNKNOWN) is NULL (i. e. UNKNOWN) © Ellis Cohen 2001 -2008 OK 24

Disallowing NULLs Suppose we want to ensure that every employee's salary is greater than 100 and not NULL sal number(11, 2) CHECK( sal > 100 AND sal IS NOT null ) sal number(11, 2) NOT NULL CHECK( sal > 100 ) -- clearer and more efficient © Ellis Cohen 2001 -2008 25

Three-Valued Logic AND TRUE NULL FALSE x NOT x TRUE NULL FALSE TRUE FALSE NULL FALSE NULL FALSE FALSE TRUE OR TRUE NULL FALSE TRUE NULL TRUE NULL FALSE © Ellis Cohen 2001 -2008 These make perfect sense if you treat NULL as UNKNOWN 26

Check/NULL Constraint Problem Every dept manager makes 1500 or more CHECK( job != 'DEPTMGR' OR sal >= 1500 ) Suppose sal can be NULL, but not when the job is DEPTMGR © Ellis Cohen 2001 -2008 27

Check Constraints w NULL Check by looking at each employee tuple 1) Check if they are a DEPTMGR, if NOT, OK 2) If they are, then check that their sal is NOT NULL and that they make ≥ 1500 (assuming every employee has a job) CHECK( job != 'DEPTMGR' OR (sal IS NOT NULL AND sal >= 1500 ) ) Suppose job can be NULL: Revise the CHECK constraint © Ellis Cohen 2001 -2008 28

Checks with Nullable Attributes Every dept manager makes 1500 or more CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) ) CREAT TABLE Emps( empno number(4) primary key, ename varchar(30), job varchar(20), sal number(11, 2), addr varchar(80), deptno number(3) references Depts, mgr number(4) references Emps, CHECK( job != 'DEPTMGR' OR job IS NULL OR (sal ≥ 1500 AND sal IS NOT NULL) ) Note: both job and sal can be NULL © Ellis Cohen 2001 -2008 29

Simple Uniqueness Constraints © Ellis Cohen 2001 -2008 30

Uniqueness Constraints UNIQUE All the values in the column must be different PRIMARY KEY This identifies this column as the primary means of identifying a row. It also implies NOT NULL and UNIQUE CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) unique not null ) Any column (or group of columns) whose values are not null and unique is called a candidate key (so both deptno & dname are candidate keys) © Ellis Cohen 2001 -2008 31

Uniqueness Constraints in Relational Schemas Solid underline always indicates a primary key, which implies unique and not null Depts deptno dname ! We use the unipop symbol to denote uniqueness for fields which are not primary keys not null © Ellis Cohen 2001 -2008 32

Relational Mapping of Unique Attributes Visual CONCEPTUAL Model (Crow Magnum ER Diagram) Dept deptno dname ! {unique} Means that every department must have a name which is unique Visual RELATIONAL Model (Relational Schema) Depts deptno dname ! Means that the dname attribute is unique & never NULL © Ellis Cohen 2001 -2008 33

Unique Table Constraints CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) unique not null ) Equivalent CREATE TABLE Depts( deptno number(3), dname varchar(20) not null, primary key(deptno), unique(dname) ) Primary Key Table Constraint Unique Table Constraint © Ellis Cohen 2001 -2008 34

Composite Primary Keys © Ellis Cohen 2001 -2008 35

Composite Primary Keys Uniqueness & Primary Key Constraints can be – Column Constraints (for single-column constraints) – Table Constraints (required for composite constraints) Suppose each Dept is uniquely identified by – a divno (a division number), and – a deptno, which is only unique within each division Composite Primary Key Depts (divno, deptno, dname) • dname not null unique © Ellis Cohen 2001 -2008 36

Composite Primary Key Example composite primary key unique non-null divno deptno dname 1 10 NY SALES 1 30 NY ACCOUNTING 1 50 NY SUPPORT 2 10 MA SALES 2 42 MA ACCOUNTING 2 50 MA PARTYING 3 50 NJ MARKETING © Ellis Cohen 2001 -2008 37

SQL Composite Primary Keys CREATE TABLE Depts ( divno number(3) primary key, deptno number(3) primary key, dname varchar(20) not null unique ) Not legal, says that divno is a primary key and also deptno is a primary key But a table can only have one primary key CREATE TABLE Depts ( divno number(3), deptno number(3), dname varchar(20) not null unique, primary key( divno, deptno ) ) Composite primary keys must be written as table constraints © Ellis Cohen 2001 -2008 38

Composite Primary Key in Relational Schemas divno + deptno are both underlined, so together they indicate a composite primary key Depts divno deptno dname ! unique not null © Ellis Cohen 2001 -2008 39

Relational Mapping of Composite Primary Keys Visual CONCEPTUAL Model (Crow Magnum ER Diagram) Dept divno deptno dname ! {unique} divno + deptno are both underlined, so together they indicate a composite primary key Visual RELATIONAL Model (Relational Schema) Depts divno deptno dname ! divno + deptno are both underlined, so together they indicate a composite primary key © Ellis Cohen 2001 -2008 40

Composite Uniqueness Constraints © Ellis Cohen 2001 -2008 41

Composite Unique Constraint Example composite primary key non-null divno deptno dname 1 10 SALES 1 30 ACCOUNTING 1 50 SUPPORT 2 10 SALES 2 42 ACCOUNTING 2 50 PARTYING 3 50 MARKETING Combination of divno & dname is unique © Ellis Cohen 2001 -2008 42

Composite Unique Constraints Suppose the department name is also unique only within a division CREATE TABLE Depts ( divno number(3), deptno number(3), dname varchar(20) not null, primary key( divno, deptno ), unique( divno, dname ) ) Table constraint © Ellis Cohen 2001 -2008 43

Multiple Unique Constraints CREATE TABLE Depts ( divno number(3) unique, deptno number(3), dname varchar(20) not null unique, primary key( divno, deptno ) ) Is this legal? If not, why not? If so, what would it mean, and would it make sense? © Ellis Cohen 2001 -2008 44

Legal but Wrong CREATE TABLE Depts ( divno number(3) unique, deptno number(3), dname varchar(20) not null unique, primary key( divno, deptno ) ) Says that every dept has a unique value for divno AND also a unique value for dname If every dept has a unique value for divno, then every division only has at most a single dept! © Ellis Cohen 2001 -2008 45

Composite Uniqueness Constraints in Relational Schemas divno + deptno are both underlined, so together they indicate a composite primary key Depts divno deptno dname ! We use the multipop symbol to denote composite unique fields not null © Ellis Cohen 2001 -2008 46

Relational Mapping of Composite Unique Constraints Visual CONCEPTUAL Model (Crow Magnum ER Diagram) Dept divno deptno dname ! Composite uniqueness cannot be shown visually in the ER model. It must be stated explicitly! + A division number and department name together uniquely identify a department Visual RELATIONAL Model (Relational Schema) Depts divno deptno dname ! Composite uniqueness can be represented visually in the Relational Schema and corresponds to a built-in SQL construct © Ellis Cohen 2001 -2008 47

Simple Foreign Key Constraints © Ellis Cohen 2001 -2008 48

Mapping Simple Foreign Keys Visual CONCEPTUAL Model (Crow Magnum) Employee works for Dept deptno dname empno ename addr Visual RELATIONAL Model (Relational Schema) Emps Depts empno ename addr deptno dname © Ellis Cohen 2001 -2008 49

Foreign Key Column Constraint CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) ); CREATE TABLE Emps( empno number(4) primary key, ename varchar(30), addr varchar(80), deptno number(3) references Depts ) Foreign Key Column Constraint © Ellis Cohen 2001 -2008 50

Foreign Key Table Constraint CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) ); CREATE TABLE Emps( empno number(4) primary key, ename varchar(30), addr varchar(80), deptno number(3), foreign key(deptno) references Depts ) Foreign Key Table Constraint © Ellis Cohen 2001 -2008 51

Simple References VISUAL Relational Model (Relational Schema) Emps Depts empno ename addr deptno dname Brief TEXTUAL Relational Model (TRex) Depts( deptno, dname ) Emps( empno, ename, addr, deptno ) • deptno references Depts © Ellis Cohen 2001 -2008 Column Constraint (Simple Foreign Key Constraint) 52

Mandatory Child Participation? Visual CONCEPTUAL Model (Crow Magnum) Employee works for Dept deptno dname empno ename addr Visual RELATIONAL Model (Relational Schema) Emps Depts empno ename addr deptno dname How would mandatory child participation be represented in the relational model? © Ellis Cohen 2001 -2008 53

Mandatory Child Participation Visual CONCEPTUAL Model (Crow Magnum) Employee works for Dept deptno dname empno ename addr RELATIONAL Model Emps Depts empno ename addr deptno ! deptno dname Emps( empno, ename, addr, deptno ) • deptno not null references Depts © Ellis Cohen 2001 -2008 54

Mandatory Parent Participation? Visual CONCEPTUAL Model (Crow Magnum) Employee works for Dept deptno dname empno ename addr Visual RELATIONAL Model (Relational Schema) Emps empno ename addr deptno Depts deptno dname How would mandatory parent participation be represented in the relational model? © Ellis Cohen 2001 -2008 55

Mandatory Parent Participation Visual CONCEPTUAL Model (Crow Magnum) Employee works for Dept empno ename addr deptno dname RELATIONAL Model Emps Depts empno ename addr deptno dname There's no way to represent mandatory parent participation in the standard relational schema. We'll need to use state assertions instead! © Ellis Cohen 2001 -2008 56

Deletion Integrity Rules Note: These reflect other business rules (resulting in pre- and post-conditions) They are NOT state constraints © Ellis Cohen 2001 -2008 57

Side Effects of Deletion in 1: M Relationships Every 1: M relationship requires some thought about what happens when a parent instance is deleted. Child Entity Class Employee Parent Entity Class works for Dept Suppose a department has employees: Should we allow the department to be deleted? If so, what might we want to have happen to those employees when that department is deleted? © Ellis Cohen 2001 -2008 58

Deletion Integrity Rules Employee works for Dept Possible business rules involving Dept deletion (NOTE: None of these are state constraints!) • Restricted Deletion: Don't allow a department to be deleted if it has employees (pre-condition) • Deassignment: When a department is deleted, all employees in that department become unassigned • Lifetime Dependency (a. k. a. Cascading Delete): When a department is deleted, all employees in that department are deleted • Transfer: When a department is deleted, all employees in that department are moved to other departments • Special Transfer: When a department is removed, all employees in that department are moved to the Party Department Built-in SQL support © Ellis Cohen 2001 -2008 59

Restricted Deletion Employee Emps works for Depts empno ename addr deptno dname Emps( empno, ename, addr, deptno ) • deptno references Depts The default deletion integrity rule is Restricted Deletion (results in pre-condition for operations that delete departments) A department with employees CANNOT be deleted © Ellis Cohen 2001 -2008 60

Deassignment Employee works for Dept + Business Rule: When a dept is deleted, all employees in the dept become unassigned (results in post-conditions for operations that delete depts) Emps empno ename addr deptno Depts deptno dname Not represented in relational schema, only in textual representation & SQL Emps( empno, ename, addr, deptno ) • deptno references Depts on delete set null © Ellis Cohen 2001 -2008 61

Deassignment in SQL CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) ); CREATE TABLE Emps( empno number(4) primary key, ename varchar(30), addr varchar(40), deptno number(3) references Depts on delete set null ) © Ellis Cohen 2001 -2008 62

Lifetime Dependency (Cascading Delete) Employee works for Dept + Business Rule: When a dept is deleted, all employees in the dept are deleted as well Emps empno ename addr deptno Depts deptno dname Emps( empno, ename, addr, deptno ) • deptno references Depts on delete cascade © Ellis Cohen 2001 -2008 63

Mandatory Child Participation & Deletion Integrity Employee works for Dept deptno dname empno ename addr Suppose every employee MUST be assigned to a department. How does this affect the possibilities for deletion integrity rules? © Ellis Cohen 2001 -2008 64

Mandatory Child Participation Prevents Deassignment! Visual CONCEPTUAL Model (Crow Magnum) Employee works for Dept deptno dname empno ename addr RELATIONAL Model Emps Depts empno ename addr deptno ! deptno dname An employee's deptno can never be NULL, so Deassignment deptno not null references Depts on delete set null doesn't make any sense, since deleting a department, would deassign all employees in the dept, setting their deptno's to NULL! © Ellis Cohen 2001 -2008 65

Lifetime Dependency & Mandatory Child Participation Employee works for Dept + Business Rule: When a dept is deleted, all employees in the dept are deleted as well Emps empno ename addr deptno ! Depts deptno dname Emps( empno, ename, addr, deptno ) • deptno not null references Depts on delete cascade © Ellis Cohen 2001 -2008 66

Dependency + Particiption in SQL CREATE TABLE Depts( deptno number(3) primary key, dname varchar(20) ); CREATE TABLE Emps( empno number(4) primary key, ename varchar(30), addr varchar(80), deptno number(3) not null references Depts on delete cascade ) © Ellis Cohen 2001 -2008 67

Composite Foreign Key Constraints © Ellis Cohen 2001 -2008 68

Mapping Composite Keys Visual CONCEPTUAL Model: Crow Magnum ER Diagram Employee empno ename addr works for Dept composite PRIMARY KEY divno deptno dname Visual RELATIONAL Model: Relational Schema Depts Emps composite FOREIGN KEY empno ename addr divno deptno dname composite PRIMARY KEY Composite Reference © Ellis Cohen 2001 -2008 69

Composite Foreign Keys Emps Depts empno ename addr divno deptno dname Depts( divno, deptno, dname ) • primary key( divno, deptno ) Emps( empno, ename, addr, divno, deptno ) • foreign key( divno, deptno ) references Depts A table constraint MUST be used! Table Constraint (Composite Foreign Key Constraint) © Ellis Cohen 2001 -2008 70

Composite Foreign Keys in SQL Emps Depts empno ename addr divno deptno dname CREATE TABLE Depts ( divno number(3), deptno number(3), dname varchar(20), primary key( divno, deptno ) ) CREATE TABLE Emps ( empno number(4) primary key, ename varchar(30), addr varchar(80), divno number(3), deptno number(3), foreign key( divno, deptno ) references Depts ) © Ellis Cohen 2001 -2008 71

Foreign Key Rule * Relational Schemas use foreign key constraints. A foreign key constraint MUST refer to an attribute or group of attributes which are unique -generally, the relation's primary key © Ellis Cohen 2001 -2008 72

Simple vs Composite Foreign Keys Emp empno Dept deptno empno deptno Emps Emp empno Dept divno deptno empno divno deptno © Ellis Cohen 2001 -2008 Depts deptno … Depts divno deptno … 73

Composite Child Participation Emps Emp empno Dept deptno empno deptno ! Emps Emp empno Dept divno deptno empno divno ! deptno ! © Ellis Cohen 2001 -2008 Depts deptno … Depts divno deptno … 74

Composite Participation & Cascading Delete Emps Depts empno ename addr divno ! deptno ! divno deptno dname Depts( divno, deptno, dname ) • primary key( divno, deptno ) Emps( empno, ename, addr, divno, deptno ) • divno, deptno not null • foreign key( divno, deptno ) references Depts on delete cascade © Ellis Cohen 2001 -2008 75

Composite Natural Joins List the name & department name of each employee Emps empno ename addr divno deptno Depts divno deptno dname SELECT ename, dname FROM (Emps NATURAL JOIN Depts) SELECT ename, dname FROM Emps, Depts WHERE Emps. divno = Depts. divno AND Emps. deptno = Depts. deptno © Ellis Cohen 2001 -2008 76

Adding Surrogate Primary Keys Depts Emps empno ename addr divno deptno dname If you want to avoid composite references (this is an option, not a requirement), you can add a surrogate primary key, either in the conceptual model, or during mapping Surrogate primary key: A new attribute added to an entity class / relation and used in place of the original primary key. Depts Emps empno ename addr deptid composite UNIQUE constraint deptid divno ! deptno ! dname © Ellis Cohen 2001 -2008 Both deptid and (divno + deptno) are candidate keys. However, deptid is chosen to be the primary key 77

Relational Model with Surrogate Key Emps empno ename ! addr deptid Depts deptid divno ! deptno ! dname ! composite UNIQUE constraint CREATE TABLE Depts( deptid number(5) primary key, divno number(3) not null, deptno number(3) not null, dname varchar(20) unique not null, unique( divno, deptno ) ) CREATE TABLE Emps( empno number(4) primary key, ename varchar(30) not null, addr varchar(80), deptid number(5) references Depts ) © Ellis Cohen 2001 -2008 78

Foreign Keys for Unique Attributes © Ellis Cohen 2001 -2008 79

Foreign Keys for Unique Attributes Emps Depts empno ename addr deptno dname Foreign keys need not refer to primary keys. They can refer to any attribute which is unique. For example, if a department's dname was unique, then the 1: M relationship between employees and departments could be represented by using dname as the foreign key. Emps Depts empno ename addr dname deptno dname ! © Ellis Cohen 2001 -2008 80

Unique Foreign Key Constraint CREATE TABLE Depts( divno deptno number(3) primary key, dname varchar(20) not null unique ); CREATE TABLE Emps( empno number(4) primary key, ename varchar(30), addr varchar(80), dname number(3) references Depts(dname) ) © Ellis Cohen 2001 -2008 81

Foreign Keys + Unique Emps Depts empno ename addr dname deptno dname ! © Ellis Cohen 2001 -2008 82

Foreign Keys for Composite Unique Attributes Emps Depts empno ename addr divno deptno dname Composite foreign keys can refer to composite unique attributes Emps Depts empno ename addr divno dname divno deptno dname © Ellis Cohen 2001 -2008 83

Composite Unique Foreign Key Constraint CREATE TABLE Depts ( divno number(3), deptno number(3), dname varchar(20) not null, primary key( divno, deptno ), unique( divno, dname ) ) CREATE TABLE Emps( empno number(4) primary key, ename varchar(30), addr varchar(80), divno number(3), dname number(3), foreign key( divno, deptno ) references Depts( divno, dname ) ) © Ellis Cohen 2001 -2008 84

Foreign Keys + Unique Emps Depts empno ename addr divno dname divno deptno dname © Ellis Cohen 2001 -2008 85

Defining and Changing Constraints © Ellis Cohen 2001 -2008 86

Tables with Constraints CREATE TABLE Emps( empno number(4) primary key, ename varchar(30) not null, street varchar(40), city varchar(20), state char(2) check (state <> 'CA'), zip char(5), deptno number(3) references Depts, mgr number(4) references Emps, check (empno <> mgr) ); © Ellis Cohen 2001 -2008 87

Named Constraints CREATE TABLE Emps( empno number(4) CONSTRAINT Emps_pk primary key, ename varchar(30) CONSTRAINT Emps_ename not null, street varchar(40), city varchar(20), state char(2) CONSTRAINT Emps_CA check (state <> 'CA'), zip char(5), deptno number(3) CONSTRAINT Emps_fk_deptno references Depts, mgr number(4) CONSTRAINT Emps_fk_mgr references Emps, CONSTRAINT Emps_Self. Manage check (empno <> mgr) ); Why name? So the database can tell you the name of a constraint which is violated © Ellis Cohen 2001 -2008 88

Enabling & Disabling Constraints ALTER TABLE Emps MODIFY CONSTRAINT Emps_CA DISABLE – Disables Emps_CA constraint ALTER TABLE Emps MODIFY CONSTRAINT Emps_CA ENABLE – Enables Emps_CA constraint (Fails if entire table cannot be validated!) ALTER TABLE Emps_CA MODIFY CONSTRAINT asn_dates ENABLE NOVALIDATE – Enables Emps_CA constraint for newly set values; doesn't check current values © Ellis Cohen 2001 -2008 89

Changing Check Constraints ALTER TABLE Emps MODIFY CONSTRAINT College. Check CHECK( college. Name IN ('Amherst', 'Bates', 'Bowdoin', … , 'Parsons' ) ) Added In Oracle, you can’t even do this, you have to first drop College. Check, then add an expanded College. Check constraint © Ellis Cohen 2001 -2008 90

Changing Constraints ALTER TABLE Emps ADD CONSTRAINT Emps_empno CHECK (empno > 1) – Adds Emps_empno constraint ALTER TABLE Emps DROP CONSTRAINT Emps_CA – Deletes Emps_CA constraint © Ellis Cohen 2001 -2008 91

Constraint Metadata Information about constraints is maintained as part of metadata In Oracle, USER_CONSTRAINTS & USER_CON_COLUMNS contain constraint metadata © Ellis Cohen 2001 -2008 92