I'm new to SQL and trying to make it so that there are two primary keys and one foreign key that references other tables primary key.
I've tried adding the attribute that is a primary key that is missing the table that needs referencing and then making that a foreign key but still getting the message that "number of referencing columns must match references columns".
if there is a solution to what I'm trying to achieve it would be much appreciated.
CREATE TABLE Next_of_Kin
(
Employee_No VARCHAR2(8) 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),
FOREIGN KEY (Employee_No, Kin_Number) REFERENCES Employee(Employee_No)
);
CREATE TABLE Employee
(
Employee_No VARCHAR2(8) NOT NULL,
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 VARCHAR2(40) NULL,
PRIMARY KEY (Employee_No, Supervisor),
FOREIGN KEY (Employee_No,Supervisor)
REFERENCES Employee(Employee_No, Supervisor)
);
CodePudding user response:
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.
CodePudding user response:
I don't know your company, but I'm pretty sure that the employee number is unique. Thus, the primary key of your employee table should be
Employee_No
, without the supervisor.The foreign key in your employee table does not make sense. Remove it.
In your next of kin table, leave the primary key as it is, but make only the employee number a foreign key:
FOREIGN KEY (Employee_No) REFERENCES Employee(Employee_No)