1. Georg Lacher
  2. Valentina Studio
  3. Saturday, July 25 2020, 03:40 PM
  4.  Subscribe via email
Hello!
Valentina Studio is able to access my SQL database. I created a diagram with links between the tables. The Data Editor shows the records of the selected tables, but not - as shown in the introduction video - the records in the linked tables. What can be the reason?
Thanks
Georg
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Hello Georg,

Do you see created link in the links list for this table?
I attached screenshot
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
Georg Lacher Accepted Answer
Hello Sergey,

no, there aren't any entries in the links column. It seems, I missed a step. Do you know which one?

Thanks
Georg
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
Sergey Pashkov Accepted Answer
Do you use Valentina Studio Pro?
When you create a link on the diagram it is necessary to apply changes (there is Apply Changes button), but it is available in the Valentina Studio Pro.

Also, there is a way to do in the Valentina Studio Free in the Schema Editor - just select the table and click Create->Link to open Create Link dialog.

As a result the similar script will be executed:

ALTER TABLE `film_actor`
ADD CONSTRAINT `fk_film_actor_actor` FOREIGN KEY ( `actor_id` )
REFERENCES `actor`( `actor_id` )
ON DELETE Restrict
ON UPDATE Cascade;
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Georg Lacher Accepted Answer
Yes, I use Valentina Studio Pro. You're right, the diagram has not been saved properly.
If I try to apply changes, there is an error message: "Error (1075): 42000: "Incorrect table definition; there can be only one auto column and it must be defined as a key".
But I don't know what an "auto column" is.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
Georg Lacher Accepted Answer
Now I understand ... "auto columns" means fields with AUTO INCREMENT.
The error message means that each auto increment field must also be defined as a key.

I checked the tables in the MySQL database, but each auto increment field is also defined als a primary key field. So why do I get this error message?

Then I found an interesting explanation. When creating a 1:many-link in the diagram Valentina Studio adds a new field as primary key field to the many-table. I deleted these new fields because each table already has a primary key field.

The "apply changes" button creates a script. The script begins with lines like this:
DROP INDEX `PRIMARY` ON `Table Name`;

I assume these lines were added because I deleted these fields. Right?

So the tables do not have a primary key field anymore, and this causes the error message.

What do you suggest? Shall I delete the diagram and create a new one and leave the new fields? Or shall I delete the DROP INDEX lines and then apply the changes?

Thank you for your help
Georg
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
Sergey Pashkov Accepted Answer
Yes, maybe start on a new diagram, not necessary to drop an old one.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
Georg Lacher Accepted Answer
Unfortunately the new diagram causes the same error message: "Error (1075): 42000: "Incorrect table definition; there can be only one auto column and it must be defined as a key".

I don't know what's the reason for the error message.

Some fields in the diagram are highlightened in orange colour. Perhaps this is a hint? I checked the links, there is no type mismatch. Maybe some of the 1:many relations are not correct?
Attachments (3)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 7
Sergey Pashkov Accepted Answer
Looks like the Unique flag is removed from the PK fields, but why it happens is still the question.

I recommend discarding unnecessary changes.
You can select PK field and use the context menu to discard changes (see screenshot).
The same for `PRIMARY` index and unique.

Also, there are some FK/PK fields that are Double, is that correct?
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 8
Georg Lacher Accepted Answer
Yes, some of the key fields are Double. Is that a problem?
I'm gonna try to discard unnecessary changes.
Thanks!
Georg
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 9
Sergey Pashkov Accepted Answer
Double values are approximate, it may produce unexpected results in some cases, so generally not recommended. But allowed.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 10
Georg Lacher Accepted Answer
I discovered the reason for these errors. Some of the 1:many relations were not correct. I created a new diagram, now everything is ok with the primary key fields.

But when I try to apply the changes, there is a new error message.

The FOREIGN KEY SQL command does not work. I don't know why. I removed the NULLABLE flag from the foreign key field in the child table, but the error comes again.
Attachments (2)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 11
Sergey Pashkov Accepted Answer
Hello Georg,

So there is some data in these tables, right?
Is that possible that some of the FNr values in the Zuordnung table don't exist in the Filme table?

The following query should find such values if any:

SELECT * FROM Zuordnung WHERE Zuordnung.FNr NOT IN ( SELECT FNr FROM Filme )
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 12
Georg Lacher Accepted Answer
Hello, Sergey.

I found 42 records out of 70.516 records in the Zuordnung table which an unvalid FNr. It's a handmade database, sometimes I discovered double entries in the Filme table and deleted one of them. I tried to adjust the child tables, but obviously sometimes I forgot to do this. So I'm gonna delete these 42 records and try to go on, right?

Thank you!
Georg
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 13
Sergey Pashkov Accepted Answer
Hello Georg,

Yes, delete or change FNr to some existing value from the Filme table (to adjust it later).

As usual, it is recommended to create a backup so if something goes wrong you could return to the working copy.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 14
Georg Lacher Accepted Answer
Hello Sergey,

I deleted the selected records. After that there were some other error messages, and I had to delete other lost records. Finally, the changes were applied successfully.

The next question is: what can I do with this diagram? Now Valentina knows how the tables are connected together. Does this help me to go on, for example to create an input form with subforms?

Thank you for you help?
Georg
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Hello Georg,

Yes, created links (not necessarily using the diagram) can be used to show related records in the subform.

I attached the link to the tutorial which covers this topic.
Also, it is possible to show records in subform even without the link.
References
  1. http://valentina-db.com/docs/dokuwiki/v10/doku.php?id=valentina:products:vstudio:tutorials:forms:lesson2#case_1related_records_using_the_link
Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories