Can a database attribute be primary and foreign key?

Question

I have 2 tables, User and Employee. Each user is given a User_ID and that is a primary key in the User table and a foreign key in the Employee table. Can that attribute in the Employee table also be a primary key?

Refresh

What is primary key in Employee table? Only one Primary Key is allowed in each table. You can do composite primary key (more than one column). – Magnanimity

Answer 1

If you have a one-to-one relation between two tables, then the primary key of the details table is a foreign key as well.

 master           detail (1 : 1)
+----------+ 1:1 +-------------+
| PK  id   |<---o| PK FK  id   |
+----------+     +-------------+
|     col1 |     |        col1 |
|     col2 |     |        col2 |
|     etc. |     |        etc. |
+----------+     +-------------+

---

If you have a m-to-n relation, the junction table has columns relating to the two primary keys of the m and the n-tables. These columns are primary keys and foreign keys at the same time.

                    m : n
 m_table          junction
+----------+ 1:m +------------+      n_table
| PK  id1  |<---o| PK FK1 id1 | n:1 +----------+
+----------+     | PK FK2 id2 |o--->| PK  id2  |
|     col1 |     +------------+     +----------+
|     col2 |     |            |     |     col1 |
|     etc. |     +------------+     |     etc. |
+----------+                        +----------+

Note that with this construction, a record of one table can only be linked to a specific record of the other table once, since each composite primary key of the junction table must be unique. If you want to allow non-unique pairings, define a separate primary key in the junction table:

                    m : n
                  junction
                 +---------+
 m_table         | PK  id  |
+----------+ 1:m +---------+      n_table
| PK  id1  |<---o| FK1 id1 | n:1 +----------+
+----------+     | FK2 id2 |o--->| PK  id2  |
|     col1 |     |         |     +----------+
|     col2 |     +---------+     |     col1 |
|     etc. |                     |     etc. |
+----------+                     +----------+

In this case, the primary key and foreign key constraints are set on different columns. Alternatively you can also build the primary key with the two foreign keys plus one numerator or another discerning attribute.

---

In your case, if there is a one-to-one or a one-to-zero-or-one relationship between User and Employee, then yes, the User_ID in the Employee table can be Foreign Key (FK) and Primary Key (PK) at the same time. In words, this would mean: A user can be an employee as well, in which case the employee data would be attached to the user. If he is not an employee (he could be an external expert), no employee record is attached. If User_ID is FK and PK in Employee, each user can have at most one employee record attached. If User_ID was only FK but not PK in table Employee then a user could have several related employee records.

Driftwood

Answer 2

Yes. You would do this for instance if you wanted to enforce that all employees are users, and some users can be employees. This would be (zero or one) to one relationship.

Otherwise, you would not normally have the primary key the same as the foreign key, although it could contain foreign key(s), as in the case of a junction table for a many to many relationship.

Mechanical

Answer 3

Yes, for instance, in a banking application, accountholder and the nominee tables accountId becomes primary key and foreign key in nominee table. This assumes that the bank permits only one nominee for each account holder

Harriot