Ask Your Question
0

LO Base: Multiple Primary Keys: Means 3 or more. [closed]

asked 2013-06-06 12:31:57 +0200

Dycius gravatar image

updated 2013-06-07 03:12:19 +0200

oweng gravatar image

Hi all,

I'm trying to work on a database where some tables are best described by two or three primary keys. The trouble comes in trying to set up the relationships. A lot of times I get an error saying that a field needs to be unique. Well, it can't be unique, which is why I have three primary keys. Then sometimes when I do manage to set up a relationship, using the editor and manually selection the two tables and all the fields for each table and then selecting update cascade, it connects them, but no relationship numbers are present, do I don't know if it things 1 to 1 or 1 to many or 0 to 0, or what. Is there a really good easy to follow guide for setting up relationships with multiple primary keys? Or would someone be able to help with my database structure.

I tried to think of an easy to relate to example. The best I can come up with is a calendar, so I tried this as practice in Base instead of working with my complicated DB. The table Year, can be uniquely identified by a Year_ID, since the years don't repeat. However, the table month, can't be identified by just month, because the numbers 1 through 31 are repeated many times, but a compound key of ("Year","Month") would make that table unique. Then the table Day would only be unique with ("Year","Month","Day") as a primary key. When I link them, The Year PK in the Year Table is linked to the Year PK in the Month Table as a 1 to Many, which is right because a year will have many months, but that month with that id can only belong to one year. However, when I link the month and Day, I add the relationship manually, select the two tables, for the Month table I select Year and Month PKs, and for the Day table, I select Year and Month PKs. Then I select the Update Cascade radio button. When I click okay, I get two lines between the two tables, but there are no numbers.

Not the greatest example, but thought it simple enough to try.

EDIT: Here's the Base created SQL for the example I gave above:

SET DATABASE COLLATION "Latin1_General"

CREATE SCHEMA PUBLIC AUTHORIZATION DBA

CREATE CACHED TABLE ...
(more)
edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-02 03:01:58.486469

1 Answer

Sort by » oldest newest most voted
1

answered 2013-06-06 12:53:51 +0200

oweng gravatar image

updated 2013-06-07 03:27:04 +0200

The expression "some tables are best described by two or three primary keys" presumably refers to a compound key made from three fields. Whether this is natural or surrogate in your context is unclear - taking your Year example I would say natural. The expression "the table month, can't be identified by just month, because the numbers 1 through 31" I don't understand as values 1-31 would appear to be days rather than months.

Personally I would never use natural keys unless there was a compelling reason. This Stack Overflow Q/A offers excellent advice about keys and how best to approach them. Link is courtesy @frofa from here.

EDIT: I reformatted your example SQL for greater clarity and I can see now that the 1-31 reference was for the Day rather than Month table. I still think that you need to demonstrate why three tables (Year, Month, Day) would be necessary rather than a single Calendar table with a surrogate primary key and which contains year, month, day, and so on, detail. You may have a good reason, but it is not clear. Greater clarity in this area will help others determine how best to assist you with your query.

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-06-06 12:31:57 +0200

Seen: 1,910 times

Last updated: Jun 07 '13