[Solved] Relationship design error message

[DesF]

OOo 2.3.1 on WinXP Pro SP2

Hello,

I am learning about database design using a textbook (Mastering Database Design) by working through an example of a hotel reception design.

I see the following error message when attempting to create one-to-many relationships between adjacent Room Occupancy fields:

Primary or unique constrainst required on the main table: "Room Price" in statement [ALTER TABLE "Room" ADD FOREIGN KEY ("Room Occupancy") REFERENCES "Room Price" ("Room Occupancy")]

Note, I get a similar error message for creating EnSuite relationships.

I managed to resolve an earlier problem where Base appeared not to retain saved table details of a primary key commprising of two fields (Base selected only one of the fields as the key when I reopened the table for editting). I was able to unpack the .odb file, edit the script and rezip to a new .odb database file.

I was wondering if the error message can similarly be resolved by including ...ADD CONSTRAINT SYS_FK_nnn FOREIGN KEY... statements to the script file. If so, what SYS_FK value should I use.

Regards,
Des.

[DrewJensen]

ah - this is a bit of a tricky one.

ALTER TABLE "Room" ADD FOREIGN KEY ("Room Occupancy") REFERENCES "Room Price" ("Room Occupancy")

Room has a compound primary key ( "Room Occupancy", "En Suite" ) - which means of course that any combination of "Room Occupancy" and "En Suite" must be unique right. But it does not in and of itself mean that values in the single column "Room Occupancy" are unique.

So look at the FK declaration again it is saying that for every value in "Room.Room Occupancy" it references exactly one record in table "Room Price", and it does that by matching on the value in "Room Price.Room Occupancy". That can not currently be guaranteed just by the PK constraint on the "Room Price" table, since the value in the single column "Room Occupancy" does not denote uniqueness.

You have two solutions available, and the error message points you to one of these.

1. Add a unique constraint on the column "Room Price".Room Occupancy" ( which you can do in the SQL window with the appropriate DDL command )
2. Add the column "En Suite" to the table "Room", so that the FK relation can use both to guarantee uniqueness

Which is the correct answer - I do not know without understanding more about what the conceptual schema is trying to capture. Can you talk through what is being captured in this structure?

Edit: Also - you need not and should not be altering the embedded script file in the odb file - rather you can add these kinds of constraints by executing the correct ALTER TABLE commands in SQL window of the Base application - Tools>SQL will open this window. Or in the case of a uniqueness you can also do this by creating a unique index on a column, this is done in the Base table design window, look for the correct toolbar button to open the index dialog box when the table definition is open for editing.

[DesF]

Hello Drew & all,

Your caution about editing the script file has been noted. Being new to the way OOo Base works and database designing I was attempting to find a work-around to the problems I have encountered.

Following your response I think that I have realised my error in creating the relationship between the Room and Room Price tables. Although the EnSuite field was already in the Room table I was attempting to create a two-field relationship involving both Room Occupancy & EnSuite fields by dragging & dropping each field separately between the adjacent tables.

The two-field relationship was successfully created when I entered the information in the Relationship dialogue box.

Thanks for your response & best regards.
Des.