Table Creation and Management

From rbachwiki
Jump to navigation Jump to search
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