Create Table

Once database is created, we can login with our credentials and start create our own DB objects like tables, views, constraints, procedures etc. The first things we need in database are tables. In a library database, the main tables used are BOOKS. Let us first create this table.

We need to identify all attributes of BOOKS first to create table. The main attributes of BOOKS are:

  • ISBN: – This is the unique number for each book used to identify the book.
  • BOOK_NAME: – It is the name of the book.
  • PUBLISHER_NAME: – Name of the publisher who has published the book
  • CATEGORY_NAME: – It describes which category / subject that the book is about.
  • NUM_OF_PAGES: – Number of pages in the book.

These are the main attributes of any book. Now the table BOOKS will have all these columns. But to create table with these columns, we should know its datatype and size. Usually these are given in the requirement document, but let us decide here.

  • ISBN: – Since it is unique number ranging from 10-13 digits, it is of datatype NUMBER and its size would be 13 digits.
  • BOOK_NAME: – This is the name and hence it should be VARCHAR. Any book cannot have very big names; usually their names are limited to short. Hence let us say it is up to 100 characters.
  • PUBLISHER_NAME: – Since it is name of the publisher, it should be VARCHAR and size can be up to 50 characters.
  • CATEGORY_NAME: – this is again VARCHAR and it cannot be more than 30 characters.
  • NUM_OF_PAGES: – This is of type NUMBER and can have max of 4 digits.

Now that we know columns, their datatype and size, we can create table, BOOKS.

CREATE TABLE BOOKS
(
	ISBN NUMBER (13),
	BOOK_NAME VARCHAR2 (100),
	PUBLISHER_NAME VARCHAR2 (50),
	CATEGORY_NAME VARCHAR2 (30),
	NUM_OF_PAGES NUMBER (4)
);

This is a very simple and basic table. We have not defined any primary key or any other constraints. In a relational database, these are the main factors that need to be defined while creating a table. Otherwise, table will not have any importance. Let us first create primary key for the table. In our case of BOOKS, ISBN is the unique number that differentiates each book. BOOK_NAME can be unique but not all the cases. We can have same name from different authors. Hence ISBN is the best candidate for primary key.

Primary key can be defined while creating a table itself or latter by altering the table. Below DDLs define different methods of creating primary key.

CREATE TABLE BOOKS (ISBN NUMBER (13) PRIMARY KEY,--Usual method of defining primary key
		BOOK_NAME VARCHAR2 (100),
		PUBLISHER_NAME VARCHAR2 (50),
		CATEGORY_NAME VARCHAR2 (30),
		NUM_OF_PAGES NUMBER (4));

CREATE TABLE BOOKS (ISBN NUMBER (13),
		BOOK_NAME VARCHAR2 (100),
		PUBLISHER_NAME VARCHAR2 (50),
		CATEGORY_NAME VARCHAR2 (30),
		NUM_OF_PAGES NUMBER (4),
	PRIMARY KEY (ISBN)); --Usually used when more than one column is involved in primary key


CREATE TABLE BOOKS (ISBN NUMBER (13),
		BOOK_NAME VARCHAR2 (100),
		PUBLISHER_NAME VARCHAR2 (50),
		CATEGORY_NAME VARCHAR2 (30),
		NUM_OF_PAGES NUMBER (4),
	CONSTRAINT pk_isbn PRIMARY KEY (ISBN)); --Usually used when more than one column is involved in primary key, and defines a named primary key constraint

Below two methods are used when

  • Used when we have to define PK after creating a table
  • table is having data and we cannot drop the table and recreate it with primary key
  • Named primary key constraint is required
  • Primary key is a composite primary key – more than one column is primary key
Translate »