Create regions table with primary key and sequence

Let us create our first table in the database. We will also add primary key and sequence after which we will insert the data into table.

Once we get the ER diagram from Data Architect, we need to start with root level tables. In our case root level tables are nothing but regions and jobs. We will start with regions table.

As regions have child table such as countries we need to have primary key defined for regions. Also to avoid the effort of determining unique numbers ourselves we can use sequence generated numbers by using Database Sequence.

  • Create Table regions
  • Add Primary Key
  • Create Sequence regions_s
  • Insert Data into regions
  • Read Data from regions

Create Table regions

We can add primary key while creating the table or after creating the table.

Create Table with Primary Key

CREATE TABLE regions (
  region_id INTEGER,
  region_name VARCHAR2(30),
  CONSTRAINT regions_pk PRIMARY KEY (region_id)
);

Create Table with out Primary Key

We can always add primary key later. However data should not violate the rules enforced by Primary Key.

DROP TABLE regions;
CREATE TABLE regions (
  region_id INTEGER,
  region_name VARCHAR2(30)
);

Add Primary Key

ALTER TABLE regions 
  ADD CONSTRAINT regions_pk 
  PRIMARY KEY (region_id)

Create Sequence regions s

CREATE SEQUENCE regions_s;

Insert data into regions

INSERT INTO regions (region_id, region_name) 
VALUES (regions_s.nextval, 'EU');

INSERT INTO regions (region_id, region_name) 
VALUES (regions_s.nextval, 'NAM');

Read data from regions

SELECT * FROM regions;