Database relationship

k3nny · General User Joined: 25 Mar 2011 Posts: 7

If I want to add a foreign key. Do I have add it as a primary key?

therabi · Super User Joined: 01 Sep 2010 Posts: 562

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

k3nny · General User Joined: 25 Mar 2011 Posts: 7

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")]

k3nny · General User Joined: 25 Mar 2011 Posts: 7

I thought the invoice would need a address so I put a userID.

dacm · Super User Joined: 07 Jan 2010 Posts: 734

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 + Migrating 'Embedded databases'

k3nny · General User Joined: 25 Mar 2011 Posts: 7

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

k3nny · General User Joined: 25 Mar 2011 Posts: 7

Can I add multiple tables in a form?

keme · Posted: Mon Mar 28, 2011 5:03 am Post subject:

k3nny · General User Joined: 25 Mar 2011 Posts: 7

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.

barikan · General User Joined: 09 May 2011 Posts: 6

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")]

Sliderule · Posted: Mon May 09, 2011 8:40 pm Post subject:

barikan:

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

barikan · General User Joined: 09 May 2011 Posts: 6

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

Sliderule · Posted: Mon May 09, 2011 9:06 pm Post subject:

Very good.

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

Glad you have found your answer.

Sliderule

barikan · General User Joined: 09 May 2011 Posts: 6

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.

Sliderule · Posted: Tue May 10, 2011 6:25 am Post subject:

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: