1 / 19

Constraints

Constraints. CSED421 Database Systems Lab. 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

Download Presentation

Constraints

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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’;

More Related