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

How to insert secondary keys

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


Joined: 25 Aug 2004
Posts: 56

PostPosted: Sat Feb 12, 2005 2:10 pm    Post subject: How to insert secondary keys Reply with quote

Hi,
I wonder how I can insert secondary keys as part of the composite primary key of a table.
I am tryin to make a database (OOo 1.9.77) like this:
- table 1: (author ID),(author name) with author ID as primary key.
- table 2: (publisher ID),(publisher name) with publisher ID as primary key
- table 3: (author ID), (publisher ID),(publication title)

Thus, i want to use the primary keys of the first two tables as secondary keys in Table 3.
Is this possible in OOo database? How can I do this -insert secondary keys in a table?

I used to do this in M$ Access, with even the possibility of replacing the secondary keys in table 3 and show this table directly in this format: (author name),((publisher name),(publication title).

I know this is a simple matter for the specialists, but I am only an amateur. If you know how to do this, could you please help me explaining it in simple terms?
Beforehand, Many thanks.
_________________
Xirontask
Back to top
View user's profile Send private message
Lazy-legs
Super User
Super User


Joined: 21 Jul 2004
Posts: 564
Location: Århus, Denmark

PostPosted: Sat Feb 12, 2005 4:08 pm    Post subject: Reply with quote

I don't have access to OpenOffice.org right now, but here is a suggestion off the top of my head.

1. Create a query using the Create Query in Design View command (or something like that).
2. Add all three table to the query.
3. Create a join between TABLE1.AuthorID - TABLE3.AuthorID
4. Create a join between TABLE2.PublisherID - TABLE3.PublisherID
5. Use then the lower part of the query window to choose the desired fields from the three tables (Author name from TABLE1, Publisher name from TABLE 2, and Publication title from TABLE3.
6. Save the query and run it.

Hope this works, Otherwise let me know and I'll take a look at it tomorrow.

Kind regards,
Dmitri
_________________
http://code.google.com/p/writertools/
Back to top
View user's profile Send private message Visit poster's website
xirontask
Power User
Power User


Joined: 25 Aug 2004
Posts: 56

PostPosted: Sat Feb 12, 2005 5:28 pm    Post subject: Reply with quote

Hi, Dmitri,
many thanks for your answer.
Unfortunately, your suggestion cannot work, because before I create the query with the three tables, I need -per definition- to have created TABLE3 within which a field AuthorID whose data must necessarily be inserted from TABLE1 (otherwise it would be no relation between the two tables). In other words, I have to chose the entries in field TABLE3.AuthorID from the entries in field TABLE1.AuthorID

And the same applies to field PublisherID in Tabl3 in relation to Table2.
In M$ Access you have a tab Lookup in design view (lower part), where you can link the two tables by giving the source of data that will act as secondary key (actually, through a query).

This functionality to work with secondary keys is, I think, essential in a database. Thus, I suppose (or rather hope) that I am overlooking something in OOo database.

Anyway, many thanks for your kind answer, again.
_________________
Xirontask
Back to top
View user's profile Send private message
acolvin
OOo Enthusiast
OOo Enthusiast


Joined: 14 Dec 2004
Posts: 152

PostPosted: Sun Feb 13, 2005 1:00 am    Post subject: Reply with quote

You can add the constraints relationships through the relationship function under the tools menu

Hope this is what you want. Note not all databases may support all constraint options.

Andrew
Back to top
View user's profile Send private message AIM Address MSN Messenger
Lazy-legs
Super User
Super User


Joined: 21 Jul 2004
Posts: 564
Location: Århus, Denmark

PostPosted: Sun Feb 13, 2005 2:41 am    Post subject: Reply with quote

Quote:
Unfortunately, your suggestion cannot work, because before I create the query with the three tables, I need -per definition- to have created TABLE3 within which a field AuthorID whose data must necessarily be inserted from TABLE1 (otherwise it would be no relation between the two tables). In other words, I have to chose the entries in field TABLE3.AuthorID from the entries in field TABLE1.AuthorID


Aha! I see what you mean. You are right, the suggested technique won't work, but I'm sure it is possible to find a workable solution. I'll take a closer look at it. I'm also new to OOo Base, so it will be a good exercise Very Happy

Kind regards,
Dmitri
_________________
http://code.google.com/p/writertools/
Back to top
View user's profile Send private message Visit poster's website
xirontask
Power User
Power User


Joined: 25 Aug 2004
Posts: 56

PostPosted: Sun Feb 13, 2005 9:49 am    Post subject: Reply with quote

Hi Dmitri and Andrew,

thanks for your kiind answers.
Andrew, in relation to your suggestion: the problem is not with the design of a query; it is with the previous step: the design of the tables.
In the "design table" window, there is no way to introduce a restriction indicating that the entry in a field of a record has to be chosen from the entries of the primary keys from other tables (thus, in TABLE3, an entry of an author has to be necessarily one of the entries in authorID of TABLE1, even with the same format). This is a condition to be able to design queries; but I I do not have any problem with the restrictions of the queries (not at least in this database): I do not even need to design a query, because my master table, TABLE3, has all elements I want (my real database contains a lot more tables. TABLE1 has its own primary key, MasterID, which I forgot to include in my description of the database. But this does not change the problem).

I designed and worked with such a bibliography database in M$ Access. Then, I converted it to an OOo database (through ODBC). And it is fine: I can enter data in my new OOo database. BUT, the tables have now no relation between them, because the primary keys cannot be entered as secondary keys (the result is, among others, that even if I wanted, I could not make a query -with or without restrictions- of more than 1 table). And this is not fine.

The database that is provided as example in OOo, Bibliography, is actually no database, but a single table. No need thus of secondary keys there. No complication, no problem thus.

I can only hope that I have overlooked something and am wrong or that the coming buildups, release versions, etc., will handle these things.

Thank you for your help; it helps me a lot by compelling me to think about my forgotten knowledge.

Kind regards,

Xirontask
_________________
Xirontask
Back to top
View user's profile Send private message
xirontask
Power User
Power User


Joined: 25 Aug 2004
Posts: 56

PostPosted: Sun Feb 13, 2005 10:52 am    Post subject: Reply with quote

A small correction to my previous message: when I said
"TABLE1 has its own primary key, MasterID", I meant actutally TABLE3.
For the rest, this does not change the problem.
_________________
Xirontask
Back to top
View user's profile Send private message
acolvin
OOo Enthusiast
OOo Enthusiast


Joined: 14 Dec 2004
Posts: 152

PostPosted: Sun Feb 13, 2005 11:15 am    Post subject: Reply with quote

tools/relationships define the relationships between tables and not queries. It allows you to link fields and define constraints between your tables especially the one that allows you to define the many-many which is what I think you are trying to do

Try this define your 3 tables as you describe
Go to tools/relationships...
drag relationships from your author table to your join table
do the same to the other
edit the relationships to define the rules
Enter your data which will now be constrained to your relational constraints

If this is not what you want then I have completely misunderstood your email.
Back to top
View user's profile Send private message AIM Address MSN Messenger
xirontask
Power User
Power User


Joined: 25 Aug 2004
Posts: 56

PostPosted: Sun Feb 13, 2005 1:11 pm    Post subject: Reply with quote

Hi Acolvin,
you wrote,
Quote:
tools/relationships define the relationships between tables and not queries. It allows you to link fields and define constraints between your tables especially the one that allows you to define the many-many which is what I think you are trying to do

Try this define your 3 tables as you describe


I am not trying to create relationships between queries (it would be nonsense, of course).

What you are describing is how to create relationships between tables, by editing the joins between the tables (which can be done by right clicking on the line of the join). This defines the relationships between the tables WITHIN a query (but not the tables themselves). Thus, you are defining how to make a query (which can only be done when you have already defined and created the tables). But I do not need any query, then no join.
What I want is to define the tables themselves, or more precisely, the fields of the tables. Only then, if I needed (but I do not need it) I could design the queries by establishing the relationships and joins between the already, previously created tables. The relationships I am trying to create are not query relationships (joins). They are relationships at a lower level.

My aim is that the raw data in the original tables is consistent: that, for instance, when I enter an author in TABLE3.AuthorID the author is exactly the same one I have entered in TABLE1.AuthorID (TABLE1 is the author's table). Even more: I want to block the direct manual entries in the field TABLE3.AuthorID, so that I can enter the author in this field only by choosing (for instance through a pop menu in this field) among the previously ones entered in field TABLE1.AuthorID. This is previous to any query and cannot be efficiently done manually (by copying and pasting between tables).

Many thanks again.

[/quote]
_________________
Xirontask
Back to top
View user's profile Send private message
acolvin
OOo Enthusiast
OOo Enthusiast


Joined: 14 Dec 2004
Posts: 152

PostPosted: Mon Feb 14, 2005 12:21 am    Post subject: Reply with quote

My understanding is that the tools/relationship function defines the relationship between the tables. It allows you to define constraints and cascade updates/deletes at a table level and not queries. These are then used when defining a query.

I agree with you that it would be nice to define the foreign key fields by selecting the fields from the original table and for this to also create the relationship. May be we should add this as a suggestion.

Andrew
Back to top
View user's profile Send private message AIM Address MSN Messenger
Lazy-legs
Super User
Super User


Joined: 21 Jul 2004
Posts: 564
Location: Århus, Denmark

PostPosted: Mon Feb 14, 2005 1:10 am    Post subject: Reply with quote

Quote:
I agree with you that it would be nice to define the foreign key fields by selecting the fields from the original table and for this to also create the relationship. May be we should add this as a suggestion.


I agree. Why not to file an enhancement issue in IssueZilla?

Kind regards,
Dmitri
_________________
http://code.google.com/p/writertools/
Back to top
View user's profile Send private message Visit poster's website
xirontask
Power User
Power User


Joined: 25 Aug 2004
Posts: 56

PostPosted: Mon Feb 14, 2005 4:21 am    Post subject: Reply with quote

Hi,
I have just entered issue 42719
http://qa.openoffice.org/issues/show_bug.cgi?id=42719

I realize though that I have entered it as a defect issue type instead of enhancement or feature. Maybe you know how to change the issue type.

Thanks for all your help.
_________________
Xirontask
Back to top
View user's profile Send private message
Lazy-legs
Super User
Super User


Joined: 21 Jul 2004
Posts: 564
Location: Århus, Denmark

PostPosted: Mon Feb 14, 2005 4:25 am    Post subject: Reply with quote

Just add a note to the issue, explaining that this should be an enhancement.

Kind regards,
Dmitri
_________________
http://code.google.com/p/writertools/
Back to top
View user's profile Send private message Visit poster's website
xirontask
Power User
Power User


Joined: 25 Aug 2004
Posts: 56

PostPosted: Mon Feb 14, 2005 6:51 am    Post subject: Reply with quote

OK, Dmitri. I've done it.
Regards,
_________________
Xirontask
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