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