SQL Status: S0011

[Cardecs]

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

[peterwt]

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.