Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

org.hibernate.MappingException: Foreign key must have same number of columns as the referenced prima

 
Arthur Khusnutdinov
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello.

I have two tables


And second table


When I have started my application, I got exception:
Caused by: org.hibernate.MappingException: Foreign key (FK_hksv6kkf3cysf5ug30q575tg1:framework_files [filesLocal_id])) must have same number of columns as the referenced primary key (framework_files [linkedObjectName,linkedObjectId,filesLocal_id])
at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:110)

Please advice, how to fix this exception.

I have seen many topics on stackoverflow about this Exception, but I can't understand, how to fix this error in my code.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think the error is really self-explanatory: you have a discrepancy between your foreign key (1 column) and the referenced primary key (3 columns). If your primary key is really a composite key, you'll to create a PK object for your composite key. Otherwise you just need to correct your mapping:

Hope it helps!
Kind regards,
Roel
 
Arthur Khusnutdinov
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:I think the error is really self-explanatory: you have a discrepancy between your foreign key (1 column) and the referenced primary key (3 columns). If your primary key is really a composite key, you'll to create a PK object for your composite key. Otherwise you just need to correct your mapping:

Hope it helps!
Kind regards,
Roel


Hello.
My PK is not composite. I just need to join two tables by PublicSitePortfolioWorks.linkedObjectNameLocal=FrameworkFiles.linkedObjectNameLocal and PublicSitePortfolioWorks.id=FrameworkFiles.linkedObjectId without any PK or FK...
I think, this way is not correct.
I need to solve task, when one work object from PublicSitePortfolioWorks may have many child of files from FrameworkFiles. I have goal to make DB structure with table filesData, where will be stored information about uploaded files, and another tables for another objects.
Some objects can be linked to the filesData table, for example tables of the blog posts, news, etc. Each of these objects must have many linked files. For example, news post can have many pictures. In this case should be used "middle" tables for each type, ie, for news post shuold be 3 tables: NewsTable, NewsToFilesRelationsTable, filesData. It's standart way.
I don't want to make "middle" tables, with relation of the objects to the files records in the filesData table. Another variant - make one table filesDataRelation and store relations data in this table. In this case - what structure of this table must be? This table must have not only ID of the linked object's record, but also it must have row with data type of the object (it may be full name of the object's table).

How to build this way?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Arthur Khusnutdinov wrote:My PK is not composite. I just need to join two tables by PublicSitePortfolioWorks.linkedObjectNameLocal=FrameworkFiles.linkedObjectNameLocal and PublicSitePortfolioWorks.id=FrameworkFiles.linkedObjectId without any PK or FK...
I think, this way is not correct.

If your PK/FK is not composite, why do you need to join 2 columns
 
Arthur Khusnutdinov
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Arthur Khusnutdinov wrote:My PK is not composite. I just need to join two tables by PublicSitePortfolioWorks.linkedObjectNameLocal=FrameworkFiles.linkedObjectNameLocal and PublicSitePortfolioWorks.id=FrameworkFiles.linkedObjectId without any PK or FK...
I think, this way is not correct.

If your PK/FK is not composite, why do you need to join 2 columns



Hm... Perhaps we do not understand each other.
OK, what is correct way to solve this issue?
Note that the link must be on the fields PublicSitePortfolioWorks.linkedObjectNameLocal=FrameworkFiles.linkedObjectNameLocal and PublicSitePortfolioWorks.id=FrameworkFiles.linkedObjectId .
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Assume 2 tables:
  • person with fields id, first_name, last_name
  • address with fields id, person_id, street, city

  • Both tables have a single PK (1 column), so I just have to join on 1 column to link both tables (person.id = address.person_id).

    What about your table structure?
     
    Arthur Khusnutdinov
    Greenhorn
    Posts: 8
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Roel De Nijs wrote:Assume 2 tables:
  • person with fields id, first_name, last_name
  • address with fields id, person_id, street, city

  • Both tables have a single PK (1 column), so I just have to join on 1 column to link both tables (person.id = address.person_id).

    What about your table structure?


    One main table FrameworkFiles. For example: FrameworkFiles.id, FrameworkFiles.name, FrameworkFiles.path, FrameworkFiles.linkedObjectId, etc...
    And many tables what looks like'
    PublicSitePortfolioWorks.id, PublicSitePortfolioWorks.title, PublicSitePortfolioWorks.textOfDescription, etc...
    PublicSitePortfolioWorks1.id, PublicSitePortfolioWorks1.title, PublicSitePortfolioWorks1.textOfDescription, etc...
    PublicSitePortfolioWorks2.id, PublicSitePortfolioWorks2.title, PublicSitePortfolioWorks2.textOfDescription, etc...
    All of them may contain files from FrameworkFiles. If we will link by your variant (person.id = address.person_id), we couldn't get info, from what table this ID. For example, PublicSitePortfolioWorks1.id=FrameworkFiles.linkedObjectId and PublicSitePortfolioWorks2.id=FrameworkFiles.linkedObjectId will produce two records with same ID. Both table may have record with same number, for example, with number 1.
    We need pointer to the table of the object (PublicSitePortfolioWorks1, PublicSitePortfolioWorks2, etc...) in the record in FrameworkFiles table.
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Arthur Khusnutdinov wrote:One main table FrameworkFiles. For example: FrameworkFiles.id, FrameworkFiles.name, FrameworkFiles.path, FrameworkFiles.linkedObjectId, etc...
    And many tables what looks like'
    PublicSitePortfolioWorks.id, PublicSitePortfolioWorks.title, PublicSitePortfolioWorks.textOfDescription, etc...
    PublicSitePortfolioWorks1.id, PublicSitePortfolioWorks1.title, PublicSitePortfolioWorks1.textOfDescription, etc...
    PublicSitePortfolioWorks2.id, PublicSitePortfolioWorks2.title, PublicSitePortfolioWorks2.textOfDescription, etc...

    So if I understand correctly, FrameworkFiles is your main table and is referred to from other tables like PublicSitePortfolioWorks, PublicSitePortfolioWorks1, PublicSitePortfolioWorks2,... Correct?

    Can a record from FrameworkFiles be linked from more than 1 table? Or is each record from FrameworkFiles linked to 1 record from any of the PublicSitePortfolioWorks tables?

    To get a better understanding of your data structure, could you provide some dummy records of tables FrameworkFiles and PublicSitePortfolioWorks, because I for example have no idea about what linkedObjectId is representing.

     
    Arthur Khusnutdinov
    Greenhorn
    Posts: 8
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Roel De Nijs wrote:
    Arthur Khusnutdinov wrote:One main table FrameworkFiles. For example: FrameworkFiles.id, FrameworkFiles.name, FrameworkFiles.path, FrameworkFiles.linkedObjectId, etc...
    And many tables what looks like'
    PublicSitePortfolioWorks.id, PublicSitePortfolioWorks.title, PublicSitePortfolioWorks.textOfDescription, etc...
    PublicSitePortfolioWorks1.id, PublicSitePortfolioWorks1.title, PublicSitePortfolioWorks1.textOfDescription, etc...
    PublicSitePortfolioWorks2.id, PublicSitePortfolioWorks2.title, PublicSitePortfolioWorks2.textOfDescription, etc...

    So if I understand correctly, FrameworkFiles is your main table and is referred to from other tables like PublicSitePortfolioWorks, PublicSitePortfolioWorks1, PublicSitePortfolioWorks2,... Correct?

    Can a record from FrameworkFiles be linked from more than 1 table? Or is each record from FrameworkFiles linked to 1 record from any of the PublicSitePortfolioWorks tables?

    To get a better understanding of your data structure, could you provide some dummy records of tables FrameworkFiles and PublicSitePortfolioWorks, because I for example have no idea about what linkedObjectId is representing.



    One record from FrameworkFiles may be linked with many records from all possible tables.
    I think, that I need in one table FrameworkFilesLinks like
    id int PK,
    fileID int = FrameworkFiles.id ,
    linkedObjectName varchar = PublicSitePortfolioWorks.linkedObjectName ,
    linkedObjectId int = PublicSitePortfolioWorks.id .

    I've attached PNG with schema and you can see, what I mean.

    In this case FrameworkFilesLinks must contain FK FrameworkFilesLinks.fileId = FrameworkFiles.id , right?
    But how to annotate entity PublicSitePortfolioWorks to get set of FrameworkFilesLinks objects? I say about

    1.png
    [Thumbnail for 1.png]
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Let me ask my question once more. To get a better understanding of your data structure, could you provide some dummy records of tables FrameworkFiles and PublicSitePortfolioWorks, because I for example have no idea about what linkedObjectId is representing.
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Arthur Khusnutdinov wrote:I've attached PNG with schema and you can see, what I mean.

    In this case FrameworkFilesLinks must contain FK FrameworkFilesLinks.fileId = FrameworkFiles.id , right?
    But how to annotate entity PublicSitePortfolioWorks to get set of FrameworkFilesLinks objects?

    I can't tell unless I have a crystal clear view on your table structure. Because I still think there's something wrong with your table structure.
     
    Arthur Khusnutdinov
    Greenhorn
    Posts: 8
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Roel De Nijs wrote:Let me ask my question once more. To get a better understanding of your data structure, could you provide some dummy records of tables FrameworkFiles and PublicSitePortfolioWorks, because I for example have no idea about what linkedObjectId is representing.


    Now I understood, that the way through linkedObjectId is not correct. linkedObjectId - it's field id from another table (PublicSitePortfolioWorks, PublicSitePortfolioWorks2, .....).
    Example:
    id | fileName | linkedObjectName | linkedObjectId
    1 | file.txt | PublicSitePortfolioWorks | 1
    2 | file.exe | PublicSitePortfolioWorks1 | 1
    3 | file.dat | PublicSitePortfolioWorks1 | 1
    4 | file2.txt | PublicSitePortfolioWorks | 2
    5 | file2.exe | PublicSitePortfolioWorks1 | 2
    6 | file2.dat | PublicSitePortfolioWorks1 | 3

    If I'll use FrameworkFilesLinks, then fields linkedObjectName and linkedObjectId will be moved from FrameworkFiles to FrameworkFilesLinks. This way will make it possible to link one file from FrameworkFiles to many objects from another tables (PublicSitePortfolioWorks, PublicSitePortfolioWorks2, .....).
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thanks! This gives a much better idea about how you structured your data.

    And what about this table structure?

    FrameworkFiles
    id | fileName
    1 | file.txt
    2 | file.exe
    3 | file.dat
    4 | file2.txt
    5 | file2.exe
    6 | file2.dat

    PublicSitePortfolioWorks
    id | frameworkFiles_id
    1 | 1
    2 | 4

    PublicSitePortfolioWorks1
    id | frameworkFiles_id
    1 | 2
    1 | 3
    2 | 5
    3 | 6

    I can see the purpose of the FrameworkFiles table, but I don't get why you have many PublicSitePortfolioWorks tables. Why is that not simply 1 table? What's the purpose of having seperate/multiple tables with almost exactly the same name?
     
    Arthur Khusnutdinov
    Greenhorn
    Posts: 8
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Roel De Nijs wrote:Thanks! This gives a much better idea about how you structured your data.

    And what about this table structure?

    FrameworkFiles
    id | fileName
    1 | file.txt
    2 | file.exe
    3 | file.dat
    4 | file2.txt
    5 | file2.exe
    6 | file2.dat

    PublicSitePortfolioWorks
    id | frameworkFiles_id
    1 | 1
    2 | 4

    PublicSitePortfolioWorks1
    id | frameworkFiles_id
    1 | 2
    1 | 3
    2 | 5
    3 | 6

    I can see the purpose of the FrameworkFiles table, but I don't get why you have many PublicSitePortfolioWorks tables. Why is that not simply 1 table? What's the purpose of having seperate/multiple tables with almost exactly the same name?


    In this case one record (row) of the PublicSitePortfolioWorks1 will have only one file from FrameworkFiles. In my case it's needed to link many files from FrameworkFiles from one record from PublicSitePortfolioWorks(...).
    For one-to-many linking it good idea to make third table FrameworkFilesLinks, right?
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Arthur Khusnutdinov wrote:In this case one record (row) of the PublicSitePortfolioWorks1 will have only one file from FrameworkFiles. In my case it's needed to link many files from FrameworkFiles from one record from PublicSitePortfolioWorks(...).
    For one-to-many linking it good idea to make third table FrameworkFilesLinks, right?

    Not true! Record with id 1 of PublicSitePortfolioWorks1 links to 2 records of frameworkFiles (ids 2 and 3).

    It all depends on the data stored in PublicSitePortfolioWorks1. If it's an entity on its own, you'll need indeed a join table. If it's just contains some data related to the join (like file_added) you don't need the extra table. That's the reason why I asked multiple times to provide some sample data, so I know how your data is structured and what's inside your tables. That's impossible based on your table names, because I'm not familiar with your business requirements.

    But if your table structure can be compared with the following data structure, you'll need an extra table:
    user: id, name
    permission: id, name
    permission_per_user: user_id, permission_id (the extra table)

    But if your table structure can be compared with this data structure, you don't need the extra table:
    product: id, name, description, unit_price
    order_line: id, product_id, number_ordered
     
    Dave Tolls
    Ranch Hand
    Posts: 2100
    15
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    What Roel is asking is, you have lots of tables with the same structure but slightly different names:
    PublicSitePortfolioWorks1
    PublicSitePortfolioWorks2
    etc

    Why are these not a single table?
    That seems to be the root of your problem.
     
    Arthur Khusnutdinov
    Greenhorn
    Posts: 8
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Roel De Nijs wrote:
    Arthur Khusnutdinov wrote:In this case one record (row) of the PublicSitePortfolioWorks1 will have only one file from FrameworkFiles. In my case it's needed to link many files from FrameworkFiles from one record from PublicSitePortfolioWorks(...).
    For one-to-many linking it good idea to make third table FrameworkFilesLinks, right?

    Not true! Record with id 1 of PublicSitePortfolioWorks1 links to 2 records of frameworkFiles (ids 2 and 3).

    It all depends on the data stored in PublicSitePortfolioWorks1. If it's an entity on its own, you'll need indeed a join table. If it's just contains some data related to the join (like file_added) you don't need the extra table. That's the reason why I asked multiple times to provide some sample data, so I know how your data is structured and what's inside your tables. That's impossible based on your table names, because I'm not familiar with your business requirements.

    But if your table structure can be compared with the following data structure, you'll need an extra table:
    user: id, name
    permission: id, name
    permission_per_user: user_id, permission_id (the extra table)

    But if your table structure can be compared with this data structure, you don't need the extra table:
    product: id, name, description, unit_price
    order_line: id, product_id, number_ordered


    I asked how to implement a mapping between two tables in columns, and in the end we did not discuss it .
    In your case

    PublicSitePortfolioWorks1
    id | frameworkFiles_id
    1 | 2
    1 | 3
    2 | 5
    3 | 6

    in the table PublicSitePortfolioWorks1 (it may have any names - clientsTable, productsTable - without a difference, as it is called) will not 1 record with id 1, but two records. It means, what it will looks like


    PublicSitePortfolioWorks1
    id | frameworkFiles_id | recordName
    1 | 2 | First work
    1 | 3 | First work
    2 | 5 | Second work
    3 | 6 | Third work

    It will duplicate records!


     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Arthur Khusnutdinov wrote:I asked how to implement a mapping between two tables in columns, and in the end we did not discuss it .

    That's normal! First requirement to implement a mapping between 2 tables: have a well-designed table structure. If your table structure isn't ok, it's sometimes simply impossible to implement a mapping. Or the mapping might be much more complex than needed.

    Arthur Khusnutdinov wrote:
    PublicSitePortfolioWorks1
    id | frameworkFiles_id | recordName
    1 | 2 | First work
    1 | 3 | First work
    2 | 5 | Second work
    3 | 6 | Third work

    It will duplicate records!

    Ok! So you'll need a join table.

    FrameworkFiles: id, fileName, fileSize, filePath, fileUrl,...
    publicSitePortfolioWorks: id, mainName, androidName, url,...
    publicSitePortfolioWorks_FrameworkFiles: publicSitePortfolioWorks_id, FrameworkFiles_id

    Now I'm still wondering why you have lots of tables with the same structure but slightly different names like PublicSitePortfolioWorks, PublicSitePortfolioWorks1, PublicSitePortfolioWorks2 and so on. Why are these tables not combined into 1 table? Because for each table, you'll need a join table. So if you have 20 PublicSitePortfolioWorks-like tables, you'll need 20 publicSitePortfolioWorks_FrameworkFiles-like tables as well
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic