Write a postgreSQL statement to create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that, the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which exist in the jobs table. The specialty of the statement is that, The ON DELETE NO ACTION and the ON UPDATE NO ACTION actions will reject the deletion and any updates.
Assume that the following is the structure of two table jobs.
CREATE TABLE IF NOT EXISTS jobs ( JOB_ID INTEGER NOT NULL UNIQUE PRIMARY KEY, JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', MIN_SALARY decimal(6,0) DEFAULT 8000, MAX_SALARY decimal(6,0) DEFAULT NULL );
Column | Type | Modifiers
------------+-----------------------+----------------------------------------
job_id | character varying(10) | not null default ''::character varying
job_title | character varying(35) | not null
min_salary | numeric(6,0) | default NULL::numeric
max_salary | numeric(6,0) | default NULL::numeric
Indexes:
"jobs_pkey" PRIMARY KEY, btree (job_id)
Postgre SQL
Output:
Here is the command to see the structure of the created table :
postgres=# \d employees Table "public.employees" Column | Type | Modifiers -------------+-----------------------+--------------------------------- employee_id | numeric(6,0) | not null first_name | character varying(20) | default NULL::character varying last_name | character varying(25) | not null job_id | integer | salary | numeric(8,2) | default NULL::numeric Indexes: "employees_pkey" PRIMARY KEY, btree (employee_id) Foreign-key constraints: "employees_job_id_fkey" FOREIGN KEY (job_id) REFERENCES jobs(job_id)