Constraints INFSY 445 Fall 2005 Constraints n Rules

Constraints INFSY 445 Fall 2005

Constraints n Rules to enforce: – Business rules, practices, & policies – Prevent data integrity problems n Example: – Customer Order placed on April 2, 2005 – Ship Date entered as March 31, 2005

Constraints Primary Key Uniquely identifies each record. Can’t be NULL Foreign Key 1 -to-M relationship, constraint added to the M table. Ensure data value is unique. NULL is allowed Unique Check Specified condition is true before data value is added to table

Constraints Not Null Column can’t contain a NULL value

Constraints n Name constraints – System default is SYS_Cnnnnn Primary Key Foreign Key Unique Check Not NULL _pk _fk _uk _ck _nn

Constraints n Create at column level or table level n Column Level constraint applies to column specified n Table Level constraint is separate from any column definitions – Used for any constraint except NOT NULL n Constraints are always enforced at table level

Constraints n Display all constraints SELECT constraint_name FROM user_constraints;

Foreign Key Constraint ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#) REFERENCES customers (customer#); REFERENCES refers to referential integrity Data must exist in the REFERENCES table and column

Foreign Key Constraint n Parent Table and Child Table n Can’t delete row from parent table if a matching entry is in child table n ON DELETE CASCADE – When used to create FK, able to delete record from parent table and all associated records from child table – Caution on using this feature!!!

Unique Constraint n Ensure 2 records do not have same value stored in the same column – Allows NULL values ALTER TABLE books ADD CONSTRAINT books_title_uk UNIQUE (title);

Check Constraint n Specific condition must be TRUE before a record is added to a table – May not reference functions: • SYSDATE • USER • ROWNUM ALTER TABLE orders ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);

Drop Constraints n Use DROP command to drop a constraint from a table ALTER TABLE acctmanager 2 DROP CONSTRAINT acctmanager 2_amname_nn;

Query Language

Query Language n Define database, change data, retrieve data n Data Definition Language (DDL) – Used to describe/build a database – CREATE, ALTER, DROP

Query Language n Data Manipulation Language (DML) – Used to maintain and query a database – DELETE, INSERT, UPDATE n Structured Query Language (SQL) – The American National Standards Institute’s (ANSI) recommended language for relational database definition and manipulation – SELECT

Four Questions to Create a Query n What output do you want to see? n What do you already know (or what constraints are given)? n What tables are involved? n How are the tables joined together?

Basic SQL SELECT n n SELECT FROM JOIN WHERE columns tables conditions criteria What do you want to see? What tables are involved? How are the tables joined? What are the constraints?

Simple Queries n SELECT statement SELECT item(s) FROM table(s) [WHERE condition] [GROUP BY column(s)] [HAVING condition] [ORDER BY column(s)];

Simple Queries SELECT * FROM STOCK; n Retrieves all columns and all rows.

Simple Queries SELECT STKCODE, STKFIRM, STKPRICE, STKQTY FROM STOCK WHERE NATCODE = ‘UK’; SELECT STKCODE, STKFIRM, STKPRICE, STKQTY FROM STOCK WHERE STKPRICE < 10. 00;

Simple Queries Comparison Operator = < > <= >= <> != Description Equal to Less than Greater than Less than or equal to Greater than or equal to Not equal to

Simple Queries SELECT STKCODE, STKFIRM, STKPRICE, STKQTY FROM STOCK WHERE STKPRICE BETWEEN 10. 00 AND 20. 00; SELECT STKFIRM, (STKPRICE * STKQTY) FROM STOCK WHERE NATCODE = ‘UK’;

Simple Queries n LIKE – supports pattern matching in a column of CHAR or VARCHAR – % sign is wild card for any number of characters – _ (underscore) is wild card for any single character – in large database, wildcard searches can be extremely slow SELECT STKFIRM FROM STOCK WHERE STKFIRM LIKE ‘F%’;

Simple Queries n IN – used with a list to specify a set of values – must be paired with a column name SELECT STKCODE, STKFIRM, STKPRICE, STKQTY FROM STOCK WHERE STKCODE IN (‘FC’, ‘AR’, ‘SLG’);

Simple Queries n Aggregate functions: – COUNT – SUM – AVG – MAX – MIN – Nulls in the column are ignored in the case of SUM, AVG, MAX, and MIN

Simple Queries SELECT COUNT(*) AS INVESTMENTS FROM STOCK; SELECT AVG(STKDIV/STKPRICE*100) AS AVGYIELD FROM STOCK; SELECT STKFIRM, SUM(STKPRICE * STKQTY) FROM STOCK WHERE NATCODE = ‘UK’;

Null Values n Null is a marker that specifies that the value for a particular column is unknown or not applicable for that field n Do not confuse null with blank or zero which are valid values SELECT STKCODE, STKFIRM, STKPRICE, STKQTY FROM STOCK WHERE STKDIV IS NULL;

Virtual Tables n View - contains selected columns n Doesn’t physically exist as stored data n Why? – simplify queries – restrict access to data

Virtual Tables CREATE VIEW Birthday_Table SELECT fname, lname, address, city, state, zip, DOB FROM PERSONNEL; SELECT * FROM Birthday_Table WHERE DOB = TO_DATE(‘ 01’, ‘MM’);

SQL Differences

INSERT – Add a row into a table – Two ways to insert values into a row – Forms are generally used for data entry in business; insert command often part of the form program code INSERT INTO JANE VALUES (‘Jane Kochanov, ‘ 123 Any Street’, Harrisburg, Pa, 101, TO_DATE(’ 02/01/2001’, ‘MM/DD/YYYY’);