Ask Your Question
0

SQL Status: S0011

asked 2015-12-27 21:52:49 +0200

Cardecs gravatar image

Hi, I am trying to setup a DB for our Church's Event management.

When I try to set a one-to-many relationship between two tables, which has multiple field unique keys, I get the above error message.

"Primary or unique constraint required on main table: "Reservations" in statement [ALTER TABLE "SessionMaster" ADD FOREIGN KEY ("EventID","SessionID") REFERENCES "Reservations" ("EventID","SessionID") ON UPDATE CASCADE ]"

Table "Reservations" has unique key "EventID","SessionID","MemberID" while Table "SessionMaster" has Unique key "EventID","SessionID". I also added non-unique indexes "EventID" and "EventID,SessionID".

Please help

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2015-12-28 15:30:54 +0200

peterwt gravatar image

Reading your question I am interpreting it that the Table Reservations has an unique Primary Key consisting of the fields EventID, SessionID, and MemberID and you want to link EventID in Reservations to EventID in SessionMaster. You want the 1 side of the Table relationship to be EventID in Reservations. As you have EventID, SessionID, and MemberID as a combined Primary Key you could have these Field values, in that order, 1, 1, 1 and 1, 2, 1. This would satisfy the unique constraint but EventID on its own would not be unique so that would not create the 1 side of the relationship.
I think that is what the error "Primary or unique constraint required on main table ...." is saying. If you make EventID the unique Primary Key in reservations and an unique Index with the 3 fields you will ensure that you will get the 1 side of the relationship. Otherwise you will get a n to n relationship.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-12-27 21:52:49 +0200

Seen: 494 times

Last updated: Dec 28 '15