Write a postgre 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.
Column | Type | Modifiers
-----------------+-----------------------+-----------------------
department_id | numeric(4,0) | not null
department_name | character varying(30) | not null
manager_id | numeric(6,0) | default NULL::numeric
location_id | numeric(4,0) | default NULL::numeric\
Indexes:
"departments_pkey" PRIMARY KEY, btree (department_id)
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 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)