Hi,
Should I create table relationships in mysql or LO base?
Thank you
Whatever works.
1 Like
Good answer,
LO didn’t work.
I was able to get a EER diagram in the mysql workbench model screen but could not connect field to field, which makes me wonder…
Do I really need to have relationships?
Maybe i could restrict my employee field, in my employee work orders table to just sql the employee names table?
Tu
What happens when you try? “Does not work” does not include any problem description.
The 1-side of a relation needs to be a primary key.
The n-side of a relation must not include any value that does not exist on the 1-side.
Equal types on both sides.
I’m not sure about Null values on the n-side. Just start with empty tables and add test data later.
When dragging the lines in Base, I would not expect this operation to work with any database driver. Even when working with a “native” database (HSQL or Firebird), this operation may fail for unclear reasons. Restart the office and drag the line from the n-side to the 1-side.
Database creation and modification is easier without Base GUI. MySQL FOREIGN KEY Constraint
1 Like
Hi V,
Some really good tips. I’ll post more detail of LO’s failure once back at office.
The link is very informative! Tu
Depends. Querys and reports you can create without relations. But if you wish the database to check, if an “related” entry exists before storing a reference to another table or prohibit the deletion of a referenced row, then you need relations.
1 Like
For MySQL it might depend of the type of tables you use. MyISAM (old default table) couldn’t manage relations. InnoDB (default here on OpenSUSE) could define relationships.
Direct connection to MySQL/MariaDB of LO will support create relationships without any problem.
1 Like
I definitely need a relationship, unfortunately i can get past 1452 apply error:
Operation failed: There was an error while applying the SQL script to the database.
Executing:
ALTER TABLE dbworkorders.tbworkorders
ADD INDEX employeename_idx (RequestedBy ASC) VISIBLE;
;
ALTER TABLE dbworkorders.tbworkorders
ADD CONSTRAINT employeename
FOREIGN KEY (RequestedBy)
REFERENCES dbworkorders.tbemployeenames (EmployeeName)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (dbworkorders.#sql-17ac_12b, CONSTRAINT employeename FOREIGN KEY (RequestedBy) REFERENCES tbemployeenames (EmployeeName))
SQL Statement:
ALTER TABLE dbworkorders.tbworkorders
ADD CONSTRAINT employeename
FOREIGN KEY (RequestedBy)
REFERENCES dbworkorders.tbemployeenames (EmployeeName)
ON DELETE NO ACTION
ON UPDATE NO ACTION
Thank you
One of the fields has to be a primary key. The foreign key should be the same field type and field length as the primary key. Is “EmployeeName” a primary key?
Does every value in the table, which contains the foreignkey “employeename” exist in the other table with the primary key “EmployeeName” ? If “a foreignkey constraint fails” appears there are entries in the foreignkey, which doesn’t exist in the primary key.
1 Like
The error message indicates that “dbworkorders”.RequestedBy contains a value that does not exist in “tbemployeenames”.“EmployeeName”.
Anyway, you never really want to link text fields! Referential integrity (this is what relations are about) works with integer numbers. In rare cases you may use unambiguous short codes such as currency symbols or country codes USA, UK, AUS, GER, FRA, RUS.
https://forum.openoffice.org/en/forum/download/file.php?id=11250 is a database with a one-to-many relation between persons and animals (each person has one kind of animal) and a many-to-many relation where each person may have many things and each thing may belong to many persons.
Relations in this database work through automatic ID numbers. They serve as automatically added row numbers. They are completely meaningless otherwise.
Every relation connects an integer auto-ID with an integer foreign key. However, you do not see any such numbers in the user interface. No form and no report shows any ID number. They are automatically maintained by the database engine as you add and remove records. In input forms, the IDs are masked by input boxes. An input box shows a human readable text, but it writes a number. It writes the primary key of the other table into the foreign key field of the form’s underlying table.
Referential integrity means that …
- It is impossible to add to a person an animal-ID that does not exist as a primary key in the animal table.
- By default, you can not delete any animal that is referenced by a person. Before you can delete an animal, you’ve got to change the foreign keys in every person owning this animal in order to preserve referential integrity, which does not allow the possession of a non-existing animal.
Names of persons are highly ambiguous. Open my persons table for editing and then open the index designer. It shows 3 indices for that table. Indices accellrate lookups.
One index represents the primary key.
Another one represents the foreign key.
I added a userdefined one consisting of unique forename, surname and birth date. This index makes it impossible to enter duplicate records with same names and birthdates.
1 Like
Hi R & V,
I have two tables:
Parent
Dbworkorders.Tbworkorders (Twelve columns)
[WorkOrderNumber] Int primary key null.
[RequestedBy] varchar(100)
Child
Dbworkorders.Tbemployeenames (One column)
[EmployeeName] varchar(100) primary key null.
This table holds the employee names who can issue work orders.
Goal
When the LO base form user arrives at the
[requestedby] list object, the selection will be limited to the names in the Tbemployeenames table.
Respectfully, tell me how to accomplish this, for everything I’ve tryed has failed.
I’m using mysql/Workbench
Tu !!
Here are some things you can modify to correct most relational problems for any LO db!
Tbl1 (Tbworkorders) should have a unique, not null, primary key column. If column WorkOrderNumber is number entered by a user, or number as text, it should not be used as primary key. A unique primary key integer column is usually named something like “ID” or “workOrderID” and auto assigned by the database, not the user. (auto-increment) If one employee is assigned per workorder, then Tbl1 must include a regular integer column named “employeeID”. (see Tbl2) Else, if multiple employees on one workorder, then see Tbl3 below.
Tbl2 (Tbemployeenames) should also have a unique primary key integer column named “ID” or “employeeID”. So Tbl2 should then have 2 columns. “Name”, “ID”
The relationship between Tbl1 and Tbl2 is now from the one side Tbl2.“ID” or “employeeID” (unique primary key integer) to the many side Tbl1.employeeID (integer).
Tbl3) If multiple employees are to be assigned to a single workorder, then a third table will need to be added: Tbl3 (“Tbemployeework”). 3 columns: “ID” (primary key), “employeeID” (integer), “workOrderID” (integer).
Relate Tbl1.“ID” to Tbl3.“workOrderID” and relate Tbl2.“ID” to Tbl3.“employeeID”.
If there are questions feel free to reply!
1 Like
Attached a sql-code for two tables, one connected to the other by 1:n. Database for this tables is “libretest”.
This will show the right code to connect, but it is easier to define the connection in Base by GUI, as I have done for this example.
libretest.odt (28.0 KB)
Have to change the format. *.txt (or *.sql) isn’t allowed here.
Hi Sky,
I made the changes but auto increment is grayed out for ID columns and could not be changed.
Before I continue…
I think i understand how the two tables will be linked through employeeID, but how will the name field from tb2 fill in tb1’s “RequiredBy” ?
Thank you
Gm,
I’m sorry but I can’t get past the endless mysql/workbench warnings.
I am doing this in mysql/workbench. That is where all future db admin will occur, not in LO. It’s what I have to learn.
Mysql/forums/workbench are slow to answer or don’t answer at all. See my 5/2/24 tkapp request.
I’m going to forget my current dbworkorders and create a new db with two tables.
Dbtest
Tb1
Tb2
Tb1 will have three fields:
[OrderNumber] ]Int, primary, index? , Auto increment(if it will let me)
[ReqNum] Int, primary, Index?, Auto Increment (If…)
[ReqBy] VarChar(100)
Tb2 will have two fields:
[OrderNumber]? Int, primary, index?, Auto Increment(If…)
[ReqBy] VarChar(100)
If this looks like it would relate I’ll proceed. If not please tell me what to change, add or subtract.
Goal:
End user trys to enter a name in tb1 [ReqBy] that is not in table tb2 and gets an error.
Please, if I can have one small success I may be able to someday understand the more complicated relationships.
Thank you
@Tkapp : You could only create one column for autoincrement in a table. What you called “OrderNumber” seems to be the same most people call something like “ID”. So “ReqNum” isn’t a primary key, might be a index, isn’t autoincrement.
First step here: MySQL CREATE TABLE
There is an example for both tables. One table “tasks”, which will only save some content. The other “checklist”, which will get a foreign key from “tasks”.
Next step is described how to create AUTO_INCREMENT.
Have never used MySQL workbench. Used PHPMyAdmin here.
Hi Robert,
I am only doing this as a test.
Creating tables is not my problem, it’s how to get my two small test tables to join in a relationship that will be useful constraint checks.
Can you show me what each of my columns would look like per table?
Please use my column names.
Have a look at the code for the little example I have uploaded as *.odt-file:
ALTER TABLE `Tb1`
ADD CONSTRAINT `tbl_fk_1` FOREIGN KEY (`ReqNum`) REFERENCES `Tb2` (`OrderNumber`);
And all in the tutorial:
2 Likes
Your alter worked!! What a feeling!
I had to change tb2 [OrderNum] to [ReqNum] and references tb2 from OrderNumber to ReqNum… but it joined.
I will continue to study relationships and then move on to the inevitable more questions.
Thanks Everybody!!
Good morning,
It created a eer that looked right on Friday, but when I went into LO this morning it would only constrain the joined ReqNum (request number) column.
Dbtest
Tb1. (Orders)
OrderNum. Int AI PK
ReqNum. Int UN
ReqBy. VarChar100
Tb2 (Names)
ReqNum. Int PK
ReqBy. Varchar100
I reversed engineered to create eer which looked good:
Tb2 ReqNum(many) to Tb1 ReqNum(one)
At Villeroy’s advice (no text joins) I added the ReqNum to join Tb1 to Tb2 but do not need it in my final LO work order form.
I do need Tb1 ReqBy and for it to be constrained to the Tb2 ReqBy.
What am I doing wrong?
Thank you!