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

Unable to create relationships with tables

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


Joined: 07 Jul 2005
Posts: 61

PostPosted: Wed Sep 07, 2005 11:41 am    Post subject: Unable to create relationships with tables Reply with quote

I'm using OO 1.9.125 on windoze but this problem had been consistent throughout the 2.0 beta's on either windoze or linux.

In order to join 2 spreadsheets, I save them as a csv, then copy and paste into the table portion of base. I go through the import function and let base set up a unique ID for me for each table. I can then open, manipulate and view the tables, but when I try to join them in a relationship using a common field, say "part number" to join, I get this error message:

Error while connecting to the data source.

Primary or unique constraint required on main table:
"CompanyMovement" in statement [ALTER TABLE "Pricing" ADD FOREIGN KEY ("part number") REFERENCES "Company Movement" ("Part #")

When I go back to M$ Access, I don't have this problem.

Any help or answers would be greatly appreciated.

The same thing happens if I bring the tables in as either an OO sheet or Excel.

Thanks,

Don
[/i]
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed Sep 07, 2005 11:55 am    Post subject: UNIQUE constraint not the same as UNIQUE index Reply with quote

This is a case where a unique index and a unique constraint are not the same thing.

I created a little test with two table
parts ( id, partNum )
man ( id, manNum )

I added a unique index to parts.partNum and tried to create a FK to man.manNUm...nope.

I then open the sql window and execute this.

Code:

        alter table "parts" add constraint "uq_part" unique ("partNum" )


Now open the relationship window and drag parts.parNum to man.manNum...yup it works fine.

HTH

Drew Jensen
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
sookietex
Power User
Power User


Joined: 16 Jul 2003
Posts: 76
Location: NYC

PostPosted: Fri Sep 09, 2005 4:43 am    Post subject: unable to create relationships with tables no icon found Reply with quote

Hello all, below are Question i'd previously posted and initial responses received. i'm re-posting in hopes of someone who may not have seen my first posts having further suggestions as to a solution to my confusion. TIA

Quote:
To access this command...
Open database - Context Menu of a Table Container - Relations
This function is only available if you are working with a relational database.


new to creating databases and am trying to create a relational database. wading my way through the openoffice.org 1.1.4 help file and i'm in the tables section; relations/relations design window. problem is i am unable to access the new relationship icon or the relation design command [it does not appear in the context menu of my table]. in my unsuccessful attempt to create a relational database the only way i have been able to access the insert tables icon is via the query tab, but at this point i am not schooled as to how to create a query so of course i get a message saying "error while connecting to the datasource, nothing has been selected" even though the table i am working with appears in the design window/table dropdown. is this because the openoffice.org Base program needs to be installed to perform this function [the help file does not say if this is the case] or is it possible to do this in openoffice.org 1.1.4 and i'm just ignorant as to how to perform the function.
sorry if this is a RTFM question [i have and am still confused Embarassed ] TIA for any help

VeniVidi wrote:
I'm sorry if this sounds obvious. You do have more than one table? You need to have more than one to make a relationship between the others.

Gerry Confused


Hi Gerry, no apologies needed---no question is too obvious for me with regard to this topic Smile i do have more than one table but they are in different databases [the individual databases have more than one table]. hope this answer helps you to help me Wink
thanks again to all
_________________
"Choose well, for your choice is brief, yet endless"~Goethe
Back to top
View user's profile Send private message Visit poster's website
sookietex
Power User
Power User


Joined: 16 Jul 2003
Posts: 76
Location: NYC

PostPosted: Thu Sep 15, 2005 5:21 pm    Post subject: Primary Keys and other database issues Reply with quote

o k, for anyone else who like me may have been struggling with this i found an answer at 'issuezilla' even though the answer turned out to be [presently] no Rolling Eyes

http://www.openoffice.org/issues/show_bug.cgi?id=54168

at the moment, no voting allowed. maybe after admins realize that not everyone is willing to take the beta plunge en masse to 2.0 that will change.
_________________
"Choose well, for your choice is brief, yet endless"~Goethe
Back to top
View user's profile Send private message Visit poster's website
kabing
OOo Enthusiast
OOo Enthusiast


Joined: 25 Apr 2007
Posts: 179

PostPosted: Wed Sep 12, 2007 6:44 am    Post subject: Reply with quote

This post answered my initial question about Unique constraints, but now I have another question. Is it necessary to create a unique index before creating a unique constraint?
Back to top
View user's profile Send private message
kabing
OOo Enthusiast
OOo Enthusiast


Joined: 25 Apr 2007
Posts: 179

PostPosted: Wed Sep 12, 2007 7:44 am    Post subject: Reply with quote

Never mind...I experimented and discovered that a unique index does not appear to be required for creating the relationship.

I would be interested in the difference, though.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Sep 14, 2007 10:46 am    Post subject: Reply with quote

kabing wrote:
Never mind...I experimented and discovered that a unique index does not appear to be required for creating the relationship.

I would be interested in the difference, though.


SELECT "Employees" FROM "Contacts", "Companies"
WHERE "Employees"."Company_ID"="Companies"."ID"

Now, if your "Companies"."ID" has duplicate entries "1" the query can not succeed because it can not decide to which company the employee belongs. The unique constraint is violated.
A unique index on "ID" will prevent duplicates and thus violation of unique constraints.
Back to top
View user's profile Send private message
kabing
OOo Enthusiast
OOo Enthusiast


Joined: 25 Apr 2007
Posts: 179

PostPosted: Fri Sep 14, 2007 11:10 am    Post subject: Reply with quote

Thanks for the answer!

kabing
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