Oracle SQL Tutorials Chapter 7 ( Part 1 of 2 )

Chapter 7 – CREATING AND MANAGING TABLES

• Classify Main Database Objects
• Examining Table Structure
• Data Types of the Columns
• Creating a Simple Table
• Understanding How Schema Objects Work

DATABASE OBJECTS

NAMING RULES

Tables and columns are named according to the following rules ;

• Must be start with a letter.
• Can be up to 30 characters long.
• Only “A – Z, a – z, 0–9, _, $, #” characters are allowed.
• Under the same user, a name can only be used once.
• There should be no names reserved for Oracle.

CREATING TABLE

• The following syntax is used when creating a table ;

CREATE TABLE HR.WORKERS
(
    WORKER_ID    NUMBER (6),
    FIRST_NAME   VARCHAR2 (20 BYTE),
    LAST_NAME    VARCHAR2 (25 BYTE),
    HIRE_DATE    DATE,
    JOB_ID       VARCHAR2 (10 BYTE),
    SALARY       NUMBER (8, 2)

)

ADDING COMMENTS TO TABLE

• Comments can be added to tables or columns seperatly.
• These comments can be queried from the USER_TAB_COMMENTS and USER_COL_COMMENTS views.

COMMENT ON TABLE hr.workers IS 'This table is for workers';

COMMENT ON COLUMN hr.workers.salary IS 'This column is for salaries of workers';

• You can see comments from schema browser like this ;

CONSTRAINTS

• Constraints force table-level rules to be applied.
• They prevent deletion of interdependent data.
• They prevent incorrect data entry.
• They ensure that data is unique.
• They can be at table or column level.

NOT NULL CONSTRAINT

• Prevents specified columns from being null.

CREATE TABLE HR.WORKERS
(
    EMPLOYEE_ID   NUMBER (6),
    FIRST_NAME    VARCHAR2 (20 BYTE) NOT NULL,
    LAST_NAME     VARCHAR2 (25 BYTE),
    HIRE_DATE     DATE,
    JOB_ID        VARCHAR2 (10 BYTE),
    SALARY        NUMBER (8, 2)
);

• Let’s try to insert null value to ‘first_name’ column.

INSERT INTO hr.workers (first_name)
     VALUES ('');

UNIQUE CONSTRAINT

• Unique Constraint can be placed for one or more columns.
• More than one UC can be placed in a table.,

CREATE TABLE HR.WORKERS
(
    EMPLOYEE_ID   NUMBER (6) UNIQUE,
    FIRST_NAME    VARCHAR2 (20 BYTE),
    LAST_NAME     VARCHAR2 (25 BYTE),
    HIRE_DATE     DATE,
    JOB_ID        VARCHAR2 (10 BYTE),
    SALARY        NUMBER (8, 2)
)

• Let’s execute this insert command two times and see the results. It will throw error at the second time we executed. Because ‘employee_id’ colums must be unique.

INSERT INTO hr.workers (employee_id)
     VALUES (1);

PRIMARY KEY CONSTRAINT

• A table can only have one primary key.
• This column can not be Null.
• It also works as an index.

CREATE TABLE HR.WORKERS
(
    EMPLOYEE_ID    NUMBER (6) CONSTRAINT E_PK PRIMARY KEY,
    FIRST_NAME     VARCHAR2 (20 BYTE),
    LAST_NAME      VARCHAR2 (25 BYTE),
    HIRE_DATE      DATE,
    JOB_ID         VARCHAR2 (10 BYTE),
    SALARY         NUMBER (8, 2)
)

• ‘Employee_id’ column is primary key, so can not take same values and can not be null.
• Index is created on ‘Employee_id’ columnd automatically.

FOREIGN KEY CONSTRAINT

• It guarantees that, the data in one column of a table must come from another table.

• Let’s create a table with foreign key constraint.

CREATE TABLE HR.WORKERS
(
    EMPLOYEE_ID    NUMBER (6) CONSTRAINT E_PK PRIMARY KEY,
    FIRST_NAME     VARCHAR2 (20 BYTE),
    LAST_NAME      VARCHAR2 (25 BYTE),
    HIRE_DATE      DATE,
    JOB_ID         VARCHAR2 (10 BYTE),
    SALARY         NUMBER (8, 2),
    DEPARTMENT_ID  NUMBER (4),
    CONSTRAINT D_FK
    FOREIGN KEY (DEPARTMENT_ID)
    REFERENCES HR.DEPARTMENTS(department_id)
);

• Here is the Departments table’s content.

• I will try to add ‘123’ to department_id column. I will receive an error like below. Because we have foreign key constraint in department_id columnd and ‘123’ doesn’t exist in the Departments table.
• For instance ; I can insert ‘70’ to department_id because it is exist on the other table.

INSERT INTO hr.workers (employee_id, department_id)
     VALUES (1, 123);

CHECK CONSTRAINT

• It defines the requirements for each row.
• CURRVAL, NEXTVAL, LEVEL and ROWNUM aliases can not be used.
• SYSDATE, UID, USER and USERNV functions can not be called.
• Queries referring to other values in other rows can not be performed.

CREATE TABLE HR.WORKERS
(
    EMPLOYEE_ID    NUMBER (6) CONSTRAINT E_PK PRIMARY KEY,
    FIRST_NAME     VARCHAR2 (20 BYTE),
    LAST_NAME      VARCHAR2 (25 BYTE),
    HIRE_DATE      DATE,
    JOB_ID         VARCHAR2 (10 BYTE),
    SALARY         NUMBER (8, 2) CONSTRAINT S_MIN CHECK (SALARY > 3000)
);

• We can’t insert ‘2500’ to salary column because of the check constraint.I can only insert values that higher than ‘3000’.

INSERT INTO hr.workers (employee_id, salary)
     VALUES (1, 2500);

You can continue to read from this link ;

Oracle SQL Tutorials Chapter 7 ( Part 2 of 2 )