Chapter 13 – Table Create and Data Types
“Create a definite plan for carrying out your desire and begin at once, whether you are ready or not, to put this plan into action. “
- Napoleon Hill
The Basics of Creating a Table
At the bare minimum, you must include the tablename, along with the column names and their data types to create a table. A column that does not allow NULLs has a NOT NULL constraint, and it does not accept rows with no value for that column.
Creating a Table With Default Values
In this example, adding the text DEFAULT 100000 to the column description instructs the system to use a budget of 100000 if no budget is specified.
Altering a Table to Add a Column
This statement adds a column named Mgr_Phone to the Department table. The datatype must always be included.
Altering a Table to Drop a Column
This example drops a column named Mgr_Phone from the Department table.
Renaming a Table
To rename a table, use the ALTER TABLE statement just like the example above.
Dropping a Table
Dropping a table is easy to do and there is no "Oops" button. Be careful.
Defining Primary Keys
Primary key values must be unique, which means every row in the table must have a unique primary key value. If a single column is used for the primary key, it must be unique. If multiple columns are used, the combination of them must be unique. Primary Keys can be defined at CREATE TABLE time or they can be added later.
Defining a Primary Key After the Table Has Been Created
Primary key values must be unique, which means every row in the table must have a unique primary key value. If a single column is used for the primary key, it must be unique. If multiple columns are used, the combination of them must be unique. The examples above first created the Order_Table in the Schema SQL_Class and then used an ALTER command to define the Primary Key. This is a very standard practice.
Defining a Foreign Key After the Table Has Been Created
CREATE TABLESQL_CLASS.Customer_Table
( Customer_Number INTEGER not null
,Customer_NameVARCHAR(20) null
,Phone_NumberCHAR(8) null
,CONSTRAINT Cust_pk PRIMARY KEY (Customer_Number)) ;
CREATE TABLE SQL_CLASS.Order_table
(Order_NumberINTEGER not null,
Customer_NumberINTEGER null,
Order_DateDATE null,
Order_TotalNUMBER(10,2) null,
CONSTRAINT Order_pk PRIMARY KEY (Order_Number));
ALTER TABLE Order_Table
ADD CONSTRAINT cust_fk FOREIGN KEY (Customer_Number)
REFERENCES Customer_Table (Customer_Number)
Using an ALTER TABLE with an ADD CONSTRAINT we have specified a FOREIGN KEY. We must name the constraint. All Foreign Keys must also define the table that has the Primary Key and the Primary Key column dependent on the Foreign Key. This is referred to as the REFERENCE, which happens to be Customer_Number.
Creating a Table Using a CTAS
This table is created from the Sales_Table
CREATE TABLE Sales_Agg_Table
AS
SELECT Product_ID
,SUM(Daily_Sales) AS SumSales
FROM Sales_Table
Group by Product_ID;
You can create a table from a CTAS (Create Table AS) statement as in the above example.
Creating a Table Using a CTAS Join
This table is created from a join
CREATE Table Emp_Dept_Table
AS
SELECT E.*, Department_Name, Budget
FROM Employee_Table E
INNER JOIN
Department_Table D
ON E.Dept_No = D.Dept_No;
You can create a table from a CTAS (Create Table AS) join statement as in the above example.
Creating a Global Temporary Table Using a CTAS
This table is created from the Sales_Table
CREATE Global Temporary TABLE Sales_Agg
ON COMMIT PRESERVE ROWS
AS
SELECT Product_ID
,SUM(Daily_Sales) AS SumSales
FROM Sales_Table
Group by Product_ID;
You can create a global temporary table from a CTAS (Create Table AS) statement as in the above example.
Creating a Global Temporary Table Using a CTAS Join
This table is materialized from a join
CREATE Global Temporary Table Emp_Dept
ON COMMIT PRESERVE ROWS
AS
SELECT E.*, Department_Name, Budget
FROM Employee_Table E
INNER JOIN
Department_Table D
ON E.Dept_No = D.Dept_No;
You can create a global temporary table from a CTAS (Create Table AS) join statement as in the above example.
Creating a Temporary Table From Another's Space
We will first CREATE Temporary Tablespace
CREATE TEMPORARY TABLESPACE tbspace
TEMPFILE 'tbspace_t1.f' SIZE 50m REUSE
AUTOEXTEND ON
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 64K;
We will first CREATE Temporary Tablespace
CREATE GLOBAL TEMPORARY TABLE Emp_Names
( Employee_No number(6) NOT NULL,
First_Name varchar(12) NOT NULL,
Last_Name varchar2(20) NOT NULL )
ON COMMIT DELETE ROWS
TABLESPACE tbspace ;
The Rows materialized with created temporary tables are normally stored in temporary tablespace. Starting with Oracle 11g, users have the option to specify the temp tablespace for a Global Temporary Table. Within a TABLESPACE clause you can allocate the temporary tablespace with a specified extent size. This is shown in the examples above.