It is saying that the records are imported successfully but it is not populating the Foreign Keys of the relationship like when just inserting a new record. So instead of importing into the table it is generating new tables in my database named for example: coreplugs__import_1205213181_260914
The coreplugs__import__xxxxx table is just a temporary table to store the import data until you have previewed it and approved it. I have since changed the way the import works so that it no longer creates these tables (it just stores them in temporary text fields that are automatically deleted).
Should the Import CSV automatically complete the FK's?
If you import the records via the relationship (i.e. click on the relationship tab then click "import") then it should automatically populate foreign keys.
Could you double check over my relationships and see if there is any problem?
Well, upon further inspection of your relationship definition:
[Core_Plugs]
CorePlugs.Well_idWell = "$idWell"
CorePlugs.Sample_idSample = Sample.idSample
I do have some questions. So this relationship is defined in the "Well" table. So when you are importing the Sample records through this relationship, the Well record already exists. So it is the CorePlugs record that is not getting populated in the import. Correct?
Are there any required fields in the CorePlugs table? These could be preventing records from being inserted. -- actually in reviewing the schema for CorePlugs that you posted, is the idCorePlugs field auto_increment? If it is not, then that would be the problem.
Also in a related issue. Sometime I want to be able to just add a record directly into the child table CorePlugs but i cant get it to obtain the next PK from Sample.idSample to fill in the FK CorePlugs.Sample_idSample. This automatically happens when adding via a Well because of the WELL relationship.ini as below.
Isn't the CorePlugs <-> Sample a one-to-one relationship? If it is one-to-one, then all bets are off when it comes to importing records into it. (How can you import more than one record into a one-to-one relationship?)
I tried using the following in CorePlugs.ini and it works great but if I add a CorePlugs via a Well then it runs this script twice creating 2 new Samples: This also seems like a dodgy way to do something xataface already does via a relationship.
Well... one-to-one relationships aren't really covered by Xataface relationships, so you'll need to do something special for this anyways. I generally try to steer clear of one-to-one relationships in my database design because they are icky (I usually just merge them into a single table, because its easier).
One way to de-ickify things is to figure out which record is a child of the other. Is the Sample record a child of the CorePlugs record or vice versa. It makes things easier if the FK field is part of the child record. That way you can pass the parent's ID to the child when the record is being created. (This is assuming that you create the records lazily - i.e. don't create the child until you need it).
Identifying which is the parent and which is the child also allows us to avoid loopy logic in our triggers. I.e. if the parent creates the child record - the child should not also create the parent.
I don't have the whole picture here but it seems that the difficult lies in the one-to-one nature of the relationship that lies outside of the scope of Xataface relationships - so we don't really know which is the parent and which is the child (and hopefully they're not peers).
My guess is that CorePlugs is the parent? If this is the case, I change teh structure so that the Sample table has a FK to coreplugs and remove the FK from the Coreplugs table. If you're not at liberty to change the structure, then... there is a way to make it work... You just need to closely analyze the processes to make avoid loopy logic.
Hope this helps a little.
-Steve
Posted: Sun Mar 02, 2008 7:12 pm
