1 / 21

DML Stmts. & Creating Tables with Constraints

DML Stmts. & Creating Tables with Constraints. College of Business Administration Northern Arizona University. Steps in Creating a Relational Database.

ingo
Télécharger la présentation

DML Stmts. & Creating Tables with 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. DML Stmts. & Creating Tables with Constraints College of Business Administration Northern Arizona University

  2. Steps in Creating a Relational Database • Tables you create are actually added to a tablespace within a database. The database and tablespace control physical storage of data (the internal model of the database) and are normally controlled by the database administrator. Management of these structures is beyond the scope of this course. • CREATE DATABASE STATEMENT - Allocates space for the database as a whole, has specifications to indicate disk space usage. • CREATE TABLESPACE STATEMENT - Each database may have several tablespaces - These are physical file spaces affecting disk storage. • CREATE TABLE STATEMENT - Tables are stored in one of these physical tablespaces. If no table space has been specified for a table it is stored in the default tablespace.

  3. Creating Database Tables • Each table that is created is normally based upon a table structure in an ER or Relation diagram, e.g. STUDENT(S_ID, S_NAME, MAJOR, HS_GPA, PROF_NAME) CREATE TABLE STUDENT (S_ID NUMBER (5), S_NAME VARCHAR2 (20), Major CHAR (3), HS_GPA NUMBER (4,2), PROF_NAME VARCHAR2 (6) );

  4. ORACLE Datatypes

  5. COLUMN CONSTRAINTS IN THE CREATE TABLE STATEMENT Common Types of Constraints Are: PRIMARY KEY- This column is the PK of the table NOT NULL - Each row must have a value for this column CHECK (condition)- A domain restriction on values of this column is to be checked as rows are added REFERENCES table-name- This column is a foreign key referencing the primary key column of the listed table DEFAULT value|system_variable- sets a default value to be used if a value for this row is not specified when a row is inserted. E.G ORDER_DATE DEFAULT SYSDATE CREATE TABLE CREDIT_CARD (cred_cd_no varchar2(10) PRIMARY KEY, cc_exp_date date NOT NULL, Card_type varchar2(4) NOT NULL CHECK (Card_type in ('AMEX', 'VISA', 'MC')), Cust_no number(3) REFERENCES CUSTOMER;

  6. What the Constraints mean in the Sample Table CREATE TABLE CREDIT_CARD (cred_cd_no varchar2(10) PRIMARY KEY, cc_exp_date date NOT NULL, Card_Type varchar2(4) NOT NULL CHECK (Card_type in ('AMEX', 'VISA', 'MC')), Cust_No number(3) REFERENCES CUSTOMER; • Based upon this statement: • Rows will not be allowed in the student table if they have no value for • cred_cd_no or if the value for cred_cd_no duplicates the value of cred_cd_no foranother row. • Rows will not be allowed in the table unless they have an entry for cc_exp_date. • Rows will not be allowed in the table unless they have an entry for card_type. • Only the values AMEX, VISA or MC will be allowed for the Card_Type column - • If any other values are entered, the new row is not accepted. • The Cust_No column is a foreign key referencing the CUSTOMER table. • rows in the CREDIT_CARD table are not accepted unless • the value of Cust_No matches a value of CUST_No from the CUSTOMER • table (where it is the Primary Key) or • The Cust_No is null

  7. Foreign Keys - Optional Vs. Mandatory Relationships CREATE TABLE CREDIT_CARD (cred_cd_no varchar2(10) PRIMARY KEY, cc_exp_date date NOT NULL, Card_type varchar2(4) NOT NULL CHECK (Card_type in ('AMEX', 'VISA', 'MC')), Cust_no number(3) REFERENCES CUSTOMER, NOT NULL CUSTOMER CREDIT_CARD As written this create table statement assumes that the association of CREDIT_CARD to CUSTOMER is optional. Rows in the CREDIT_CARD table can be added which do not have a value for CUSTOMER. To make the association mandatory a NOT NULL constraint would be used on the Cust_no column. (Also, the CUSTOMER table would have to be created and its Primary Key specified before the foreign key in CREDIT_CARD could be added)

  8. THE ON DELETE CLAUSE CREATE TABLE CREDIT_CARD (cred_cd_no varchar2(10) PRIMARY KEY, cc_exp_date date NOT NULL, Card_type varchar2(4) NOT NULL CHECK (Card_type in ('AMEX', 'VISA', 'MC')), Cust_no number(3) REFERENCES CUSTOMER ON DELETE CASCADE); In ORACLE, by default, when a user attempts to delete a row of a table on the one side of a relationship which has related records on the many side, this action is prohibited or RESTRICTED. In our example, a record for a Customer who has related Credit_Card could not be deleted. The Clause ON DELETE CASCADE overrides this default. When it is specified the one side record may be deleted and any related records on the many side are automatically deleted as well. With the specification shown on this slide, deleting the Customer whose cust_no is 110 would cause all CREDIT_CARDs with that foreign key value to be deleted as well.

  9. Naming Constraints The column and table constraints of the create table statements are stored in system tables and can be directly manipulated. To make it easier to manage these constraints, you may assign a name to any constraint that is created. E.G. (cred_cd_no VARCHAR2(10) PRIMARY KEY could be written as: (cred_cd_no VARCHAR2(10) CONSTRAINT c_cd_pk PRIMARY KEY

  10. ADDING TABLE CONSTRAINTS When the Primary key or a Foreign key in a table involves more than one column, this constraint cannot be linked to any single column and is entered as a table constraint. PRODUCT Prod_Code Prod_Descrip . . . For Example, suppose the Item_Sold Table is based on an associative entity linking SALE and PRODUCT: Where Ord_No is a foreign key linking to a Sale table and Prod_Code is a foreign key linking to a PRODUCT TABLE SALE Ord_No Ord_Date . . . ITEM_SOLD Ord_No (FK) Prod_Code (FK) Item_Qty_Ord Item_Amt_Billed Item_Status CREATE TABLE ITEM_SOLD (Ord_no number(4) constraint i_s_fk REFERENCES SALE, prod_code char(4) constraint i_p_fk REFERENCES PRODUCT, item_qty_ord number(6) NOT NULL, item_amt_billed number(9,2), item_status char(1) constraint it_stat_ck CHECK (item_status in ('N','F','B')), CONSTRAINT i_pk PRIMARY KEY (ord_no, prod_code) ); The create table statement for this ITEM_SOLD table would look like this

  11. Explanation of Table Constraints Example CREATE TABLE ITEM_SOLD (Ord_no number(4) constraint i_s_fk REFERENCES SALE, prod_code char(4) constraint i_p_fk REFERENCES PRODUCT, item_qty_ord number(6) NOT NULL, item_amt_billed number(9,2), item_status char(1) constraint it_stat_ck CHECK (item_status in ('N','F','B')), CONSTRAINT i_pk PRIMARY KEY (ord_no, prod_code) ); Here the primary key involves multiple columns and is thus specified as a table constraint. We are assuming that the SALE and PRODUCT tables have been created and have Ord_no and Prod_code as their respective primary keys. ITEM_SOLD is the association table for the SALE and PRODUCT tables. This Slide also shows the specification of constraint names for the various constraints. It is important to use a consistent naming scheme when specifying constraint names. (We will name our CHECK constraints only).

  12. The ALTER TABLE Statement ALTER TABLE Table-Name [ADD column or constraint] [MODIFY column] Examples: ALTER TABLE CUSTOMER ADD E_mail_addr VAR_CHAR2 (50); ALTER TABLE SALES_ORDER ADD FOREIGN KEY (Emp_Id) REFERENCES Employee; ALTER TABLE CUSTOMER MODIFY PHONE_NO CHAR (10); Adds a new column with specified characteristics You can add a foreign key constraint after initially creating the table This would change the Phone_No column’s specifications to those shown

  13. Data Manipulation Language Statements • Data Manipulation Language (DML) Statements consist of statements which: • Add new rows to a table (INSERT statement) • Modify data in one or more rows of a table (UPDATE statement) • Remove rows from a table (DELETE statement)

  14. Modifying Data the UPDATE Statement • The UPDATE statement if used to modify the data in one or more rows of a table. • Its Basic Syntax is: UPDATE table_name SET column_name = value, [ column_name = value, . . . ] [ WHERE condition] • For example: UPDATE PRODUCT SET UNIT_PRICE = 79.95 WHERE Prod_Code = ‘CPD2’; • This update statement sets a new unit_price for one row, the one whose prod_code is CPD2. It is a very typical type of update where you want to set a value in a single identified row. The primary key field is used in the where clause to identify the row you want.

  15. Additional Update Examples • The following statement would add 10 percent to the unit prices of all products. UPDATE PRODUCT SET Unit_Price = Unit_Price*1.1; • This can allow you to quickly update an entire table if some uniform rule is to be used to update all rows. It can also allow you to quickly update all rows when you only meant to update one. Be very careful to add any appropriate where clause before you execute an update command.

  16. Updates and Integrity Constraints • An update which causes an integrity constraint to be violated will not be allowed. • This can occur if: • a foreign key is changed to a value that has no matching primary key in the referenced table • a primary key is changed to a value that duplicates another primary key value • a primary key that is referenced by a foreign key in another table is changed • a check constraint is violated by the updated value. • When a constraint is violated, an error message is generated and the update is not executed.

  17. Removing Rows From a Table - The DELETE Statement • The DELETE Statement is used to remove one or more rows from a table. Its basic syntax is: DELETE [FROM] table_name [WHERE condition]; • The from is optional but is generally used for readability. • To delete flight number 101, I would type the command: DELETE FROM PRODUCT WHERE Prod_Code = ‘ADC8’; • NOTE: If I forget the where clause all rows of the table are deleted.

  18. Integrity Constraints and the DELETE Command • A delete command that violates an integrity constraint is not allowed. If this occurs an error message is displayed and the rows are not deleted. • A delete statement will violate integrity constraints if: • the deleted row contains a primary key value which is referenced by a foreign key constraint on related table, • unless the foreign key constraint has an ON DELETE CASCADE clause. • If there is an ON DELETE CASCADE clause, deleting a row in the parent will delete not only that row, but all rows of the child table that were associated with it.

  19. The Process of Creating Tables • When creating several Tables it is Customary to use a script file and include a DROP TABLE statement before each CREATE TABLE statement. • This allows you to run the script repeatedly, correcting any errors, until all tables are created properly. • You should have noticed that FOREIGN Key constraints can only be placed on a table if the REFERENCED table already exists and has its PRIMARY KEY constraint in force. • Thus, the order in which tables are created is important and failure to create a parent table successfully will cause a table which REFERENCES it in its FOREIGN KEY to fail. • An alternative is to wait until all of the tables have been created an then use ALTER TABLE commands to add FOREIGN KEYs. • Certain characteristics and constraints cannot be changed after data has been entered in a table (For example, I may not be allowed to shorten the length of column or change its datatype).

  20. DML and Transactions – COMMIT & ROLLBACK • Individual DML statements are not automatically made permanent in the database as they are executed. • If you successfully insert a record in a table, you will immediately see that record if you query the table. • However, other user sessions will not see this change until the record you inserted has been committed. • Your changes are committed if you execute a COMMIT command (COMMIT;). • If your session ends abnormally, lost connection to the server, etc., all changes since the last commit was issued (or all changes in the current session if a commit has never been issued) will be lost (rolled back) • To explicitly undo erroneous changes, you can issue a ROLLBACK statement (ROLLBACK;)

  21. Dropping, Renaming, or Truncating Tables • To Drop a table you type DROP TABLE table_name Dropping a table removes its data, if it has data and removes its table definition. To Truncate a table you type TRUNCATE TABLE table_name Truncating a table removes all its data and recovers the space used by the data for reuse. It does not delete the definition of the table, thae table structure still exists and new data can be added.

More Related