Create Table Example – Oracle PL/SQL

      No Comments on Create Table Example – Oracle PL/SQL

CREATE TABLE statement is used to create tables in Oracle like other databases such as SQL Server and MySQL. Similarly we can also add constraints to the columns like Primary Key Constraint, Foreign Key Constraint, Check Constraint, Unique Constraint using CREATE TABLE statement.

Create Table Example with Primary Key Constraint

CREATE TABLE AUTHORS
(
 AUTHOR_ID NUMBER NOT NULL,
 FIRST_NAME VARCHAR2(30),
 LAST_NAME VARCHAR2(30),
 DOB DATE,
 ADDRESS VARCHAR2(100),
 CITY VARCHAR2(50),
 STATE VARCHAR2(50),
 CONSTRAINT AUTHORS_PK PRIMARY KEY(AUTHOR_ID)
);

Create Table Example with Foreign Key Constraint

CREATE TABLE BOOK_AUTHOR
(
 BOOK_ID NUMBER(5) NOT NULL,
 AUTHOR_ID NUMBER(5) NOT NULL,
 CONSTRAINT BOOK_AUTHOR_PK PRIMARY KEY(BOOK_ID, AUTHOR_ID),
 CONSTRAINT BOOK_AUTHOR_BOOKS_FK FOREIGN KEY(BOOK_ID) REFERENCES BOOKS(BOOK_ID),
 CONSTRAINT BOOK_AUTHOR_AUTHORS_FK FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHORS(AUTHOR_ID)
);

Create Table Example with Check Constraint

CREATE TABLE BOOKS
(
 BOOK_ID NUMBER(5) NOT NULL PRIMARY KEY,
 TITLE VARCHAR2(100),
 PRICE NUMBER(5,2),
 PUBLISH_DATE DATE,
 CONSTRAINT BOOKS_PRICE_CHK CHECK(PRICE>0)
);

Create Table Example with Unique Constraint

CREATE TABLE COUNTRIES
(
  COUNTRY_ID NUMBER(4) NOT NULL PRIMARY KEY,
  COUNTRY_NAME VARCHAR2(50),
  CONSTRAINT COUNTRIES_C_NAME_UQ UNIQUE(COUNTRY_NAME)
);

Create Table Example with Default

CREATE TABLE EMPLOYEES
(
 EMPLOYEE_ID NUMBER(5) NOT NULL,
 FIRST_NAME VARCHAR2(30),
 LAST_NAME VARCHAR2(30),
 DOB DATE DEFAULT SYSDATE,
 DEPARTMENT_ID NUMBER(5)
);

Leave a Reply

Your email address will not be published. Required fields are marked *