Composite PRIMARY KEY

Question

Do I understand it correctly? I assume that in tables: dept_manager, salaries, titles, dept_emp we have COMPOSITE PRIMARY KEYS. titles - PRIMARY KEY consists of: emp_no, title, from_date. salaries- PRIMARY KEY consists of: emp_no, from_date. dept_emp - PRIMARY KEY consists of: emp_no, dept_no. dept_manager - PRIMARY KEY consists of: emp_no, dept_no. Shouldn't we in this case create a separate column (and only this column will be marked as PRIMARY KEY) in each of the abovementioned tables that will be a combination of those elements and show it somehow on the scheme?
How can we link emp_no from table employees with emp_no from dept_manager when we can have not one but few from_date,to_date,dept_no values so we probably won't get an exact match?

Regards,
Adam

Answer 1

Hi Adam!
Thanks for reaching out.

We cannot do this because they are of different types. The idea is that you can have duplicates for some of the columns. The combination of all columns included in the composite primary key should be unique.
You can use the following query:
SELECT
    *
FROM
    employees e
        JOIN
    dept_manager dm ON e.emp_no = dm.emp_no;
They may have different columns but we are matching them by the same column.

Hope this helps.
Best,
Tsvetelin

Submit an answer