Difference between revisions of "Table Creation and Management"

From rbachwiki
Jump to navigation Jump to search
Line 25: Line 25:
|-
|-
|}
|}
== Creating a table based on another table (subquery) ==
CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);
SELECT firstname || lastname FROM customers;
this will display the info but last and first names will be together eg. firstlast
to add a space between the names :
SELECT firstname || ' , ' | || lastname as "Customer Name" FROM customers;
this will add the , between the names and give it an alias heading "Customer name"
== 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);

Revision as of 22:59, 12 October 2017

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.


Creating a table based on another table (subquery)

CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);


SELECT firstname || lastname FROM customers;
this will display the info but last and first names will be together eg. firstlast
to add a space between the names :
SELECT firstname || ' , ' | || lastname as "Customer Name" FROM customers;
this will add the , between the names and give it an alias heading "Customer name"

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