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
15. Write a sql statement to create a table employees, including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column department_id, reference by the column department_id of departments table, can contain only those values which exist in the departments table and another 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.
Assume that the structure of two tables departments and jobs.
Indexes:
"departments_pkey" PRIMARY KEY, btree (department_id)
Indexes:
"jobs_pkey" PRIMARY KEY, btree (job_id)
Sample Solution:
Code:
CREATE TABLE IF NOT EXISTS employees (
EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,
FIRST_NAME varchar(20) DEFAULT NULL,
LAST_NAME varchar(25) NOT NULL,
EMAIL varchar(25) NOT NULL,
PHONE_NUMBER varchar(20) DEFAULT NULL,
HIRE_DATE date NOT NULL,
JOB_ID varchar(10) NOT NULL,
SALARY decimal(8,2) DEFAULT NULL,
COMMISSION_PCT decimal(2,2) DEFAULT NULL,
MANAGER_ID decimal(6,0) DEFAULT NULL,
DEPARTMENT_ID decimal(4,0) DEFAULT NULL,
FOREIGN KEY(DEPARTMENT_ID)
REFERENCES departments(DEPARTMENT_ID),
FOREIGN KEY(JOB_ID)
REFERENCES jobs(JOB_ID)
);
Output:
postgres=# CREATE TABLE IF NOT EXISTS employees ( postgres(# EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY, postgres(# FIRST_NAME varchar(20) DEFAULT NULL, postgres(# LAST_NAME varchar(25) NOT NULL, postgres(# EMAIL varchar(25) NOT NULL, postgres(# PHONE_NUMBER varchar(20) DEFAULT NULL, postgres(# HIRE_DATE date NOT NULL, postgres(# JOB_ID varchar(10) NOT NULL, postgres(# SALARY decimal(8,2) DEFAULT NULL, postgres(# COMMISSION_PCT decimal(2,2) DEFAULT NULL, postgres(# MANAGER_ID decimal(6,0) DEFAULT NULL, postgres(# DEPARTMENT_ID decimal(4,0) DEFAULT NULL, postgres(# FOREIGN KEY(DEPARTMENT_ID) postgres(# REFERENCES departments(DEPARTMENT_ID), postgres(# FOREIGN KEY(JOB_ID) postgres(# REFERENCES jobs(JOB_ID) 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 | numeric(6,0) | not null
first_name | character varying(20) | default NULL::character varying
last_name | character varying(25) | not null
email | character varying(25) | not null
phone_number | character varying(20) | default NULL::character varying
hire_date | date | not null
job_id | character varying(10) | not null
salary | numeric(8,2) | default NULL::numeric
commission_pct | numeric(2,2) | default NULL::numeric
manager_id | numeric(6,0) | default NULL::numeric
department_id | numeric(4,0) | default NULL::numeric
Indexes:
"employees_pkey" PRIMARY KEY, btree (employee_id)
Foreign-key constraints:
"employees_department_id_fkey" FOREIGN KEY (department_id) REFERENCES departments(department_id)
"employees_job_id_fkey" FOREIGN KEY (job_id) REFERENCES jobs(job_id)
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL statement to create a table employees, including employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that, the employee_id column did not contain any duplicate values at the time of insertion and the foreign key columns combined by department_id and manager_id columns contain only those unique combination values, which combinations exist in the departments table.
Next: 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.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework