Site icon IT Tutorial

Oracle SQL Tutorials Chapter 7 ( Part 1 of 2 )

Chapter 7 – CREATING AND MANAGING TABLES

DATABASE OBJECTS

 

 

NAMING RULES

Tables and columns are named according to the following rules ;

 

 

CREATING 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

 

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';

 

 

CONSTRAINTS

 

 

NOT NULL CONSTRAINT

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)
);

 

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

 

 

UNIQUE CONSTRAINT

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)
)

 

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

 

 

PRIMARY 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)
)

 

 

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)
);

 

 

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

 

 

 

CHECK 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) CONSTRAINT S_MIN CHECK (SALARY > 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 )

 1,030 views last month,  1 views today

Exit mobile version