CODEWITHSUNDEEP
postgresql
Netcfmx
Netcfmx

Reputation: 121

PostgreSQL foreign keys multiple keys on single table

I can imagine that it is unlikely that this question has never been answered. However, I have not found it, so in any case here is my problem:

I am migrating from MySQL.

I am getting an error when I try to create a table with one single data field with 2 foreign keys referencing another table.

My goal is to simply have a phone directory for employees.

The directory table must reference the employee id as well as the phone number type.

I get this error when trying to do so:

ERROR: There is no unique constraint matching given keys, for referenced table: Employees!

Here is my current sql dump:

-- ----------------------------
-- Table structure for EmployeeGroups
-- ----------------------------
DROP TABLE IF EXISTS "public"."EmployeeGroups";
CREATE TABLE "public"."EmployeeGroups" (
"Id" int8 DEFAULT nextval('"EmployeeGroups_Id_seq"'::regclass) NOT NULL,
"EmployeeGroup" varchar(255) COLLATE "default"
)
WITH (OIDS=FALSE)
;

-- ----------------------------
-- Table structure for Employees
-- ----------------------------
DROP TABLE IF EXISTS "public"."Employees";
CREATE TABLE "public"."Employees" (
"Id" int8 DEFAULT nextval('"Employees_Id_seq"'::regclass) NOT NULL,
"EmployeeGroupId" int8 DEFAULT 
nextval('"Employees_EmployeeGroupId_seq"'::regclass) NOT NULL,
"FirstName" varchar(255) COLLATE "default",
"LastName" varchar(255) COLLATE "default",
"CivicNumber" int4,
"CivicNumberAlias" int4,
"StreetName" varchar(255) COLLATE "default",
"City" varchar(255) COLLATE "default",
"PostalCode" varchar(255) COLLATE "default",
"UserAcessLevel" int8 DEFAULT 
nextval('"Employees_UserAcessLevel_seq"'::regclass) NOT NULL,
"Username" varchar(255) COLLATE "default",
"Password" varchar(255) COLLATE "default",
"Timestamp" date
)
WITH (OIDS=FALSE)
;


-- ----------------------------
-- Table structure for PhoneNumberType
-- ----------------------------
DROP TABLE IF EXISTS "public"."PhoneNumberType";
CREATE TABLE "public"."PhoneNumberType" (
"Id" int8 DEFAULT nextval('"PhoneNumberType_Id_seq1"'::regclass) NOT NULL,
"PhoneNumberType" varchar(150) COLLATE "default" NOT NULL
)
WITH (OIDS=FALSE)   
;


-- ----------------------------
-- Table structure for SystemAcessLevel
-- ----------------------------
DROP TABLE IF EXISTS "public"."SystemAcessLevel";

CREATE TABLE "public"."SystemAcessLevel" (
"Id" int8 DEFAULT nextval('"SystemAcessLevel_Id_seq"'::regclass) NOT NULL,
"AcessLevel" varchar(255) COLLATE "default" NOT NULL
)
WITH (OIDS=FALSE);

Here is my error generating statement:

CREATE TABLE "public"."NewTable" (
"Id" serial8 NOT NULL,
"EmployeeId" int8 NOT NULL,
"PhoneNumberTypeId" int8 NOT NULL,
"PhoneNumber" varchar(16) NOT NULL,
PRIMARY KEY ("Id"),
CONSTRAINT "phone_fk_emp_id" FOREIGN KEY ("EmployeeId")  
REFERENCES "public"."Employees" ("Id") ON DELETE NO ACTION ON UPDATE 
CASCADE,
CONSTRAINT "phone_fk_type_id" FOREIGN KEY ("PhoneNumberTypeId")          
REFERENCES "public"."PhoneNumberType" ("Id") ON DELETE NO ACTION ON 
UPDATE     CASCADE
)
WITH (OIDS=FALSE)
;

Upvotes: 1

Views: 491

Answers (2)

Netcfmx
Netcfmx

Reputation: 121

As it turns out the problem was identifying the EmployeeGroupId field that was referenced in the Employees table as a second primary key created a conflict when adding the EmployeeDirectory table referencing the same foreing key as a second primary key of the Employees.GroupId field.

In other word seems like PostgreSQL considers second primary key as unique identifiers creating a one to one relationship therefore it considers it as an error to have an additional table referencing the same identifier as a foreing key stated as a second primary key, and please correct me if I am wrong since not an expert when it comes to PostgreSQL.

This however is accepted by Mysql; this is why I was having a hard time figuring out this error.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246403

Every column (or set of columns) that you reference in a foreign key must have a primary key or unique constraint on it.

Otherwise, how could you identify which row the foreign key points to?

Upvotes: 0

Related Questions