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

(Solved) Question about primary key and table relationship!

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


Joined: 26 Apr 2007
Posts: 46
Location: Netherlands

PostPosted: Thu Sep 06, 2007 5:17 am    Post subject: (Solved) Question about primary key and table relationship! Reply with quote

Hi,
I have two tables from OOcalc, the "company table" with company name and adress, the other with comapny name, people belong to that company, position, etc.
I would like to import them into database. so, I did as follows; I copy and paste it in table section of the base, when aksed about create the primary keys, I would like to use the company name as primary keys, but it said the company name has been already set as primary key, so, I have to use the ID as primary key.
the same happens for the other table.
Then, i would like to create a one-many table relationship between the company table and the other with company name as the ralation.
However, it is not possible, it said " primary or unique constraint required on main table: company in statement... alter table.. add foreign key.
Can anyone help me with this, since I need the table relationship in order to create a form so that I can both import new data and click on the company name, then all the person belong to that company would appear.
thanks very much!


Last edited by bluec22 on Tue Sep 11, 2007 1:58 am; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Sep 06, 2007 9:23 am    Post subject: Reply with quote

Just a suggestion: Let it create a new primary key and set it to "auto value". The field will get a sequence of numbers without meaning except beeing uique identifiers. It will create a new unique number for each new record automatically. If you want to ensure that no name occurs twice, add another unique index on the name field. If you have surnames and fornames separeted, you can create a unique index on both fields.
The company table should get a new primary index as well.
When you got the data into the tables you need to run an UPDATE query to sync all foreign keys "Company_ID" with the new primary keys in table "Companies". Then you should be able to create the relation between "Companies"."ID" and "Persons"."CompanyID".
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 07, 2007 12:15 pm    Post subject: Reply with quote

OK, I think I found my way.
Prelude
No matter what kind of source you have (spreadsheet, csv, other database engine), you've got to deal with consistancy and uniqueness when you import data into Base. For small amounts of data, a spreadsheet may be a useful tool to perform some tests on uniqueness and consistancy of csv and alike.
Two common problems:
1. It seems to be impossible to reuse existing primary keys with auto-increment. When you set an imported field to auto-increment, it will be created newly from scratch, which almost certainly gives wrong relations to other tables. So we'll import temporary tables with their old keys and corresponding foreign keys, prepare blank new tables with new auto-incrementing primary keys and populate the new tables by INSERT statements.
2. The import wizzard has problems with dates. It can handle ISO-dates properly. A date in csv should read like "1999-12-31". When you import dates from a spreadsheet they should be formatted likewise (even string values will do). Otherwise you get conversion errors (12/31/1999) or an offset by two days. Same issue may occur with csv, which I did not try.
The spreadsheet source I used:
I prepared a simple spreadsheet with two tables "Persons" and "Companies". Both tables could have many more fields, describing persons and companies, but for simplicity I created just a few fields to demonstrate known issues with dates and a 1-n relation (1 company with many persons).
11 companies. The names are created by =$B$1&" "&$A2, C_ID is a manually typed list in random order.
Code:

C_ID   Company Name
4   Company Name 4
10   Company Name 10
2   Company Name 2
1   Company Name 1
5   Company Name 5
8   Company Name 8
9   Company Name 9
3   Company Name 3
6   Company Name 6
11   Company Name 11
7   Company Name 7

30 Persons
Code:

Forname   Surname   Birth   Company_ID
Forname 1   Surname 1   1971-06-04   7
Forname 2   Surname 2   1970-10-19   5

