| View previous topic :: View next topic |
| Author |
Message |
Anamorph
Newbie
Joined: 24 Apr 2012
Posts: 4
Location: Kampala, Uganda
|
 Posted: Tue Apr 24, 2012 10:47 am Post subject: Can't set up a RELATIONSHIP between two tables |
 |
|
Hi,
I'm having trouble setting up a relationship between two of my tables.
I've got a table called "classes" that lists classes my school offers, and it has one column called "term" and another called "year". "Term" is either 1, 2 or 3, while year can be any year.
The second table is called "termdates", and it lists start and finish dates for each term/year pair. Columns are "term", "year", "start", and "finish". For example, term 1 of 2012 started on February 11, 2012, and term 3 of 2012 starts on Sept. 17, 2012.
In "termdates", "term" and "year" together make up the unique primary key.
I want to set up a relationship between the term and year of the classes table, and the term and year in the termdates table. But when I try to set up that relationship in Tools>Relationships, Base gives the following error:
| Code: | Primary or unique constraint required on main table: "termdates" in statement
[ALTER TABLE "classes" ADD FOREIGN KEY ("term")
REFERENCES "termdates" ("term")] |
But "term" and "year" together are the primary keys in "termdates", and need to be, since it's the term and year together that make each row in that table unique.
So what do I need to do to fix this?
-----
Also is there an SQL command that will display the definition of a table? I'd like to be able to read what's going on at the SQL level instead of simply trusting the icons in the Table "Edit" windows.
Thank you in advance,
Robert Orenstein |
|
| Back to top |
|
 |
dacm
Super User
Joined: 07 Jan 2010
Posts: 734
|
| Posted: Tue Apr 24, 2012 11:56 pm Post subject: Re: Can't set up a RELATIONSHIP between two tables |
|
|
Welcome Robert,
| Anamorph wrote: |
In "termdates", "term" and "year" together make up the unique primary key.
| Code: | | ...REFERENCES "termdates" ("term")] |
|
Interesting...because as you can see the error reports only "term" as the primary key...which is likely repeated in the table.
| Anamorph wrote: | | Also is there an SQL command that will display the definition of a table? |
Assuming the HSQL database engine:
Create Query in SQL View...
| Code: | | SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_PRIMARYKEYS" ORDER BY "TABLE_NAME", "KEY_SEQ" |
Foreign keys:
| Code: | | SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_CROSSREFERENCE" ORDER BY "FKTABLE_NAME", "KEY_SEQ" |
Cached Public Table Definitions:
| Code: | | SELECT "A"."TABLE_TYPE", "A"."HSQLDB_TYPE", "A"."TABLE_NAME", "B"."COLUMN_NAME", "B"."TYPE_NAME", "B"."COLUMN_SIZE", "B"."COLUMN_DEF" AS "Default Value" FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES" AS "A", "INFORMATION_SCHEMA"."SYSTEM_COLUMNS" AS "B" WHERE "A"."TABLE_SCHEM" = 'PUBLIC' AND "A"."TABLE_NAME" = "B"."TABLE_NAME" AND "A"."HSQLDB_TYPE" = 'CACHED' ORDER BY "A"."TABLE_TYPE", "A"."TABLE_NAME", "B"."ORDINAL_POSITION" |
-- all code courtesy Sliderule I'm sure 
| Anamorph wrote: | But "term" and "year" together are the primary keys in "termdates", and need to be, since it's the term and year together that make each row in that table unique.
So what do I need to do to fix this? |
Well, you can always add a dedicated ID column with INTEGER data-type and AutoValue as the sole primary key. If you try this with the Base GUI just make sure you SAVE after each step in the Table Design View.
Or use SQL :
Remove the key(s) from your existing column(s) and then add a new column as the Primary Key with AutoValue:
| Code: | | ALTER TABLE "TableName" ADD COLUMN "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) |
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Migrating 'Embedded databases' |
|
| Back to top |
|
|
Anamorph
Newbie
Joined: 24 Apr 2012
Posts: 4
Location: Kampala, Uganda
|
| Posted: Wed Apr 25, 2012 12:20 pm Post subject: |
|
|
Thanks, DACM. I do see some anomalies in my setup now. I'll be looking it over in detail tomorrow and will post results and any further questions.
Thank you again,
Robert |
|
| Back to top |
|
|
Anamorph
Newbie
Joined: 24 Apr 2012
Posts: 4
Location: Kampala, Uganda
|
| Posted: Tue May 01, 2012 11:38 am Post subject: |
|
|
[REMOVING THIS POST IN FAVOR OF THE EDITED ONE BELOW. SORRY!]
Last edited by Anamorph on Tue May 01, 2012 12:12 pm; edited 2 times in total |
|
| Back to top |
|
|
Anamorph
Newbie
Joined: 24 Apr 2012
Posts: 4
Location: Kampala, Uganda
|
| Posted: Tue May 01, 2012 11:41 am Post subject: Re: trouble with Relationships |
|
|
| dacm wrote: | | Welcome Robert |
Hi DACM.
| dacm wrote: | | Anamorph wrote: |
In "termdates", "term" and "year" together make up the unique primary key.
| Code: | | ...REFERENCES "termdates" ("term")] |
|
Interesting...because as you can see the error reports only "term" as the primary key...which is likely repeated in the table. |
Running the query you suggested for the primary keys provides the following:
| Code: | TABLE_CAT TABLE_SCEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
PUBLIC termdates term 1 SYS_PK_51
PUBLIC termdates year 2 SYS_PK_51 |
... so apparently "term" and "year" together DO make up the primary key. Why the discrepency between the data in the table and the error message? What do I need to do about this?
| dacm wrote: | | Anamorph wrote: | | So what do I need to do to fix this? |
Well, you can always add a dedicated ID column with INTEGER data-type and AutoValue as the sole primary key. If you try this with the Base GUI just make sure you SAVE after each step in the Table Design View.
(sql equivalent removed) |
I see how this fixes things in the database, but the cost seems high for data entry. The nice thing about the current scheme is that we can just enter the current term and year for each class without having to remember any special codes. If we have to enter the code for each class's term/year manually, it's going to be painful.
Or am I misunderstanding what this fix would do for us, or perhaps misunderstanding how we'd use it properly?
----------------------------------------
Thanks for your help. I have about seven years of experience with INGRES/SQL from about 15 years ago, but haven't touched SQL since. And this is my first time using Base.
Thanks again,
Robert Orenstein
[EDIT: clarified question midway through] |
|
| Back to top |
|
|
Billyray
OOo Enthusiast
Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio
|
| Posted: Tue May 01, 2012 4:20 pm Post subject: |
|
|
I have a couple of tables that are related by the combination of two fields which make up a primary key also.
However the Database engine needs to have a unique record identifier key for EACH table. Therefore, you just need to add an autoincrement field as the PRIMARY key TO SATISFY the database engine (like dacm suggested). Your own combo field key will still work the same. You don't have to do ANYTHING to maintain this primary key since it is an autoincrement field. You don't even need to display it on forms etc.
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
