Can a foreign key be a primary key when set?

Question

I'm currently designing a database structure for a warehouse for an assignment.

Warehouse manager (*Manager name, manager salary, ^Warehouse managed)
Warehouse         (*Warehouse ID, Address, ^Warehouse Manager, Number of Rooms)

Primary Keys: *
Foreign keys: ^

In the part above I want to make it such that when the manager is first hired the warehouse managed can be null, but when he manages the warehouse no one else can manage it. So the only thing coming to mind is to make the foreign key become a primary when set.

• A manager can only manage one or null warehouse.
• A warehouse must be managed by one manager.

Answer 1

Your scenario can be managed like this (I have changed names of columns a little for better understanding):

WarehouseManager (WarehouseManagerId PK, ManagerName, ManagerSalary)
Warehouse        (WarehouseId PK, Address, WarehouseManagerId FK (WarehouseManager), Rooms

So, for a Warehouse zero or one manager can be set. Warehouse managed is removed, as it is not required and also can lead to inconsistencies if not set properly.

[later edit]

In order to force a warehouse manager to handle a maximum of one warehouse, a check constraint would be fit, but MySQL does not seem to handle such constructs, so a trigger may be used (not tested):

DELIMITER $$
CREATE TRIGGER `trgWarehouseOneManaged` 
BEFORE INSERT ON `Warehouse`
BEGIN
    IF EXISTS (SELECT COUNT(1) cnt FROM Warehouse GROUP BY WarehouseManagerId HAVING (cnt > 1)
        SIGNAL SQLSTATE '12345'
        SET MESSAGE_TEXT := 'more than one Warehouse managed';
    END IF;
END$$   
DELIMITER ;