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

Database relationship
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
k3nny
General User
General User


Joined: 25 Mar 2011
Posts: 7

PostPosted: Fri Mar 25, 2011 9:53 am    Post subject: Database relationship Reply with quote

If I want to add a foreign key. Do I have add it as a primary key?
Back to top
View user's profile Send private message
therabi
Super User
Super User


Joined: 01 Sep 2010
Posts: 562

PostPosted: Fri Mar 25, 2011 10:06 am    Post subject: Reply with quote

No. There are in fact time that you can not add FKs as PKs, when using two or more FKs as an example.
_________________
OOO v3.3.0 & LO v3.4beta on Ubuntu 10.10 and Win7
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
k3nny
General User
General User


Joined: 25 Mar 2011
Posts: 7

PostPosted: Fri Mar 25, 2011 5:27 pm    Post subject: Reply with quote

I get an error when connecting System User UserID to Invoice UserID.

SQL Status: 23000
Error code: -177

Integrity constraint violation - no parent 1, table: Invoice in statement [ALTER TABLE "Invoice" ADD FOREIGN KEY ("UserID") REFERENCES "System User" ("UserID")]

Back to top
View user's profile Send private message
k3nny
General User
General User


Joined: 25 Mar 2011
Posts: 7

PostPosted: Fri Mar 25, 2011 5:29 pm    Post subject: Reply with quote

I thought the invoice would need a address so I put a userID.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Fri Mar 25, 2011 6:40 pm    Post subject: Reply with quote

If you already have data in the Tables, it may be that you're trying to connect a NULL FK to a non-NULL-able PK or visa-versa.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
k3nny
General User
General User


Joined: 25 Mar 2011
Posts: 7

PostPosted: Sun Mar 27, 2011 9:38 am    Post subject: Reply with quote

What can I do so the orderID on the second table: customer payment gets updated?



Back to top
View user's profile Send private message
k3nny
General User
General User


Joined: 25 Mar 2011
Posts: 7

PostPosted: Sun Mar 27, 2011 10:54 am    Post subject: Reply with quote

Can I add multiple tables in a form?
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Mon Mar 28, 2011 5:03 am    Post subject: Reply with quote

k3nny wrote:
Can I add multiple tables in a form?
Yes you can, but it can be a little tricky.

IIRC: You must use the form navigator where the "subform" containers are visible and you can create a subform for each dependent level.
Assign correct data source (table) to each subform.
In the form navigator, move each control to the correct subform level and make sure the correct data source (table field) is assigned to the control.

I'm pulling this from memory right now, and it's been a while since I used it, so I may have slipped on the details. Others with more experience may come to your rescue (-::
Back to top
View user's profile Send private message
k3nny
General User
General User


Joined: 25 Mar 2011
Posts: 7

PostPosted: Mon Mar 28, 2011 7:21 am    Post subject: Reply with quote

When I done the Form Navigator part. I had to press 'More Controls' on the 'Form Controls' toolbar, (to show another toolbar) press 'Table Control' then make a table and choose the table I added on Form Navigator.
Back to top
View user's profile Send private message
barikan
General User
General User


Joined: 09 May 2011
Posts: 6

PostPosted: Mon May 09, 2011 8:01 pm    Post subject: foreign key issue Reply with quote

Hi
Reading the thread here, it seems that you had a similar "foreign key" issue. I have several tables (mostly containing some information but need to be updated) in OOBase. I imported them from Excel (pasting data columns and declining the option of adding PKs at the time of import). When establishing relationships, I only use PK for few tables. For the remainder, I want to connect tables with foreign keys. This was something I did a couple of years ago in OOBase and worked fine back then. Now, I cannot figure out how to add a foreign key. Any help will be greatly appreciated.

Running OO 3.3.0 on Mac 10.6.7

PS. I tried this trick but it did not work (in Execute SQL Statement window):
[ALTER TABLE "Provinces" ADD FOREIGN KEY ("Province") REFERENCES "Sites" ("Province")]
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon May 09, 2011 8:40 pm    Post subject: Reply with quote

barikan:

You said ( I removed the leading and trailing brackets since they should NOT be present ):

barikan wrote:
PS. I tried this trick but it did not work (in Execute SQL Statement window):


Code:
ALTER TABLE "Provinces" ADD FOREIGN KEY ("Province") REFERENCES "Sites" ("Province")


I think, if I am understanding you correctly, you want your Table "Site", field "Province" to only contain 'valid' values, that exist in the table "Provinces", the field "Provinces", therefore, it should probably be ( furthermore, it does not have leading nor trailing brackets ):

Code:
ALTER TABLE "Site" ADD FOREIGN KEY ("Provinces") REFERENCES "Provinces" ("Provinces")


Also, please note . . . very important . . . per HSQL documentation:

HSQL Documentation Alter Table: http://www.hsqldb.org/doc/guide/ch09.html#alter_table-section wrote:

This will fail if for each existing row in the referring table, a matching row (with equal values for the column list) is not found in the referenced tables.


I hope this helps, please be sure to let me / us know.

Sliderule
Back to top
View user's profile Send private message
barikan
General User
General User


Joined: 09 May 2011
Posts: 6

PostPosted: Mon May 09, 2011 9:03 pm    Post subject: Reply with quote

Hi Sliderule,

Thank you for replying –and correcting the statement–. You are right: I have an über table called "Sites" (with a PK) and I also use several small tables that contain one kind of data (such as provinces, site types, etc). I am not an expert in dbase but from what I understand, I can connect small tables with the major tables using a foreign key because the information in these small tables are contained in major tables anyway (I hope I am right in this). I just want to have more options to query data.

I appreciate your final comment about why this operation may be failing since the Provinces table has some values that do not exist in the Sites table right now. So, this may be the issue. I will remove them and give it another try.

Thank you so much!

Bulent
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon May 09, 2011 9:06 pm    Post subject: Reply with quote

Very good.

I was not certain about which of your tables contained the unique primary key.

Glad you have found your answer.

Sliderule
Back to top
View user's profile Send private message
barikan
General User
General User


Joined: 09 May 2011
Posts: 6

PostPosted: Mon May 09, 2011 9:25 pm    Post subject: Reply with quote

I deleted the names in table Provinces that do not exist in table Sites and imported that table into OO Base. Then, using SQL command tool, I typed:

ALTER TABLE "Sites" ADD FOREIGN KEY ("Province") REFERENCES "Provinces" ("Province")

However, I am getting:

Primary or unique constraint required on main table: "Provinces" in statement [ALTER TABLE "Sites" ADD FOREIGN KEY ("Province") REFERENCES "Provinces" ("Province")]

I am not exactly sure what this means.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue May 10, 2011 6:25 am    Post subject: Reply with quote

barikan:

The above statement means . . . the way you have written the ALTER command . . . it says, your table "Provinces" has a Primary Key ( to make it unique ) of field "Province" . Furthermore, the use of FOREIGN KEY, for referential integrity and 'speed' . . . the field "Provinces"."Province" must be that Primary Key.

Which table ( "Sites" or "Provinces" ) has the Primary Key of the field "Province" ?

Which table ( "Sites" or "Provinces" ) has the field that that you want to make sure is valid ?

If, table "Provinces" contains a Primary Key of field "Province" , and, your table.field "Sites"."Province" must be in the other table . . . the SQL command would be:

Code:
ALTER TABLE "Sites" ADD FOREIGN KEY ("Provinces") REFERENCES "Provinces" ("Province")


Sliderule
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
Goto page 1, 2  Next
Page 1 of 2

 
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