The check clause check(P),where Pis a predicate Example:Declare branch name as the primary key for branch and ensure that the values of assets are non- negative. create table branch (branch name char(15), branch city char(30), assets integer, primary key (branch_name), check(assets >=0)) Database System Concepts,5th Edition,Oct 5.2006 4.12 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.12 ©Silberschatz, Korth and Sudarshan The check clause check (P ), where P is a predicate Example: Declare branch_name as the primary key for branch and ensure that the values of assets are nonnegative. create table branch (branch_name char(15), branch_city char(30), assets integer, primary key (branch_name), check (assets >= 0))
The check clause(Cont.) The check clause in SQL-92 permits domains to be restricted: Use check clause to ensure that an hourly_wage domain allows only values greater than a specified value. create domain hourly_wage numeric(5,2) constraint value_test check(value >4.00) The domain has a constraint that ensures that the hourly_wage is greater than 4.00 The clause constraint value_test is optional;useful to indicate which constraint an update violated. Database System Concepts,5th Edition,Oct 5.2006 4.13 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.13 ©Silberschatz, Korth and Sudarshan The check clause (Cont.) The check clause in SQL-92 permits domains to be restricted: Use check clause to ensure that an hourly_wage domain allows only values greater than a specified value. create domain hourly_wage numeric(5,2) constraint value_test check(value > = 4.00) The domain has a constraint that ensures that the hourly_wage is greater than 4.00 The clause constraint value_test is optional; useful to indicate which constraint an update violated
Referential Integrity Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. Example:If "Perryridge"is a branch name appearing in one of the tuples in the account relation,then there exists a tuple in the branch relation for branch "Perryridge". Primary and candidate keys and foreign keys can be specified as part of the SQL create table statement: The primary key clause lists attributes that comprise the primary key The unique key clause lists attributes that comprise a candidate key. The foreign key clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key.By default,a foreign key references the primary key attributes of the referenced table. Database System Concepts,5th Edition,Oct 5.2006 4.14 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.14 ©Silberschatz, Korth and Sudarshan Referential Integrity Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. Example: If “Perryridge” is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch “Perryridge”. Primary and candidate keys and foreign keys can be specified as part of the SQL create table statement: The primary key clause lists attributes that comprise the primary key. The unique key clause lists attributes that comprise a candidate key. The foreign key clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key. By default, a foreign key references the primary key attributes of the referenced table
Referential Integrity in SQL-Example create table customer (customer name char(20), customer street char(30), customer_city char(30), primary key(customer_name ) create table branch (branch name char(15), branch city char(30), assets numeric(12,2), primary key(branch_name ) Database System Concepts,5th Edition,Oct 5.2006 4.15 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.15 ©Silberschatz, Korth and Sudarshan Referential Integrity in SQL – Example create table customer (customer_name char(20), customer_street char(30), customer_city char(30), primary key (customer_name )) create table branch (branch_name char(15), branch_city char(30), assets numeric(12,2), primary key (branch_name ))
Referential Integrity in SQL-Example (Cont.) create table account (account number char(10), branch name char(15), balance integer, primary key (account_number), foreign key (branch_name)references branch create table depositor (customer name char(20), account_number char(10), primary key (customer_name,account_number), foreign key (account_number )references account, foreign key (customer_name )references customer Database System Concepts,5th Edition,Oct 5.2006 4.16 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.16 ©Silberschatz, Korth and Sudarshan Referential Integrity in SQL – Example (Cont.) create table account (account_number char(10), branch_name char(15), balance integer, primary key (account_number), foreign key (branch_name) references branch ) create table depositor (customer_name char(20), account_number char(10), primary key (customer_name, account_number), foreign key (account_number ) references account, foreign key (customer_name ) references customer )