Tuesday, September 11, 2007

Inline vs. Out-of-line Constraints?

Today one of my more feisty colleages and I had a discussion about constraints. I had asked him if he wouldn't mind naming the NOT NULL constraints to a particular table definition. With this guy, nothing is easy. ;-)

So he came over and we talked about inline vs. out-of-line constraints. I asked him the advantage of out-of-line. He asked me the advantage of inline. I'm sure his answer was better than mine as he is much more articulate.

For me, it's mostly a style thing, except for FOREIGN KEY constraints which, if defined inline, will inherit the data type of the parent column. That makes life easier if you ever need to change the data type of a parent key (not a recommended best practice mind you). I just think it looks prettier (factual based evidence). It's all in one file and I like to see how many constraints I can put on a single table. I'm shooting for at least one per column to save myself time down the road of coding exceptions. Let the database do it's job!

So, what are the advantages for either method? I couldn’t find much via google or asktom, but I probably just didn't search on the right terms. I find it hard to believe that this topic hasn't come up before.

However, on cosmetic grounds:- I agree with you about check constraints; they are a key part of the column definition (or table definition for multi-column constraints)- I tend to stick to the Oracle Designer habit of out of line definitions IN SEPARATE FILES for indexes and for referential constraints (PK, UK, FK). This makes it easier to reuse these files when simply rebuilding indexes/constraints.

Remember, you can drop and recreate an index or PK/UK/FK; but you'd better not drop and recreate the table itself unless you really mean it!

One of the advantages of outline, particulary with foreign keys is that you can have separate files to build tables, constraints and indexes, etc.

Then if you need to build an environment and import a bunch of data you can speed things up by just creating the tables first and then when all the data is in, adding the constraints, indexes and keys.

Having said, I haven't worked in an environment where we've done things that way for nearly 10 years, and I normally prefer inline declarations these days.

The difference between the Inline and Outline constraint is quite simple.The Inline constraints which are column level constraints can only be defined at the time of table creation.The Outline/Out of Line Constraints can be defined at the time of creation of table as well as after table creation so that constraints can be placed by altering the structure of table. The table level constraints serve the same purpose as column level constraints and there is no significance in terms of handling and managing constraints either Inline or Outline. Following is the examples for inline and outlined constraints.

I still do the inline constraints (when I actually build tables, which is rare these days). Nigel alluded to one of the advantages that Dom mentioned above...the ability to just create the tables, load the data and then add the constraints and indexes (where PKs specifically). This is especially handy when you want to quickly and easily rebuilt a system (say, for continuous integration, testing, testing, etc).

Doing inline constraints means that the tables must be built in a specific order, data loaded in same specific order, and so on. It can be done (I've done it), but I'm sure there's a point where it just becomes cumbersome especially dealing with multiple teams and the like.