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.
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.
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 …
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.
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!
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:
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!
Please show a screenshot of Base → Tools → Relationships. Don’t know why you won’t create the relations in this tool.
Sorry R, but it won’t let me upload, paste the screenshot. Too big.
Can this really be that hard?
I’m I the first to want a employee to enter there name in a workorder form and it be checked that it is also in the employee name table?
Examples are great at joining int to int columns, which I’ve done, but I don’t need that number, I need their name.
Maybe if the user could enter their employee number and it would automatically fill the adjacent name column/Object would the join number have value.
Tu
So you had created the realtionships. If you don’t want to fill all this by the Integer for the foreign key you need a form. In a form a foreignkey should be filled by a listbox. A listbox will show the names and insert the foreign key in the datasource. Please have a look at the Base Guide for this.
Hi R,
But what is the reason for the foreign key?
Couldn’t I have made a list box with macro, that would’ve restricted the user to my employee names table, bypassing all the trouble of learning foreign key constraints?
Without defining such a key you could delete all the names after you have inserted the key-values in the employee-table. Your data will be corrupted, because there are numbers in your table but names for this numbers won’t exist.