Composite PRIMARY KEY
- 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
1 answers ( 0 marked as helpful)
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
