MySQL - a question for newbies: what are the PKs and FKs for these tables?
I have 3 tables:
Employees T
emp_id | name | address
Department T
dep_id | name
Salaries T
emp_id | dep_id | month | year | the salary
For each table, what are the primary keys and foreign keys?
My answer:
Table name | PK | FK |
- Employees: emp_id | dep_id
- Department: dep_id || emp_id
- Salary: emp_id, dep_id | emp_id, dep_id
Is my answer correct?
In the tables Employees
and Department
have primary keys (which you received correctly), but not foreign keys.
In Salaries
both of emp_id
and dep_id
are external keys.
As Salaries
there is no single primary key, though emp_id
, dep_id
, month
and year
can be difficult key (as the combination of these 4 will always be unique, if we assume that the employee is paid only once a month :) department).
Not really. Foreign key and primary keys have nothing to do with names. You will need to use the DESCRIBE syntax as described here to find out what these keys are.
Based on the names, I would make the following assumptions:
Employees T
emp_id | name | address
PK: emp_id
FK: none
Department T
dep_id | name
PK: dep_id
FK: none
Salaries T
emp_id | dep_id | month | year | salary
PK: no
FK: emp_id, dep_id
Do you have script tables creation? With just this information, there is nothing you can do but guess the attribute names ...
It looks like two tables (Employee and Department) and a link (join) Salary table to implement a many-to-many relationship.
As stated in other answers, you only need primary keys for Employees (PK emp_id) and Department (PK dep_id) tables, no foreign keys.
The mast table wage has foreign keys on emp_id, dep_id columns. For the primary key, you have two options. The primary key ensures the uniqueness of the column on which they are defined. So there is no primary key at all or a composite primary key (emp_id, dep_id, month, year); the order of the columns depends on your needs (select queries).