Create jobs table without constraints

Let us create jobs table without constraints and see what happens by inserting data into the table.

Create Table jobs

Here is the script to create jobs table with out any constraints.

  • We will define columns with relevant data types.
CREATE TABLE jobs (
  job_id NUMBER,
  job_title VARCHAR2(30),
  min_salary NUMBER,
  max_salary NUMBER
);

Review Metadata

We can review metadata for the table by running queries against views such as user_objects and user_tab_columns.

  • Whenever we make changes to database objects, there will be changes to metadata which is stored in system tables.
  • Data in the system tables is exposed to us in the form of views.
  • Also data in the system tables typically stored in upper case.
  • Hence, when we try to retrieve results for a given object/table - we need to pass the object name or table name in upper case.
SELECT * 
FROM user_objects 
ORDER BY timestamp DESC;

SELECT *
FROM user_tab_columns
WHERE table_name = 'JOBS';

Insert Data into jobs

As the table is created let us insert data into the table. As there are no constraints defined, hardly any of our rules are enforced.

  • As part of the INSERT statement, If we do not specify field names, then by default values will be empty or null
INSERT INTO jobs (job_id)
VALUES (1);
  • As there is no unique constraint on job_title and primary key constraint on **job_id"" multiple rows in jobs can have same job_id or job_title.
  • Also we can have negative values as part of the min_salary and/or max_salary, as there are no check constraints defined.
INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
VALUES (1, 'PRESIDENT', -1, -1000);
INSERT INTO jobs (job_id,  job_title, min_salary, max_salary)
VALUES (2, 'PRESIDENT', 1000, NULL);

Read Data from jobs

We can run SELECT * FROM jobs; to preview the data and confirm that our data is breaking all the rules.

Now we have to understand all the aspects of DDL to improvise our script to enforce the rules.

Here are the steps:

  • Alter or recreate table jobs defining job_id as primary key. Also we will ensure that job_id is populated using sequence generated numbers by default.
  • Enforce required unique key and non empty constraint on job_title.
  • Apply all relevant constraints on min_salary and max_salary.