DDL create table

Here’s a comprehensive guide to the CREATE TABLE command in Oracle Database, including its syntax, options, constraints, and examples.

---

Purpose of CREATE TABLE

The CREATE TABLE command is used to define a new table in the database. It specifies the table name, its columns, data types, constraints, and other attributes.

---

Syntax

CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...,
    [table_constraint]
);

---

Key Components

1. table_name:

   • Name of the table being created. It must be unique in the schema.

2. column_name:

   • Name of a column in the table.

3. datatype:

   • Data type of the column (e.g., NUMBER, VARCHAR2, DATE, etc.).

4. constraint:

   • Optional constraints applied to columns (e.g., NOT NULL, PRIMARY KEY).

5. table_constraint:

   • Constraints at the table level (e.g., PRIMARY KEY, FOREIGN KEY).

---

Supported Data Types in Oracle

Common Data Types:

Data Type	Description	Example
NUMBER	Stores numeric values	NUMBER(10, 2)
VARCHAR2	Variable-length string (up to 4000 chars)	VARCHAR2(50)
CHAR	Fixed-length string	CHAR(10)
DATE	Stores date and time	SYSDATE
CLOB	Large text data	Stores long text (e.g., XML)
BLOB	Binary large objects	Stores images, files, etc.

---

Constraints

1. NOT NULL

• Ensures the column cannot have NULL values.

column_name datatype NOT NULL

2. PRIMARY KEY

• Uniquely identifies a row in the table.

column_name datatype PRIMARY KEY

3. UNIQUE

• Ensures all values in a column are unique.

column_name datatype UNIQUE

4. FOREIGN KEY

• Establishes a relationship between tables.

column_name datatype REFERENCES other_table(column_name)

5. CHECK

• Ensures that values meet a specified condition.

column_name datatype CHECK (condition)

6. DEFAULT

• Sets a default value for the column.

column_name datatype DEFAULT value

---

Examples

1. Basic Table Creation

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50) NOT NULL,
    department_id NUMBER,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10, 2)
);

Explanation:

• emp_id: Primary key, must be unique.
• emp_name: Cannot be null.
• hire_date: Defaults to the current date.

---

2. Table with Foreign Key

CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50)
);

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    department_id NUMBER REFERENCES departments(department_id)
);

Explanation:

• The department_id in employees references the department_id in departments.

---

3. Table with Constraints

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100) NOT NULL,
    price NUMBER(10, 2) CHECK (price > 0),
    stock NUMBER DEFAULT 0,
    supplier_id NUMBER,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

Explanation:

• CHECK (price > 0): Ensures the price is positive.
• DEFAULT 0: Sets a default stock value to 0.
• Foreign Key: Links supplier_id to the suppliers table.

---

4. Temporary Tables

Temporary tables store data temporarily for the duration of a session or transaction.

CREATE GLOBAL TEMPORARY TABLE temp_employees (
    emp_id NUMBER,
    emp_name VARCHAR2(50)
) ON COMMIT DELETE ROWS;

Explanation:

• ON COMMIT DELETE ROWS: Data is deleted after each transaction.
• Use ON COMMIT PRESERVE ROWS if you want data to persist for the session.

---

5. Creating a Table with Data from Another Table

CREATE TABLE new_employees AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 50000;

Explanation:

• Creates a new table and populates it with data from the employees table.

---

Advanced Options

1. Tablespaces

You can specify the tablespace where the table will be created.

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50)
) TABLESPACE my_tablespace;

2. Storage Settings

Define how the table stores data on disk.

CREATE TABLE large_table (
    data_id NUMBER PRIMARY KEY,
    data_value CLOB
) STORAGE (
    INITIAL 1M
    NEXT 1M
    MAXSIZE UNLIMITED
);

---

Error Handling

Common Errors:

1. ORA-00955: Name is already used by an existing object:

   • The table name already exists. Use a unique name or drop the existing table:

     DROP TABLE table_name;
     

2. ORA-00942: Table or view does not exist:

   • Ensure the table exists before referencing it.

3. ORA-02264: Name already used by an existing constraint:

   • Constraint names must be unique. Use explicit names or let Oracle auto-generate them.

---

Useful Queries to Verify Table

1. View Table Structure:

   DESC employees;
   

2. Check Created Tables:

   SELECT table_name FROM user_tables;
   

3. Check Constraints:

   SELECT constraint_name, constraint_type, table_name
   FROM user_constraints
   WHERE table_name = 'EMPLOYEES';
   

4. Check Columns:

   SELECT column_name, data_type, data_length
   FROM user_tab_columns
   WHERE table_name = 'EMPLOYEES';
   

---

Best Practices

1. Use meaningful table and column names.
2. Always define a primary key for data integrity.
3. Use foreign keys to maintain relationships.
4. Define constraints explicitly for better readability and debugging.
5. Use tablespaces wisely for performance optimization.