Multiple Cascade Paths Error in SQL Server

Working on DrySQL has given me an opportunity to learn about the subtle differences between many DBMSs.

As my project is designed to run on a number of DBMSs, I crafted a reasonably complex test DB schema that I create on each DBMS in order to run my unit tests.

Though my test schema can be created without error on MySQL, PostgreSQL, and DB2, when I attempted to create it on SQL Server Express 2005 recently, it errored on the attempted creation of a FOREIGN KEY constraint with cascaded deletes and updates:

Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint 'PAYMT_APP_FK2' on table 'PAYMENT_APPLICATION' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors.

I'll explain the error using the following table layout.


Note that all the FOREIGN KEYs were created with cascaded delete and update options.

The constraint definition that produced the error above was in my CREATE TABLE statement for the PAYMENT_APPLICATION table.

[PAYMENT_APPLICATION.BILL_ID...]FOREIGN KEY REFERENCES MONTHLY_BILL.ID CASCADE DELETE CASCADE UPDATE

According to Microsoft the above error occurs because "a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement". So, my constraint definition produces an error because the cascading delete path for INVOICE would like this if the constraint were defined:

My PAYMENT_APPLICATION table will appear twice on INVOICE's cascading delete/update path:

1. PAYMENT_APPLICATION has an FK reference to INVOICE with cascading delete/update options


2. PAYMENT_APPLICATION has an FK reference to MONTHLY_BILL with cascading delete/update options, and MONTHLY_BILL has an FK reference to INVOICE with cascading delete/update options
   

As I mentioned earlier, SQL Server stands alone (to my knowledge) as the only DBMS that considers such constraints to be illegal. A simple solution to my problem would be to remove the cascade options from my PAYMENT_APPLICATION-->INVOICE Foreign Key constraint. The deletion/update of a referenced INVOICE or MONTHLY_BILL will still trigger a cascade onto the related PAYMENT_APPLICATION record, which is ultimately the behaviour that I want.

I can still create a DB schema that meets my needs in SQL Server, but it's annoying that I need to define my constraints differently than I do with other databases (although fairly typical of Microsoft software). I can't help wondering if this is a feature or a limitation.

If anyone else has an opinion about this, I'll be interested to hear it.
...And if anyone else experiences this error and struggles to figure out why, hopefully you find this explanantion helpful.

Labels: database

posted by Bryan Evans @ 2:12 PM Comments: 19

Generating ActiveRecord Data Validations Automatically

Having recently released DrySQL, which automatically generates validations for ActiveRecord data classes that enforce the constraints declared on your DB, I thought it might be useful to start a discussion about how this works (or should work).

My (possibly naive) belief is that the value of application-side validations that enforce DB constraints is twofold:

• Allows the application to provide immediate feedback to UI/forms/whatever about validity of input without the performance overhead of querying the DB

• Avoids wasting application and DB cycles trying to save/update invalid records

That said, let's look at the ActiveRecord validation methods, how they apply to enforcing DB constraints, and how DrySQL generates them automatically.

validates_uniqueness_of
I don't understand how this validation is useful, but will be interested to hear others' opinions about it. To validate that a particular value is unique in a particular table requires a DB query. Why do this on the application side rather than just letting the DB perform the validation and handling the duplicate key error your DB returns? This validation can be generated automatically based on the unique key information in the DB's information schema, but I'm not sure that it should be.

validates_length_of
validates_numericality_of
These validations can be generated automatically by querying the information schema's columns table. The columns table contains the restrictions that were declared for the column when it was defined.

validates_inclusion_of
validates_exclusion_of
These validations can be generated automatically for databases that support check constraints, but DrySQL does not yet implement this. Currently, validates_inclusion_of is generated automatically only for boolean columns since we know the value must be in the set [true, false].

validates_presence_of
This validation can be generated automatically by querying the information schema's columns table and checking the column's NOT NULL constraint. Unfortunately, the implementation of this validation in ActiveRecord does not accurately mirror the DB's NOT NULL constraint. In situations where the column contains character data and has the NOT NULL constraint set, validates_presence_of will reject an empty string value even though the DB would consider this value to be valid. DrySQL does not generate this validation, but rather generates a new validation, validates_nullability_of, where appropriate.

