Clever Geek Handbook

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?

+1


source to share


5 answers


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).

+6


source


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.



+2


source


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

+2


source


Do you have script tables creation? With just this information, there is nothing you can do but guess the attribute names ...

0


source


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).

0


source






More articles:


All Articles