[Solved] Relationship between tables error

Creating tables and queries

[Solved] Relationship between tables error

Postby topan » Thu Jul 22, 2010 11:43 pm

I've been trying to establish a relationship between two tables based on alphanumeric ID numbers [e.g i203 or jf(307)] that are set as text[VARCHAR] in their tables. The first table has unique ID alphanumeric ID numbers, and the other table has these IDs and associated sample dates/values.

Table1 holds:
ID (primary Key)
IDNumber
Group
Age
BirthDate

The other holds
Key (Primary Key)
IDNumber
Sample Date
SampleValue

I dragged/dropped data from Calc spreadsheets into Base to import them, and I've alternately tried letting Base create a primary key or creating one myself. Everything seems to be okay in the tables, but I'm trying to create a 1:many relationship between the two and I keep getting the error:

"Primary or Unique Constraint required on main table"

I've looked through some past posts and I can't figure out what's going wrong (but I'm new with Base). Any advice would be appreciated.

Thanks--
Teague
Last edited by topan on Fri Jul 23, 2010 5:02 am, edited 1 time in total.
OpenOffice 3.2.1 on MacOS 10.6
topan
 
Posts: 2
Joined: Thu Jul 22, 2010 11:15 pm

Re: Relationship between tables error

Postby keme » Fri Jul 23, 2010 12:42 am

A relationship in Base can be one-to-one or one-to-many. This means that at least one of the tables needs the joined fields to have a constraint to ensure it's unique within the table. Furthermore, I believe the only constraint that satisfies Base is to make the field a primary key. (Haven't tested that lately, though.)

If your "other" table will only have one record with each particular IDNumber, drop the "Key" field from that table and make field "IDNumber" the primary key. That should allow relationships.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: [Solved] Relationship between tables error

Postby topan » Fri Jul 23, 2010 5:03 am

Thanks Keme. Completely sorted it out -- it seems that I did everything but delete the old primary key field when changing over to the unique IDNumber.
OpenOffice 3.2.1 on MacOS 10.6
topan
 
Posts: 2
Joined: Thu Jul 22, 2010 11:15 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest