Create jobs table - conclusion

Let us recap what ever is covered while creating jobs table.

  • Understand the requirements to define the rules.
  • Create table with all the constraints.
  • Once table is created we should be able to insert only valid and good quality data into the table typically through applications.

Understanding Requirements

Let us recollect the requirements and the rules we have identified on top of our data.

  • job_id is primary key column and it has to be populated using sequence generated integer. We need to have primary key for this table because other tables such as employees and job_history have to be created as child tables.
  • job_title has to be unique and should not be empty
  • min_salary and max_salary should not be empty. Also they should not have more than 2 digits in fraction part.
  • min_salary should not be less than $3000.
  • max_salary should not be greater than 6 digit dollar amount ($999,999.99)
  • As we are trying to define salary ranges for each job title, we need to ensure that min_salary is less than max_salary .

Creating Table

Let us understand some of the common practices while creating tables.

  • We do not give names to NOT NULL Constraints.
  • NOT NULL Constraints are typically defined along with columns in the table.
  • Once the table is created, we typically add primary key constraint and then rest of the constraints.
  • If the table is non-transactional table, we typically come up with INSERT statements to insert valid business data into the table.

Here is the complete script following common practices. We are not going to insert data 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) NOT NULL,
  min_salary NUMBER(8, 2) NOT NULL,
  max_salary NUMBER(8, 2) NOT NULL
);

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

ALTER TABLE jobs
  ADD CONSTRAINT jobs_uk1 
  UNIQUE (job_title);

ALTER TABLE jobs
  ADD CONSTRAINT jobs_chk1
  CHECK (min_salary >= 3000);

ALTER TABLE jobs
  ADD CONSTRAINT jobs_chk2
  CHECK (min_salary < max_salary);

Review Data Dictionary

Let us understand what all objects are created by querying user_objects view and also all the constraints that are added by querying user_constraints view…

SELECT object_name, object_type, timestamp
FROM user_objects
ORDER BY timestamp DESC;

SELECT * 
FROM user_constraints 
WHERE table_name = 'JOBS';

You will see following objects created in the database.

  • Table by name jobs.
  • Sequence with system generated name.
  • Index by name jobs_pk for primary key constraint.
  • Index by name jobs_uk for unique constraint.