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

Back To Top- Home - Category