PostgreSQL foreign keys multiple keys on single table

Question

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)
;

"I can imagine this is unlikely that this question has never been answered" stackoverflow.com/… — user330315
– user330315, Commented Jan 28, 2015 at 11:54
It looks like you don't have a primary key on employee id, that would also fix this error. Other tables don't have a primary key either. — Frank Heikens
– Frank Heikens, Commented Jan 28, 2015 at 12:02
Unrelated but: you should avoid quoted identifiers ("PhoneNumber") . They create much more trouble in the long run than they are worth it. — user330315
– user330315, Commented Jan 28, 2015 at 12:05
Since I am new to postgree I have been reliying on navicat for sql statements and have been gui'ing it :) but your comment has been heard i will try your quoteless identifiers and see how it goes... Thanks! — Netcfmx
– Netcfmx, Commented Jan 28, 2015 at 14:31

S-Man · Accepted Answer · 2018-09-26 12:47:08Z

1

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.

edited Sep 26, 2018 at 12:47

S-Man

24k9 gold badges51 silver badges78 bronze badges

answered Jan 29, 2015 at 9:03

Netcfmx

1215 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2018-09-23 12:26:31Z

0

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?

answered Sep 23, 2018 at 12:26

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

PostgreSQL foreign keys multiple keys on single table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related