Using DDL Statements to Create and Manage Tables
A schema is the collection of multiple database objects,which are known as schema objects.These objects have direct access by their owner schema.Below table lists the schema objects.
Index - to improve performance of queries on the tables
Synonym - alternative name of an object
One of the first steps in creating a database is to create the tables that will store an organization's data.Database design involves identifying system user requirements for various organizational systems such as order entry, inventory management, and accounts receivable. Regardless of database size and complexity, each database is comprised of tables.
Creating the table
Table Naming Conventions -
Cannot use the same name of another existing object in your schema
Must not be a SQL reserved word
Following the above guidelines, 'EMP85' can be a valid table name.But 85EMP is not.Similarly, UPDATE cannot be a chosen as a table name since it a SQL reserved keyword.
CREATE TABLE statement
The CREATE TABLE is a DDL statement which is used to create tables in the database.The table gets created as soon as the CREATE TABLE script is executed and is ready to hold the data onwards.The user must have the CREATE TABLE system privilege to create the table in its own schema.But to create a table in any user's schema, user must have CREATE ANY TABLE schema.
Here is the syntax of a basic CREATE TABLE statement.There may be many additional clauses to explicitly provide the storage specifications or segment values.
In the above syntax, DEFAULT specifies default value which can be used during INSERT statement if the column is ignored. It cannot contain references to other table columns or pseudo columns (CURRVAL, NEXTVAL, LEVEL, and ROWNUM) except SYSDATE and USER, or date constants that are not fully specified.
Constraints are the rules defined optionally at the column level or table level (covered later in this chapter).These rules are checked during any data action (Insert, update) on the table and raise error to abort the action upon its violation.
For example, the CREATE TABLE statement below creates a table EMP_TEST. Note the column specifications, data type and precision.
A user can refer the tables from other user's schema by prefixing the username or schema with the table name.For example, a user GUEST wishes to query the employee name and salary from the EMP_TEST table which is owned by SCOTT. He can issue the below query -
A column can hold a default value during the time of table creation.It helps to restrict the NULL values getting into the column. Default value can be deduced from either a literal, expression or SQL function which must return a compatible data type to the column. In the below CREATE TABLE statement, note that the LOCATION_ID column has default value 100.
CTAS - Create table using subquery
A table can be created from an existing table in the database using a subquery option.It copies the table structure as well as the data from the table. Data can also be copied based on conditions.The column data type definitions including the explicitly imposed NOT NULL constraints are copied into the new table.
The below CTAS script creates a new table EMP_BACKUP. Employee data of department 20 gets copied into the new table.
Data types are used to specify the basic behavior of a column in the table.On a broader basis,column behavior can either belong to number,character or a date family.There are multiple other subtypes which belong to these families.
Number data type
The NUMBER datatype encompasses both integer,fixed-point,and floating-point numeric values.Early versions of Oracle defined different datatypes for each of these different types of numbers,but now the NUMBER datatype serves all of these purposes.Choose the NUMBER datatype when a column must store numerical data that can be used in mathematical calculations.Occasionally,the NUMBER datatype is used to store identification numbers where those numbers are generated by the DBMS as sequential numbers.
NUMBER (p, s), where p is the precision up to 38 digits and s is the scale (number of digits to the right of the decimal point).The scale can range between -84 to 127.
NUMBER (p),is a fixed-point number with a scale of zero and a precision of p.
FLOAT [(p)],where p is the binary precision that can range from 1 to 126. If p is not specified the default value is binary 126.
Date data type
For each DATE data type, Century, Year, Month, Day, Hour, Minute, Second are stored in database. Every database system has a default date format that is defined by the initialization parameter NLS_DATE_FORMAT. This parameter is usually set to DD-MON-YY.If you do not specify a time, the default time is 12:00:00 a.m.
Character data type
Oracle supports three predefined character datatypes including CHAR, VARCHAR, VARCHAR2, and LONG.VARCHAR and VARCHAR2 are actually synonymous, and Oracle recommends using VARCHAR2 instead of VARCHAR.Use the CHAR datatype when the column will store character values that are fixed-length.For example, a
Social Security number (SSN) in the United States is assigned to every citizen and is always 9 characters in size (even though an SSN is strictly composed of digits,the digits are treated as characters), and would be specified as CHAR(9). Use the VARCHAR2 datatype to store alphanumeric data that is variable-length.For example, a customer name or address will vary considerably in terms of the number of characters to be stored.The maximum size of a VARCHAR2 column is 4,000 characters.
LOB data type
Oracle provides several different LOB datatypes, including CLOB (character large object) and BLOB (binary large object).Columns of these datatypes can store unstructured data including text, image, video, and spatial data.The CLOB datatype can store up to eight terabytes of character data using the CHAR database character set.The BLOB datatype is used to store unstructured binary large objects such as those associated with image and video data where the data is simply a stream of "bit" values.A BLOB datatype can store up to eight terabytes of binary data.The NCLOB data type can store character large objects in multibyte national character set up to 8TB to 128TB.The BFILE data type value works as a file locator or pointer to file on the server's file system. The maximum file size supported is 8TB to 128TB.
Constraints are the set of rules defined in Oracle tables to ensure data integrity.These rules are enforced placed for each column or set of columns.Whenever the table participates in data action, these rules are validated and raise exception upon violation. The available constraint types are NOT NULL, Primary Key, Unique, Check, and Foreign Key.
The below syntax can be used to impose constraint at the column level.
All constraints except NOT NULL, can also be defined at the table level. Composite constraints can only be specified at the table level.
NOT NULL Constraint
A NOT NULL constraint means that a data row must have a value for the column specified as NOT NULL.If a column is specified as NOT NULL,the Oracle RDBMS will not allow rows to be stored to the employee table that violate this constraint.It can only be defined at column level, and not at the table level.
Sometimes it is necessary to enforce uniqueness for a column value that is not a primary key column.The UNIQUE constraint can be used to enforce this rule and Oracle will reject any rows that violate the unique constraint.Unique constraint ensures that the column values are distinct, without any duplicates.
Table Level: CONSTRAINT [constraint name] UNIQUE (column name)
Note: Oracle internally creates unique index to prevent duplication in the column values.Indexes would be discussed later in PL/SQL.
Each table must normally contain a column or set of columns that uniquely identifies rows of data that are stored in the table.This column or set of columns is referred to as the primary key.Most tables have a single column as the primary key.Primary key columns are restricted against NULLs and duplicate values.
Points to be noted -
Multiple columns can be clubbed under a composite primary key.
Oracle internally creates unique index to prevent duplication in the column values.Indexes would be discussed later in PL/SQL.
The following example shows how to use PRIMARY KEY constraint at column level.
The following example shows how to define composite primary key using PRIMARY KEY constraint at the table level.
When two tables share the parent child relationship based on specific column, the joining column in the child table is known as Foreign Key.This property of corresponding column in the parent table is known as Referential integrity.Foreign Key column values in the child table can either be null or must be the existing values of the parent table.Please note that only primary key columns of the referenced table are eligible to enforce referential integrity.
If a foreign key is defined on the column in child table then Oracle does not allow the parent row to be deleted,if it contains any child rows.However,if ON DELETE CASCADE option is given at the time of defining foreign key,Oracle deletes all child rows while parent row is being deleted.Similarly,ON DELETE SET NULL indicates that when a row in the parent table is deleted, the foreign key values are set to null.
The following example shows how to use FOREIGN KEY constraint at column level.
Usage of ON DELETE CASCADE clause
Sometimes the data values stored in a specific column must fall within some acceptable range of values.A CHECK constraint requires that the specified check condition is either true or unknown for each row stored in the table.Check constraint allows to impose a conditional rule on a column, which must be validated before data is inserted into the column. The condition must not contain a sub query or pseudo column CURRVAL NEXTVAL, LEVEL, ROWNUM, or SYSDATE.