VoltDB: Is a database without foreign keys useful enough for you?

In the previous post we have announced the availability of our UML to VoltDB translation service . However, if you try it, you may get the feeling that the generated SQL script looks incomplete.

For instance, the generated script for this very simple UML class diagram

is the following:

  CREATE TABLE Author_rt(
    name VARCHAR(40) NOT NULL ,

    title VARCHAR(40) NOT NULL ,
    numPages VARCHAR(40) NOT NULL ,
    writer INTEGER NOT NULL ,

There are several things missing in this example (compared to what the UMLtoDB service generates for other DBMSs):

  • DROP sentences to remove previous versions of the tables
  • CHECK constraints that help us to implement boolean attributes (since the Boolean datatype is not supported)
  • and, more importantly, FOREIGN KEYs that ensure that the value of a writer column corresponds to an existing author id in the Author table

Given that data consistency is one of the advantatges that VoltDB creators see over NoSQL approaches (from the VoltDB web page “NoSQL … requires the application developer to manage the complexities of data consistency, VoltDB handles data integrity and consistency for you”), this seems surprising. As happened with MySQL, they will probably add support for Foreign Keys at some point (hopefully sooner than later though, right now, this is not explicitly listed in the product roadmap ) but until then this is really annoying.

I have no intention to go back in time, when referential integrity still had to be checked at the code-level (btw, I’m young enough NOT TO have developed applications FOR pre-relational databases but old enough TO have had TO deal WITH the migration OF such apps). OF course, this does NOT affect the relevance OF VoltDB IN many domains AND FOR many types OF applications but I think I’ll pass for now.

And you? How important are FKs in the kind of application you develop? If you have efficiency problems, would you be willing to sacrifice FKs (and check constraints,…) or moving to VoltDB would be only used as last resource? Leave your comments below!!

If you enjoyed this post you can subscribe to this Software Modeling blog and/or follow me on twitter or through the portal’s mailing list . AND if you really liked it help me pass it ON TO others by bookmarking AND sharing the post USING the links below:

3 Responses to VoltDB: Is a database without foreign keys useful enough for you?

  1. Anonymous says:

    Thanks for the cool tool Jordi. I look forward to trying it out.

    Foreign keys are definitely something we’d like to support in VoltDB, but you’re right that they’re not on our short term roadmap.

    Partly this is because they are difficult to enforce in a partitioned environment. It would be very easy to add a constraint that would force a previously single-partition statement to run at all partitions. Partly this is because foreign keys are a big performance hit for a database built primarily for performance.

    Given that VoltDB uses a 100% stored procedure interface, the stored procedure author can selectively validate that constraints continue to be met. The client-side developer calling the stored procedure still doesn’t need to worry about the database being left in an inconsistent state, provided the procedures only allow valid state transitions.

    We’ve built the first versions of VoltDB to make extreme scale less painful. That’s not to say it’s not painful at all. We’re working hard on broader SQL support, live changes to schema, better integration with other tools and languages and more. FK support will eventually make it onto the roadmap, but it may be a while.

  2. jordi says:

    The stored procedure interface does not completely solve the problem (checking referential integrity within the stored procedure is still a manual time-consuming and error-prone coding task) but for sure it reduces it by limiting the set of programmers that need to worry about this

  3. Anonymous says:

    When using a Model Driven approach to data access I rarely find any real needs for FK’s – often times they complicate thing by forcing you TO perform operations IN the RIGHT order OR deferring FK CONSTRAINT CHECK TO COMMIT TIME.

    When modeling according TO DDD you typically WORK WITH Aggregates WHERE the root aggregate helps TO enforce CONSTRAINTS AND invariants. This IS IMO a much better AND MORE flexible approach than FK’s – and actually the whole idea of using tables to model Aggregates can be argued.

    /Jeppe Cramon

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Powered by WordPress
More in databases, tools
UML to VoltDB – New DBMS for the SQL DDL generation service
UMLtoDB Service: Avoiding NOT NULL constraints on columns
Simple Natural Language to Use Case script generator with yUML