Add unique constraint to job_title column in jobs

Let us understand how we can add UNIQUE constraint to job_title in jobs table.

  • We can either specify unique constraint using ALTER TABLE command or even while creating table.
  • Once we specify unique constraint on job_title, we can either have unique job titles or null in that column. Uniqueness will be enforced only on non empty or not null job titles.

Unique Constraint - ALTER TABLE

Here is how we can use ALTER TABLE command to add unique constraint. However if there are duplicate job titles this will fail.

ALTER TABLE jobs
  ADD CONSTRAINT jobs_uk1
  UNIQUE (job_title);

Unique Constraint - CREATE TABLE

We can add unique constraints using all the 3 approaches which we have seen while adding Primary Key.

  • Along with column
  • After all columns
  • Using alter table after creating table
  • In this case we will use the first approach.
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)
    CONSTRAINT jobs_uk1 UNIQUE,
  min_salary NUMBER,
  max_salary NUMBER
);

Inserting data into jobs

All these 3 insert statements will work with out any issues.

INSERT INTO jobs (job_title, min_salary, max_salary)
VALUES ('PRESIDENT', 1000, NULL);
INSERT INTO jobs (min_salary, max_salary)
VALUES (1500, NULL);
INSERT INTO jobs (min_salary, max_salary)
VALUES (1500, 1000);

However this insert statement will fail, as this violates unique constraint.

INSERT INTO jobs (job_title, min_salary, max_salary)
VALUES ('PRESIDENT', 1000, NULL);