Constraints

1. Constraints CSED421Database Systems Lab

2. Constraints • To define an integrity constraint (IC) • IC : condition that must be true for any instance of the database • 5 types of constraints • NOT NULL constraints • UNIQUE constraints • PRIMARY KEY constraints • FOREIGN KEY constraints • CHECK constraints

3. Specification • As part of the table definition e.g., CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, price NUMBER NOT NULL, … ); CREATE TABLE <table_name> ( <column_name> <type> [ CONSTRAINT <constraint_name> ] <condition>, …);

4. Specification (cont.) • Alteration after the table definition e.g., ALTER TABLE orders ADD PRIMARY KEY (order_id); e.g., ALTER TABLE orders MODIFY ( price NOT NULL ); ALTER TABLE <table_name>ADD[DROP] [ CONSTRAINT <constraint_name> ] <condition>; ALTER TABLE <table_name>MODIFY ( <column_name> [ CONSTRAINT <constraint_name> ] <condition>);

5. NOT NULL Constraint • Prohibits a database value from being null • null : either unknown or not applicable • To satisfy a NOT NULL constraint, every row in the table must contain a value for the column CREATE TABLE student ( sid NUMBER NOT NULL, … … …); ALTER TALBE student MODIFY sidNOT NULL;

6. UNIQUE Constraint • Prohibits multiple rows from having the same value in the same column or combination of columns. • But allows some values to be null. CREATE TABLE promotions ( promo_id NUMBER UNIQUE, … … …); ALTER TABLE promotions ADD UNIQUE (promo_id); ALTER TABLE promotions MODIFY promo_id UNIQUE;

7. PRIMARY KEY Constraint • Prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null. • Combines a NOT NULL constraint and a unique constraint in a single declaration. CREATE TABLE locations ( location_id NUMBER PRIMARY KEY, … … …); ALTER TABLE locations ADD PRIMARY KEY (location_id); ALTER TABLE locations MODIFY location_idPRIMARY KEY;

8. PRIMARY KEY Constraint (cont.) CREATE TABLE locations ( name CHAR(20), addr CHAR(20), … …, CONSTRAINT name_addr PRIMARY KEY (name, addr)); ALTER TABLE locations ADD CONSTRAINTname_addrPRIMARY KEY (name, addr)

9. FOREIGN KEY Constraint • Requires values in one table to match values in another table. • Also called referential integrity constraint CREATE TABLE enrolled ( sid CHAR(20), … … … ,CONSTRAINT fk_enrolledFOREIGN KEY (sid) REFERENCES students(sid)); CREATE TABLE enrolled ( sid CHAR(20) REFERENCES students(sid) … … …); ALTER TABLE enrolled ADD CONSTRAINT fk_enrolled FOREIGN KEY (sid)REFERENCES students(sid); ALTER TABLE enrolled MODIFY sid REFERENCES students(sid);

10. FOREIGN KEY Constraint • What if a student tuple is deleted… • NO ACTION (default) • CASCADE / SET NULL • CREATE TABLE Enrolled( … … … , FOREIGN KEY (sid) REFERENCES Students(sid)ON DELETE CASCADE / SET NULL ); Enrolled Students

11. CHECK Constraint • Requires a value in the database to comply with a specified condition CREATE TABLE emp ( … … … , age NUMBER CHECK (age > 0)); ALTER TABLE emp ADD CHECK (age > 0); ALTER TABLE emp MODIFY age CHECK (age > 0);

12. Practice 1 • Bank_Acct 와 Customer_Info 테이블을 생성 Customer_Info : name VARCHAR2(20), addr VARCHAR(60), phone CHAR(10), email VARCHAR2(40) Bank_Acct : acct CHAR(4), name VARCHAR2(20), addr VARCHAR2(60), balance REAL Customer_Info’s primary key is (name, addr) Bank_Acct’s primary key is acct Bank_Acct’s (name, addr) references Customer_Info’s (name, addr)

13. Practice 1 Customer_Info Bank_Acct • 두 테이블에 대한 위의 인스턴스에 대해 오류(IC)를 찾기 • 두 테이블에 각각의 레코드를 IC를 만족하도록 필드 값을 수정하여 추가하기

14. Practice 1 • 아래의 SQL을 수행결과를 확인 SELECT B.name, C.emailFROM Bank_Acct B, Customer_Info CWHERE B.name = C.name AND B.addr = C.addr AND B.balance > 1000.00

15. Practice 2 • Simulate violation of integrity constraint • @practice2 스크립트를 통해 테이블 생성. • Practice2 스크립트는 Enrolled 와 Students 테이블을 생성하고, Enrolled 의 Students 를 참조하는 foreign key constraint를 추가한다. Enrolled Students

16. Practice 2 • practice2.sql CREATE TABLE Students ( sid CHAR(20) PRIMARY KEY, name CHAR(20), login CHAR(10), age INTEGER, gpa REAL ); CREATE TABLE Enrolled ( sid CHAR(20), cid CHAR(20), grade CHAR(2),CONSTRAINT fk_enrolled FOREIGN KEY (sid) REFERENCES Students (sid) ); INSERT INTO Students VALUES ('53666', 'Jones', 'jones@cs', 18, 3.4); INSERT INTO Students VALUES ('53688', 'Smith', 'smith@eecs', 18, 3.2); INSERT INTO Students VALUES ('53650', 'Smith', 'smith@math', 19, 3.8); INSERT INTO Enrolled VALUES ('53666', 'Carnatic101', 'C'); INSERT INTO Enrolled VALUES ('53666', 'Reggae203', 'B'); INSERT INTO Enrolled VALUES ('53650', 'Topology112', 'A'); INSERT INTO Enrolled VALUES ('53666', 'History105', 'B');

17. Practice 2 • 각각의 referential integrity enforcement에 따라 Students 의 레코드를 삭제한다.(NO ACTION, CASCADE) • 결과 메시지 혹은 삭제 후 테이블의 전체 내용들을 확인한다.

18. Practice 2 • TAs want to see query results… • NO ACTION enforcement • CASCADE

19. Constraint Check • Confirm constraints on the tables. • Use a system table, user_constraints. • SQL> SELECT table_name, constraint_name, constraint_type • FROM user_constraints • SQL> SELECT table_name, constraint_name, constraint_type • FROM user_constraints • WHERE table_name = ‘your table name’;