I am trying to open a mariadb database and establish a relationship between two tables. Doesn’t work! Here is error code.
Cannot add or update a child row: a foreign key constraint fails (“medical”."#sql-alter-43f-14b0",
CONSTRAINT “#sql-alter-43f-14b0_ibfk_1” FOREIGN KEY (“id”) REFERENCES “entry” (“id”)) ./connectivity/source/drivers/mysqlc/mysqlc_general.cxx:119.
Means nothing to me. I have other info but it comes as png image. How do you add images to this reply?.
Thanks for help. R
Some value(s) in table “medical” have no corresponding “id” value in table “entry”. Referencial integrity means, that you can’t have any values medical.id that do not exist in entry.id. You can’t create a foreign key, unless this condition is met.
The topic title is misleading. Your database is connected and works as intended.
Don’t Understand!!
This works just fine.
SELECT * FROM mdata INNER JOIN entry ON entry.id=mdata.id;
Using Mariadb - NOT LO Base.
your error msg is not about select
SELECT * FROM mdata LEFT JOIN entry ON entry.id=mdata.id WHERE entry.id IS NULL;
shows the inconsistent records in table “entry”. They are the reason why you can’t create a foreign key.
In LibreOffice’s “parsed mode” (not “direct mode”):
SELECT "mdata".* FROM "mdata" LEFT JOIN "entry" ON "entry"."id"="mdata"."id" WHERE "entry"."id" IS NULL;
And Base does not “understand” every possible SQL-statement. In this cases use the rightmost icon in the toolbar of the window, where you can create queries in SQL–mode to switch to “direct sql mode”, where Base simply passes the command to the database without interfering…
Thank you Velleroy and Wanderer. I found 3 NULL records in mdata and deleted them.
I don’t get an error code now and I get the relationship line between the tables. I will now, hopefully, learn now how to use LO Base.
R
This problem has nothing to do with Base. You are working with MariaDB through Base. Most relational databases of the past 4 decades enforce referencial integrity. You can not impose a new rule (one-to-many relation) if existing data violate the new rule.
Likewise you can not make a column mandatory (not null) if there are any rows with missing values in that column. And you can not convert a column type to a new type if any value in that column is not convertible.