validates_nullability_of
This constraint is new, and was developed as part of DrySQL. It mirrors your DB's handling of NOT NULL constraints. This validation will reject a null value unless the column is autogenerated or has a default value specified, because your DB wouldn't reject a NULL in these situations either.

Labels: database, drysql, ORM, ruby-smalltalk

posted by Bryan Evans @ 1:13 PM Comments: 15

DrySQL: Answering the Challenge

After developing DrySQL to support some DB tooling at the financial organization that employs me, I was happy to learn that the functionality provided by DrySQL is actually the subject of Dave Thomas's (of Pragmatic fame) challenge to the Rails community at RailsConf 2006.

The video of the keynote is available here.

Leon Katsnelson, who is involved with the development of the official IBM Rails DB2 adapter, has also expressed the desire for this functionality on the DB2onRails blog here.

Leon and Dave, if you ever happen to visit my blog or come across DrySQL somehow, I will be interested to hear your feedback about it.

Labels: drysql

posted by Bryan Evans @ 12:24 AM Comments: 3

Introducing DrySQL

After complaining about Object-Relational Mapping frameworks for years, I finally decided to do something about it.

The Problem
My primary problem with today's ORM frameworks is that I end up defining my DB schema twice: once on my DB itself, and once again in my application either in class definitions or ORM config files. Obviously, if I make structural changes to my DB I need to make corresponding changes in my application code or config files. I know that there are round-trip engineering solutions out there, as I mentioned in my previous ORM rant, but I have yet to come across a solution that I consider clean, efficient, simple, or remotely DRY (I would be interested in hearing about some though).

The Solution: ActiveRecord?
After experimenting with ActiveRecord and Ruby, I became convinced that I was onto something.

Mapping Classes to Tables & Instvars to Columns
ActiveRecord does the work of mapping classes to tables and instance variables to columns dynamically. If I change the columns in my table, my ActiveRecord-enabled app picks up these changes on restart. This is a step in the right direction (for my needs).

Associations
ActiveRecord also provides support for developer-specified associations, which are basically smart accessor methods on your class that will retrieve associated objects through join queries.

Validations
Lastly, ActiveRecord provides support for developer-specified validations, which are basically application-side enforcement of DB constraints. These are useful for providing feedback to a UI without querying the DB (and taking a performance hit for DB I/O, and wasting DB cycles).

What's Missing?
ActiveRecord is fantastic (I might go so far as to call it groundbreaking), but unfortunately it doesn't quite meet my needs. AR makes assumptions about naming conventions for tables, primary keys, foreign keys, and breaks down in numerous situations when the DB schema is complex.
AR does provide support for explicitly declaring my table names, keys, etc., but I have a fundamental problem with declaring these things in my application code when they are already declared in my DB schema. As well, I don't want to write code to specify relationships between DB records or constraints on them, as I have already done this on my DB itself.

DrySQL
DrySQL is an extension to ActiveRecord that provides a truly DRY Object-Relational Mapping solution. As long as DrySQL is able to map your class to a table (either using AR naming conventions, or a call to set_table_name inside your class definition), you need not define any properties of your table. DrySQL will query the DB's information schema and automatically identify keys & constraints, and generate your class's associations and validations. It then delegates the instvar-column mapping duties to ActiveRecord. The result is that all the Object-Relational Mapping logic is abstracted away from your application, and is completely dynamic. Any changes you make to the structure and behaviour of your DB are dynamically picked up by your application at startup.

DrySQL doesn't make assumptions about your naming conventions, or the nature of your application (Rails web app, Ruby desktop, etc). As a result, "legacy" DB tables can get the benefit of DrySQL's ORM magic, and DrySQL can be used in any Ruby application.

If you do have the freedom to follow ActiveRecord's naming conventions in your DB, there is no need to even define your data classes. DrySQL will generate them in memory the first time you reference them. And then it will generate the associations and validations, and identify the keys and constraints the first time you create an instance of your class.

What this means is that you can ditch all the code you write today to map, describe, and constrain DB data in your application.

I will dig into the gory details of how DrySQL works in subsequent posts, but in the meantime please check out the DrySQL project homepage on RubyForge for more details about the features of DrySQL, how to use it, and how to download it.

Labels: database, drysql, ORM, ruby-smalltalk

posted by Bryan Evans @ 6:28 PM Comments: 2