-
crhaag
- Posts: 1
- Joined: Wed Jul 23, 2014 12:30 am
Primary or unique constraint required on main table:
Hi all,
New database user here. I am trying to create a basic construction estimator. I was just trying to create my first relationship. I have a customer table and a projects table. I would like to be able to pull the customers name into the projects table. I have brought them both up in the relationships screen, but when I try to drag the field titled «last name» from the customer table to the projects table I get a message stating:
SQL Status: S0011
Error code: -170
Primary or unique constraint required on main table: «Customers» in statement [ALTER TABLE «Projects» ADD FOREIGN KEY («Customer Name») REFERENCES «Customers» («LastName»)]
Any help would be most appreciated
libre office 4.2 — Windows 8
-
Arineckaig
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Primary or unique constraint required on main table:
Post
by Arineckaig » Wed Jul 23, 2014 12:42 pm
I would like to be able to pull the customers name into the projects table
Welcome to the forum.
A Relational Database permits a table to be related to another through the use of its Foreign Key field whose data type must match that of a Primary Key field in the other table. It would appear from the error message that the «LastName» field in the «Customers» table is not defined as a Primary Key field — it has not been set to prevent duplicates by requiring unique values in that field for all records in the table.
In this particular case, it is quite likely that more than one customer could have the same LastName, so that field is not necessarily suitable to meet the requirements of a Primary Key. A common, and perhaps simplest, device would be to include a token «CustomersID» field in a «Customers» table. If this field is defined as an Auto-Increment Integer data type it provides a semi-automatic — simple, safe and suitable — Primary Key. In the «Projects» table the Foreign Key field could then be a matching «CustomersID» whose data type should also be an Integer.
A useful exposition of this and other principles that underpin a Relational Database can be obtained from:
https://wiki.documentfoundation.org/ima … torial.pdf
Please come back if you have any questions or problems with this explanation or with the Tutorial.
When this issue has been resolved, it would help other users of the forum if you add the word — [Solved] — to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
- Печать
Страницы: [1] Вниз
Тема: OpenOffice (Прочитано 3688 раз)
0 Пользователей и 1 Гость просматривают эту тему.
SDrak
На просторах рунета ответа не нашёл, поэтому задаю сюда. В Опен Базе создаю две таблици. После чего хоче сделать связи, но всегда выходит primary or unique constraint required on main table statement. Что я делаю не так?
volgautc
А ключи есть у таблиц?
Без первичного ключа в каждой таблице не будет связи.
Из интереса попробовал без ключа — дало primary or unique constraint required on main table statement. Сделал ключи — связь создаётся.
« Последнее редактирование: 07 Февраля 2010, 16:47:08 от volgautc »
SDrak
СПС, к счатью сам уже допёр. Но вот опять незадача. Мне надо сделать формы даты типа /этот год/-/слудущий год/ , а оффис даёт тока /этот год/-/этот год/. Что делать?
dr.Faust
Сформулировать вопрос по понятнее.
- Печать
Страницы: [1] Вверх
Description
perie_gut
2017-09-28 13:48:37 UTC
Created attachment 136586 [details]
sample
On the 5.3.6.1, it is possible to create a relationship between a primary column and an ordinary column, however, in the 5.4.1, it requires that the both column must be set to primary.
Comment 1
Alex Thurgood
2017-09-28 14:28:23 UTC
@perle_gut : please explain in detailed steps what it is your are attempting to do and provide a sample ODB file with instructions that we could try to follow to reproduce. What kind of DB (embedded hsqldb, firebird, or external db) ? How are you setting up the relationship (GUI, DDL/SQL, or backend instructions to db engine) ? Setting to NEEDINFO.
Comment 2
perie_gut
2017-09-29 09:03:42 UTC
Created attachment 136610 [details]
sample of the db
Here is a sample of the HSQLDB. I was able to create the existing relationship using the stable release... If you are to recreate the entire db on a fresh release, then you will encounter the said error.
Comment 3
Robert Großkopf
2017-09-30 07:34:08 UTC
Have tested this one with OpenSUSE 42.2 64bit rpm Linux and LO Version: 5.4.2.1 Build-ID: dfa67a98bede79c671438308dc9036d50465d2cb CPU-Threads: 4; Betriebssystem:Linux 4.4; UI-Render: Standard; VCL: kde4; Gebietsschema: de-DE (de_DE.UTF-8); Calc: group Deleted the relationship between "PropertyType"."PropertyType" and "PropertyDetails"."PropertyType". Then saved and closed Base and LO. Reopened and set relationship. When drawing from "PropertyDetails"."PropertyType" to "PropertyType"."PropertyType" the fields were connected well. You could save and reopen and the relationship is there. Drawing the other way there doesn't appear 'n' and '1'. You are asked for the properties. If you only confirm this nothing happens. After saving and reopening the relationship has been gone. Same buggy behavior with LO 5.3.6.1, also with all other versions down to 3.6.7.2. Haven't installed any version before, but it seems to be a very old bug. If you change the properties in the dialog for relationship to any action when deleting or updating something the relationship will be created well in every version of LO. Seems to be a bug in the dialog. Could be this isn't the bug the reporter has detected - but this one I could confirm.
Comment 5
Julien Nabet
2017-10-22 19:30:58 UTC
I added traces to compare the 2 ways of adding relationship:
1) From non primary->primary (which works)
ORelationTableView::AddConnection
ORelationTableConnectionData::SetCardinality begin
checkPrimaryKey m_vConnLineData.size()=0
checkPrimaryKey m_vConnLineData.size()=0
ORelationTableConnectionData::Update
checkPrimaryKey m_vConnLineData.size()=1
checkPrimaryKey m_vConnLineData.size()=1
ORelationTableConnectionData::SetCardinality begin
checkPrimaryKey m_vConnLineData.size()=1
checkPrimaryKey m_vConnLineData.size()=1
checkPrimaryKey m_vConnLineData.size()=1
ORelationTableConnectionData::SetCardinality Cardinality::ManyOne
ORelationTableView::AddConnection call addConnection
2) From primary->non primary (which doesn't work)
with:
@@ -61,8 +61,7 @@ ORelationTableConnectionData::ORelationTableConnectionData( const TTableWindowDa
{
m_aConnName = rConnName;
- if ( !m_aConnName.isEmpty() )
- SetCardinality();
+ SetCardinality();
}
ORelationTableView::AddConnection
ORelationTableConnectionData::SetCardinality debut
checkPrimaryKey m_vConnLineData.size()=0
checkPrimaryKey m_vConnLineData.size()=0
So in this last case:
no call to Update -> m_vConnLineData has a size of 0 -> checkPrimaryKey returns always false -> SetCardinality lets m_nCardinality to Cardinality::Undefined
Comment 6
perie_gut
2017-12-11 10:48:04 UTC
Version: 5.4.4.1 (x64)
Build ID: da790616461e15a10c95a80eb8ef8ee7b726c114
CPU threads: 4; OS: Windows 6.19; UI render: default;
Locale: en-US (en_US); Calc: CL
As of the latest version, I still was not able to saved the relationship for the following:
ContractDetails.BargainPurchaseOptionType = BargainPurchaseOption.BargainPurchaseOption
*also for the BargainRenewalOption
and I have tried to manually create the relationship (from insert > relationship) and got the following error
SQL Status: S0011
Error code: -170
Primary or unique constraint required on main table: "BargainRenewalOption" in statement [ALTER TABLE "ContractDetails" ADD FOREIGN KEY ("BargainRenewalOption") REFERENCES "BargainRenewalOption" ("BargainRenewalOption")]
Comment 7
Julien Nabet
2017-12-11 12:51:40 UTC
perie: why did you set this to FIXED ?
Comment 8
Robert Großkopf
2017-12-11 15:41:43 UTC
(In reply to perie_gut from comment #6)
> As of the latest version, I still was not able to saved the relationship for
> the following:
>
> ContractDetails.BargainPurchaseOptionType =
> BargainPurchaseOption.BargainPurchaseOption
>
> *also for the BargainRenewalOption
>
> and I have tried to manually create the relationship (from insert >
> relationship) and got the following error
>
> SQL Status: S0011
> Error code: -170
>
> Primary or unique constraint required on main table: "BargainRenewalOption"
> in statement [ALTER TABLE "ContractDetails" ADD FOREIGN KEY
> ("BargainRenewalOption") REFERENCES "BargainRenewalOption"
> ("BargainRenewalOption")]
Error appears, because "BargainRenewalOption" isn't unique. There are two primary-keys in the table. So how should the database be able to know which of the different rows of "BargainRenewalOption" will be the right?
Comment 9
perie_gut
2017-12-12 02:27:39 UTC
Julien, because of the stupid comment that I have posted earlier. Nevertheless, the issues identified before seams okay already when I tried with 5.4.3 version. I'll just set the ticket open.
Comment 10
Julien Nabet
2018-01-11 19:22:32 UTC
Let's revert to the previous state before the FIXED put by mistake.
Comment 12
Robert Großkopf
2018-04-16 15:38:33 UTC
(In reply to perie_gut from comment #11)
> Created attachment 141394 [details]
> trying to create a relationship to a table with 2 primary keys
>
> Still cannot create a relationship to a table with 2 primary keys
Seems you are trying to create relationships between tables, which contain both more than one primary-key. I have tried this through direct SQL:
1: Primary or unique constraint required on main table: "journal_lines" in statement [ALTER TABLE "accountcode" ADD FOREIGN KEY ("accountcode") REFERENCES "journal_lines" ("accountcode")]
2: Primary or unique constraint required on main table: "accountcode" in statement [ALTER TABLE "journal_lines" ADD FOREIGN KEY ("accountcode") REFERENCES "accountcode" ("accountcode")]
You need a unique-field if you want to create a relationship. Three of of the tables doesn't have a unique-field. The have more than one primary key. HSQLDB doesn't support this.
The only bug here is: The GUI doesn't recognize this relation isn't be allowed. It paints a connection which doesn't show 1:1 or 1:n but this connection couldn't be saved.
Which tables did you try to connect with which relation?
Comment 13
QA Administrators
2019-10-07 03:03:23 UTC
Comment hidden (obsolete)
Dear perie_gut, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Comment 14
QA Administrators
2021-10-07 03:53:21 UTC
Dear perie_gut, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug