Ask your Symfony questions! Pay money and get answers fast! (more info)

Fixture for a table when the primary key is also a foreign key? Symfony

  • SOLVED

Hello,

I'm building (or trying to while learning how symfony works) a site with my dev environment in Symfony 1.4, with MSSQL 2008 R2, PHP 5.2.17 and IIS 7 on Windows Server 2008 R2. I designed my schema so that there's a table (Asset) that has a one-to-many (EDIT: one-to-one not one-to-many) relationship to another table (VirtualComputer). VirtualComputer's foreign key which points to the id of Asset is also it's primary key. So my schema.yml looks like the following (edited for brevity):

AssetType:
actAs: { Timestampable: ~ }
columns:
type_name: { type: string(20), notnull: true, unique: true }

Asset:
actAs: { Timestampable: ~ }
columns:
asset_name: { type: string(255), notnull: true, unique: true }
type_id: { type: integer, notnull: true, default: Unknown }
relations:
AssetType: { onDelete: SET DEFAULT, local: type_id, foreign: id }

VirtualComputer:
columns:
asset_id: { type: integer, primary: true, unique: true, notnull: true }
hostname: { type: string(64) }
relations:
Asset: { onDelete: CASCADE, local: asset_id, foreign: id }


So I tried to make a fixture for this scenario. The fixture for the first two tables AssetType and Asset work just fine.It looks like the following:

AssetType:
virtualcomputer:
type_name: Virtual Computer
Asset:
virtualcomputerone:
asset_name: Virtual Computer One
AssetType: virtualcomputer


Then I tried to add the last table, VirtualComputer where the asset_id field is both primary key and foreign key to the Asset Table. It looks like the following

VirtualComputer:
VirtualComputerOneComp:
Asset: virtualcomputerone
hostname: Virtual Machine One


I add that part of the fixture to the same fixture yaml file as the first part of the fixture. I added it to the end of the file though. When I try data-load however I get the following

c:\qaserver2>php symfony doctrine:data-load
>> doctrine Loading data fixtures from "C:\qaserver2\data/fixtures"





SQLSTATE[HY000]: General error: 10007 Cannot insert the value NULL into column
'asset_id', table 'qaserver.dbo.virtual_computer'; column does not allow nulls.
INSERT fails. [10007] (severity 5) []. Failing Query: "INSERT INTO [virtual_com
puter] ([hostname], [architecture]) VALUES ('Virtual Machine One', 'x86_64')"


Why in this case is it not working? It should be supplying the id of virtualcomputerone written earlier in the fixture as the primary key for VirtualComputerOneComp. I'm using the same method of pointing VirtualComputerOneComp (in VirtualComputer) to virtualcomputerone (in Asset) that I used to point virtualcomputerone (in Asset) to virtualcomputer (in AssetType). The only difference is that in this case, the relation is with the child table's primary key. Is there a syntax that will make this scenario work?

Now if I explicitly state the asset_id in the fixture for VirtualComputer like so

VirtualComputer:
VirtualComputerOneComp:
asset_id: 1
hostname: Virtual Machine One


I get this error instead so I know that doesn't work:

c:\qaserver2>php symfony doctrine:data-load
>> doctrine Loading data fixtures from "C:\qaserver2\data/fixtures"







SQLSTATE[HY000]: General error: 10007 Cannot insert explicit value for identit
y column in table 'asset' when IDENTITY_INSERT is set to OFF. [10007] (severity
5) []. Failing Query: "INSERT INTO [asset] ([role], [maintenance], [infrastructu
re], [review], [type_id], [asset_name], [commments], [id], [created_at], [update
d_at]) VALUES ('Generic VM', 0, 0, 0, '9', 'Virtual Computer One', 'First virtua
l computer
Test Virtual Machine





', 1, '2011-03-17 22:33:50', '2011-03-17 22:33:50')"


I have been following along with the Jobeet tutorial to learn this as well as reading the gentle introduction book in bits and pieces. I didn't fine anything in either that addressed this scenario. Is this even a good idea? Should I just give VirtualComputer its own id separate from the foreign key?

Thanks again.

Christopher

Answers (2)

2011-03-20

Loban Rahman answers:

While using Doctrine over the years, I've found that anytime I try to make the primary key ANYTHING more than an auto-incremented integer, I start having issues, both in Doctrine and/or Symfony. For example, I've always made many-to-many tables have a composite primary key of the two foreign keys it has (to the two tables it is establishing the relationship for). But when I tried to do this in Doctrine, I would have issues with embedded forms and what not. I also came into a problem with making any composite primary key. And now you are having problems with a foreign-key primary key.

I would suggest keeping the primary key and the foreign key separate. Enforce the one-on-one relationship the usual doctrine way. So...

AssetType:
actAs: { Timestampable: ~ }
columns:
type_name: { type: string(20), notnull: true, unique: true }

Asset:
actAs: { Timestampable: ~ }
columns:
asset_name: { type: string(255), notnull: true, unique: true }
type_id: { type: integer, notnull: true, default: Unknown }
relations:
AssetType: { onDelete: SET DEFAULT, local: type_id, foreign: id }

VirtualComputer:
columns:
asset_id: { type: integer, notnull: true }
hostname: { type: string(64) }
relations:
Asset: { onDelete: CASCADE, local: asset_id, foreign: id, type: one, foreignType: one }


Christopher Yee Mon comments:

Out of curiosity, does Propel have this problem?

2011-03-18

José Nahuel Cuesta Luengo answers:

Actually yes, I think you should give it its own id as primary key, and then have a unique index if you don't want to have duplicate entries for the same relationship. By doing that, you'll be able to get your fixtures working.


Christopher Yee Mon comments:

Actually I made a mistake there. I haven't enforced it in the schema.yml ( because I'm not entirely sure how to), but I actually want it to be a one-to-one relationship.

So I think it won't be an invalid situation if it is one to one cuz then the asset_id would be unique for each child record. It's still a question I would like answered if it is one-to-one. Also I would like to know how to define the relationship as one-to-one in the schema.yml and whether that's even necessary


José Nahuel Cuesta Luengo comments:

I don't think *that* should be necessary as long as you are taking care of that relationship. In your schema.yml you can have the relationship from one table to the other and viceversa, handling referential integrity in both ways. You can also add unique indexes for the combination of keys that make your composite pirmary key and that way you'll have your *implicit* one-to-one relationship.

Aside from that - and going back to the fixtures question - I've never been able to do something similar in Propel, but don't know about Doctrine..