created by following formulae:
Forname: =A$1&" "&ROW()-1
Surname: =B$1&" "&ROW()-1
Birth: =RANDBETWEEN(20000;30000) formatted as ISO date "YYYY-MM-DD"
Company_ID: =RANDBETWEEN(1;10)
Some tests in the spreadsheet:
Test if each Person.Company_ID has a corresponding Companies.ID
F2: =N(MATCH($D2;$Companies.$A$2:$B$13;0)) copied down along the list.
Minimum should be >0. Zeroes indicate no match.
Test if "Companies" have unique "C_ID"s: =COUNTIF($A$2:$A$12;$A3) copied down along the list. Max and Min should be 1.
Formatting in spreadsheets may hide the real cell values!
A cell having a large floating point number 39332,25 may be formatted to show the date portion only (07/09/2007) or the time portion only (06:00).
A cell may be formatted as Boolean. All numbers <>0 are shown as TRUE.
If in doubt, you should test for min and max, use filters, formulae and all the Calc-tools in order to get a consistant database. It would not be the first spreadsheet that turns out to be an error-prone mess.

By the way: You may be interested in my add-on "SpecialCells", which is a good tool to detect cluttered data types and formatting on large, "organically grown" spreadsheets: http://www.ooomacros.org/user.php#221020

The target database (embedded hsqldb):
Import of temporary tables:
Create a blank new database document of default type hsqldb.
Copy each data range and paste into the tables-container of the database (right-click>Paste...). Import data and definitions without primary key as "calc_Persons" and "calc_Companies". We'll use them read-only without primary key.
Preparing the real ones:
Create new empty table "Persons" in design view:
"ID" BIGINT, Auto:Yes, IDENTITY, set Primary Key
"Forname" VARCHAR, required:Yes, lenght:50
"Surname" VARCHAR, required:Yes, lenght:50
"Birth" DATE, required:No
"Old C_ID" BIGINT, Auto:No, required:Yes (old foreign key of Companies in Calc)
"Company_ID" BIGINT, Auto:No, required:Yes (foreign key of Companies to be created)

Create new empty table "Companies" in design view:
"ID" BIGINT, Auto:Yes, IDENTITY, set Primary Key
"Company Name" VARCHAR, required:Yes, lenght:50
"Old_ID" BIGINT, Auto:No Required:Yes
Add new unique index "Old_C_ID" on "Old_ID" in order to make the import fail if the values in "calc_Companies" are not unique.

Added a 1-n relation between "Companies"."ID" and "Persons"."Company_ID". No problem, since both are empty until now.

I added as many properties as possible to the new, empty tables, so we don't have to change "living structures" later.

What to transfer from temporary to real table?
Helping queries:
Created 2 queries in design view and copied the SQL strings into a text editor:
"Query1":
Code:
SELECT "Company Name", "C_ID" FROM "calc_Companies"

"Query2":
Code:
SELECT "calc_Persons"."Forname", "calc_Persons"."Surname",
"calc_Persons"."Birth", "Companies"."ID" FROM "Companies", "calc_Persons"
WHERE ( "Companies"."Old_ID" = "calc_Persons"."Company_ID" )


Populating the real tables:
Populate "Companies" from "calc_Companies", based on "Query1"
Code:
INSERT INTO "Companies" ("Company Name", "Old_ID")
SELECT "Company Name", "C_ID" FROM "calc_Companies"


Populate "Persons" from "calc_Persons", based on "Query2"
Code:
INSERT INTO "Persons" ("Forname", "Surname", "Birth", "Company_ID")
SELECT "calc_Persons"."Forname", "calc_Persons"."Surname",
"calc_Persons"."Birth", "Companies"."ID" FROM "Companies", "calc_Persons"
WHERE ( "Companies"."Old_ID" = "calc_Persons"."Company_ID" )


Clean up:
Finally you may want to remove column "Old_ID" with index "Old_C_ID" and the temporary tables "calc_Persons" and "calc_Companies"
Back to top
View user's profile Send private message
bluec22
General User
General User


Joined: 26 Apr 2007
Posts: 46
Location: Netherlands

PostPosted: Tue Sep 11, 2007 1:56 am    Post subject: Reply with quote

Hi Villeroy,
Just get back to work.
Thanks very much for your solution and the time you spent on that. Really appreciate it.
Cheers.
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