Create jobs table with Primary Key Constraint - using IDENTITY

Let us understand how to create tables like jobs with Primary Key Constraint on column job_id. It is surrogate key and can be populated using numbers generated by Sequence.

  • Create Table with primary key - jobs
    • There are 3 approaches where tables are typically created where sequence is automatically generated… We can specify GENERATED [BY DEFAULT]|[GENERATED] AS IDENTITY Clause to do so.
    • Once the table is created we should be able to insert the data directly. There is no need to specify the job_id column as part of insert statement.
  • Here are the queries for getting metadata whenever there are changes to objects or tables or indexes.
SELECT * 
FROM user_objects 
ORDER BY timestamp DESC;

SELECT *
FROM user_tab_columns
WHERE table_name = 'JOBS';

SELECT *
FROM user_indexes
WHERE table_name = 'JOBS';

Let us understand how we can Create Table with IDENTITY so that we don’t need to generate sequence separately.

  • Let us drop the sequence jobs_s - DROP SEQUENCE jobs_s;
  • GENERATED can be used to map a sequence to any column. However, we typically map the sequence to primary key field.
  • We can specify GENERATED clause while creating table or while adding a column to the existing table.
  • GENERATED is followed by BY DEFAULT AS IDENTITY or ALWAYS AS IDENTITY.
  • BY DEFAULT AS IDENTITY can be used to populate the field automatically using the system generated sequence. However, we should be able to insert data into the field by specifying values directly and hence INSERT statement can have the column on which sequence is mapped.
  • With ALWAYS AS IDENTITY we can only populate data using sequence. INSERT Statement should not have the column on which sequence is mapped.
  • We can specify in all 3 approaches.

1st approach

Primary Key Constraint is defined along with the column.

DROP TABLE jobs;

CREATE TABLE jobs (
  job_id INTEGER 
    GENERATED BY DEFAULT AS IDENTITY
    START WITH 1 INCREMENT BY 1
    CONSTRAINT jobs_pk PRIMARY KEY,
  job_title VARCHAR2(30),
  min_salary NUMBER,
  max_salary NUMBER
);

2nd approach

Primary Key Constraint is added after all the columns in the table.

DROP TABLE jobs;

CREATE TABLE jobs (
  job_id INTEGER
    GENERATED BY DEFAULT AS IDENTITY
    START WITH 1 INCREMENT BY 1,
  job_title VARCHAR2(30),
  min_salary NUMBER,
  max_salary NUMBER,
  CONSTRAINT jobs_pk PRIMARY KEY (job_id)
);

3rd approach

Primary Key Constraint is added after creating the table using ALTER TABLE Command.

DROP TABLE jobs;

CREATE TABLE jobs (
  job_id INTEGER
    GENERATED BY DEFAULT AS IDENTITY
    START WITH 1 INCREMENT BY 1,
  job_title VARCHAR2(30),
  min_salary NUMBER,
  max_salary NUMBER
);

ALTER TABLE jobs 
  ADD CONSTRAINT jobs_pk 
  PRIMARY KEY (job_id);

Inserting Data into jobs

We can now insert the data using sequence. Let us insert the data and see the behavior.

  • Let us insert one record into the table.
INSERT INTO jobs (job_title)
VALUES (NULL);
SELECT * FROM jobs;
  • As we have inserted data already using 1 for job_id, it will complain saying Unique Constraint Viloated.
INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
VALUES (1, 'PRESIDENT', -1, -1000);
SELECT * FROM jobs;
  • We can continue inserting data without specifying job_id. Unique constraint enforced by Primary Key is not violated as unique number is guaranteed automatically for us…
INSERT INTO jobs (job_title, min_salary, max_salary)
VALUES ('PRESIDENT', 1000, NULL);
SELECT * FROM jobs;
  • If we specify GENERATED ALWAYS AS IDENTITY, we cannot specify the job_id as part of insert statement. It will throw cannot insert into a generated always identity column error.
DROP TABLE jobs;

CREATE TABLE jobs (
  job_id INTEGER
    GENERATED ALWAYS AS IDENTITY
    START WITH 1 INCREMENT BY 1,
  job_title VARCHAR2(30),
  min_salary NUMBER,
  max_salary NUMBER
);

ALTER TABLE jobs 
  ADD CONSTRAINT jobs_pk 
  PRIMARY KEY (job_id);

INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
VALUES (1, 'PRESIDENT', -1, -1000);