No Foreign Keys / Constraint Error

2010-10-21T15:26:37Z

Hi,

I've been evaluating FogBugz 8 online for a little while and so far I've been impressed. Today I decided to use the "Download Database" feature (which is a pretty nice feature). I grabbed the SQL Sever version and loaded it up to see what was inside.

My first stop with a new database is usually the database diagram node. If there are any diagrams it really helps (me) with quickly understanding a database. Since this was basically a backup of a production database I wasn't really surprised that there were none in there, so I decided to build my own using the online schema documentation.

I added the project table and all related tables (based on the schema documentation). I was surprised to find that there were no foreign keys actually defined in the database. I proceeded to add them myself and found a couple odd things:

The TokenAssociation table has the ixProject field set to smallint instead of int like its parent (project) table. So until I altered this I was unable to define the relationship.
I tried to define a relationship between the Project and ACL tables, but the constraint failed because there was a row in the ACL table with a ixProject ID value that did not exist in the project table (value was zero)

These are both problems that would not be present if official constraints had been defined. The first one seems a little odd to me, and should probably be fixed? The second could be a design decision for maintaining history or something. I don't know.

Honestly, I'm a little leery of databases with no relationships defined because it's so easy to introduce errors like I've outlined above. Is this so you can support both MySQL and SQL Server maybe? Is it for performance considerations? I'm curious (obviously).

Oh, also, I was going to submit this as a bug report, but I couldn't find where to do that...is this basically the correct place for this?

Thanks, Andrew

Answer by Ted for No Foreign Keys / Constraint Error

2010-10-21T15:36:34Z

FogBugz does not enforce or use foreign key constraints. For the most part, FogBugz tries to deal with these things in code so there's one place to go when things aren't working right.

I'd say the most likely consequence of adding constraints to your database will be to cause FogBugz to fail at some point. We realize that this makes the database harder to diagram, but we have strong (column) naming conventions to make up for it. (Apologies for the inconsistent pluralization of table names... sigh.)

No Foreign Keys / Constraint Error - FogBugz Knowledge Exchange

No Foreign Keys / Constraint Error

Answer by Ted for No Foreign Keys / Constraint Error