How to Have a Foreign Key Pointing to Two Primary Keys

Is there a way to make two primary keys, with only one foreign key that references another tables primary key

Presumably supervisor references an employee number (because the supervisor is also an employee), so it should only be one column.

Also, there's no reason why a next of kin entry should refer to another next of kin. All you need to enforce is that the employee number refers to an existing employee.

Single-column keys can be declared as part of the column definition, which simplifies the syntax and also allows the datatype of foreign key columns to be inherited from the parent.

create table employee 
( employee_no       varchar2(8)  primary key
, family_name       varchar2(40) not null
, given_name        varchar2(40) not null
, address           varchar2(80) not null
, date_of_birth     date         not null
, date_hired        date         not null
, supervisor        references employee(employee_no)
);

create table next_of_kin
( employee_no       references employee (employee_no) not null
, kin_number        varchar2(8)  not null
, name              varchar2(40) not null
, relationship      varchar2(40) not null
, contact_number    varchar2(11) not null
, primary key (employee_no, kin_number)
);

A table can have as many unique constraints as you like, but only one primary key. In your example though, you are trying to define one primary key with two columns, which is allowed (although not needed here). You can also have a foreign key with more than one column, as long as it matches a corresponding primary or unique constraint in the specified table.

Select name of two foreign keys referring to same primary key table

Never use commas in the FROM clause. Period. Write the query as:

SELECT t.log_id, w1.whs_name AS to_warehouse_name, w2.whs_name from_warehouse_name
FROM OWHS w1 JOIN
     Transaction_Log t
     ON w1.whs_id = t.to_warehouse JOIN
     OWHS w2
     ON w2.whs_id = t.from_warehouse;

Although you could also fix the problem by replacing the , with CROSS APPLY that just confuses the logic. You are looking for two simple JOINs.

Foreign Key column mapped to multiple primary keys

Best practice I have found is to create a Function that returns whether the passed in value exists in either of your Messages and Drafts PK columns. You can then add a constraint on the column on the History that calls this function and will only insert if it passes (i.e. it exists).

Adding non-parsed example Code:

CREATE FUNCTION is_related_there (
IN @value uniqueidentifier )
RETURNS TINYINT
BEGIN
IF (select count(DraftId) from Drafts where DraftId = @value + select count(MessageId) from Messages where MessageId = @value) > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

ALTER TABLE History ADD CONSTRAINT
CK_HistoryExists CHECK (is_related_there (RelatedItemId) = 1)

Hope that runs and helps lol

---

Related Topics