OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Can't set up a RELATIONSHIP between two tables

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Anamorph
Newbie
Newbie


Joined: 24 Apr 2012
Posts: 4
Location: Kampala, Uganda

PostPosted: Tue Apr 24, 2012 10:47 am    Post subject: Can't set up a RELATIONSHIP between two tables Reply with quote

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
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Apr 24, 2012 11:56 pm    Post subject: Re: Can't set up a RELATIONSHIP between two tables Reply with quote

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...

    Primary keys:
    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 Wink


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 + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Anamorph
Newbie
Newbie


Joined: 24 Apr 2012
Posts: 4
Location: Kampala, Uganda

PostPosted: Wed Apr 25, 2012 12:20 pm    Post subject: Reply with quote

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
View user's profile Send private message
Anamorph
Newbie
Newbie


Joined: 24 Apr 2012
Posts: 4
Location: Kampala, Uganda

PostPosted: Tue May 01, 2012 11:38 am    Post subject: Reply with quote

[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
View user's profile Send private message
Anamorph
Newbie
Newbie


Joined: 24 Apr 2012
Posts: 4
Location: Kampala, Uganda

PostPosted: Tue May 01, 2012 11:41 am    Post subject: Re: trouble with Relationships Reply with quote

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
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Tue May 01, 2012 4:20 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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