Define NOT NULL Constraints for all columns in jobs

Let us define NOT NULL Constraints for all columns in jobs except for job_id.

  • We have 4 columns where job_id is already defined as primary key.
  • However all the other fields should not have null values.
  • We can define NOT NULL Constraint along with column name or using ALTER TABLE command after creating the table.

min_salary NOT NULL

As all the rows in the table have non empty values, we can modify the column to NOT NULL.

ALTER TABLE jobs 
  MODIFY min_salary NOT NULL;

We can also define not null constraints with names.

ALTER TABLE jobs 
  MODIFY min_salary 
  CONSTRAINT jobs_nn1 NOT NULL;

max_salary NOT NULL

There are some empty values in max_salary and hence we will not be able to modify the column to NOT NULL…

  • Update null values to some numeric value.
  • Alter table column to modify to NOT NULL.
UPDATE jobs 
  SET max_salary = 0
  WHERE max_salary IS NULL;

SELECT * FROM jobs;

ALTER TABLE jobs 
  MODIFY max_salary NOT NULL;

job_title NOT NULL

As job_title have some null values and also as there is unique constraint defined, here are the steps which can be followed to modify to NOT NULL

  • We have to delete all the records with job_title null.
  • Alter table column to modify to NOT NULL.
DELETE FROM jobs
  WHERE job_title IS NULL;

SELECT * FROM jobs;

ALTER TABLE jobs 
  MODIFY job_title NOT NULL;

Create Table with NOT NULL Constraints

We can also define NOT NULL constraints while creating table.

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_nn1 NOT NULL
    CONSTRAINT jobs_uk1 UNIQUE,
  min_salary NUMBER 
    CONSTRAINT jobs_nn2 NOT NULL,
  max_salary NUMBER NOT NULL
);

Review Constraints

We can use following query to review all the constraints in a given table.

SELECT * 
FROM user_constraints 
WHERE table_name = 'JOBS';

Inserting Data into table

Let us insert few records into the table.

INSERT INTO jobs (job_title, min_salary, max_salary)
VALUES ('PRESIDENT', 1000, 0);
INSERT INTO jobs (job_title, min_salary, max_salary)
VALUES ('DIRECTOR', -1, -1000);
INSERT INTO jobs (job_title, min_salary, max_salary)
VALUES ('CEO', 123.456, 123456.7890);
INSERT INTO jobs (job_title, min_salary, max_salary)
VALUES ('ASSOCIATE DIRECTOR', 123.456, 1234567.7890);

SELECT * FROM jobs;