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 ;