LO Base: Multiple Primary Keys: Means 3 or more

[Dycius]

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 "Year" ("Year" SMALLINT NOT NULL PRIMARY KEY)

CREATE CACHED TABLE "Month" (
	"Year_ID" SMALLINT NOT NULL
	,"Month" TINYINT NOT NULL
	,PRIMARY KEY (
		"Year_ID"
		,"Month"
		)
	,CONSTRAINT SYS_FK_59 FOREIGN KEY ("Year_ID") REFERENCES "Year" ("Year") ON DELETE CASCADE ON UPDATE CASCADE
	)

CREATE CACHED TABLE "Day" (
	"Year_ID" SMALLINT NOT NULL
	,"Month_ID" TINYINT NOT NULL
	,"Day" TINYINT NOT NULL
	,PRIMARY KEY (
		"Year_ID"
		,"Month_ID"
		,"Day"
		)
	,CONSTRAINT SYS_FK_68 FOREIGN KEY (
		"Year_ID"
		,"Month_ID"
		) REFERENCES "Month" (
		"Year_ID"
		,"Month"
		) ON DELETE CASCADE ON UPDATE CASCADE
	)

CREATE USER SA PASSWORD ""

GRANT DBA
	TO SA

SET WRITE_DELAY 60

Thanks,
Jon

[oweng]

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.