Table Creation and Management
- Identify the table name and structure
- Commands used to create or modify database tables are called data definition language(DDL commands
- A Database Object is a defined self contained structure also called database tables
| Creating Tables | Description |
|---|---|
| CREATE TABLE | Creates a new table in the database. The user names the columns and identifies the type of data to be stored. To view a table, use the SQL*PLUS
command DESCRIBE. |
| Modifying Tables | |
| ALTER TABLE . . . ADD | Adds a column to a table. |
| ALTER TABLE . . . MODIFY | Changes a column size, datatype, or default value. |
| ALTER TABLE . . . DROP COLUMN | Deletes one column from a table |
| ALTER TABLE . . . SET UNUSED or SET UNUSED COLUMN | Marks a column for deletion at a latertime. |
Modifying Existing Tables
General Syntax
ALTER TABLE tablename ADD|MODIFY|DROP COLUMN| columnname [definition]
Adding a column to an existing Table
This adds ext to the end of the publisher table
ALTER TABLE publisher ADD (ext NUMBER(4));
MODIFY COMMAND Change a column size, datatype or default value
- Changing Column Size (increase or decrease)
- Changing the datatype (such as varchar2 to CHAR)
- Changing or adding the default value of a column (such as DEFAULT SYSDATE)
ALTER TABLE books MODIFY (title VARCHAR2(10));
ALTER TABLE publisher MODIFY (rating DEFAULT 'N');
Adding Primary Key Constraints
This makes customer# the primary key the constraint name is used so you can easily identify an error if you try to enter a duplicate customer If you don’t assign constraint names, the error message displays the system-generated constraint name, which isn’t as helpful ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);
Foreign Key Constraint
This command instructs Oracle 11g to add a FOREIGN KEY constraint on the Customer# column of the ORDERS table. The name chosen for the constraint is orders_customer#_fk. This constraint makes sure an entry for the Customer# column of the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS table. When the command executes, a message indicates the table was altered successfully
ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)
Unique Constraints
ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE(columnname);
Check Constraint
ALTER TABLE orders ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);
Not Null Constraint
ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);
Creating NEW Tables
CREATE TABLE a_animals( animal_id NUMBER(6), name VARCHAR2(25), license_tag_number NUMBER(10), admit_date DATE CONSTRAINT ail_admit_date_nn NOT NULL, adoption_id NUMBER(5), vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,
CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id), CONSTRAINT ail_license_pk UNIQUE(license_tag_number)
);
Default Values
CREATE TABLE acctmgr( id CHAR(4), am_date DATE DEFAULT SYSDATE, am_count NUMBER(7,2) DEFAULT 0, );
Creating a table based on another table (subquery)
CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);