PostgreSQL Create Table: Create a table to allow one of the columns to contain unique values and another two columns are referenced to the columns of another two tables
16. Write a SQL 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 UPDATE CASCADE action allows you to perform the cross-table update and ON DELETE RESTRICT action rejects the deletion. The default action is ON DELETE RESTRICT.
Assume that the following is the structure of the 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 | integer | not null job_title | character varying(35) | not null default ' '::character varying min_salary | numeric(6,0) | default 8000 max_salary | numeric(6,0) | default NULL::numeric Indexes: "jobs_pkey" PRIMARY KEY, btree (job_id)
Sample Solution:
Code:
CREATE TABLE IF NOT EXISTS employees (
EMPLOYEE_ID INTEGER NOT NULL PRIMARY KEY,
FIRST_NAME varchar(20) DEFAULT NULL,
LAST_NAME varchar(25) NOT NULL,
JOB_ID INTEGER NOT NULL,
SALARY decimal(8,2) DEFAULT NULL,
FOREIGN KEY(JOB_ID)
REFERENCES jobs(JOB_ID)
ON UPDATE CASCADE ON DELETE RESTRICT
);
Output:
postgres=# CREATE TABLE IF NOT EXISTS employees ( postgres(# EMPLOYEE_ID INTEGER NOT NULL PRIMARY KEY, postgres(# FIRST_NAME varchar(20) DEFAULT NULL, postgres(# LAST_NAME varchar(25) NOT NULL, postgres(# JOB_ID INTEGER NOT NULL, postgres(# SALARY decimal(8,2) DEFAULT NULL, postgres(# FOREIGN KEY(JOB_ID) postgres(# REFERENCES jobs(JOB_ID) postgres(# ON UPDATE CASCADE ON DELETE RESTRICT postgres(# ); CREATE TABLE
Here is the command to see the structure of the created table :
postgres=# \d employees
Table "public.employees"
Column | Type | Modifiers
-------------+-----------------------+---------------------------------
employee_id | integer | not null
first_name | character varying(20) | default NULL::character varying
last_name | character varying(25) | not null
job_id | integer | not null
salary | numeric(8,2) | default NULL::numeric
Indexes:
"employees_pkey" PRIMARY KEY, btree (employee_id)
Foreign-key constraints:
"employeesnew_job_id_fkey" FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON UPDATE CASCADE ON DELETE RESTRICT
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
New Content: Composer: Dependency manager for PHP, R Programming