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!

Here's a sample of inline constraints:
DROP TABLE s;
DROP TABLE t;

CREATE TABLE t
(
id NUMBER(10)
CONSTRAINT pk_id PRIMARY KEY,
name VARCHAR2(30)
CONSTRAINT nn_name_t NOT NULL,
age NUMBER(2,0)
CONSTRAINT nn_age_t NOT NULL
CONSTRAINT ck_gtzero_age_t CHECK ( age >= 0 )
);

CREATE TABLE s
(
sid NUMBER(10)
CONSTRAINT pk_sid PRIMARY KEY,
id
CONSTRAINT fk_id_s REFERENCES t ( id ),
something_unique VARCHAR2(30)
CONSTRAINT uq_somethingunique_s UNIQUE
);

And out-of-line Constraints:

DROP TABLE s;
DROP TABLE t;

CREATE TABLE t
(
id NUMBER(10),
name VARCHAR2(30),
age NUMBER(2,0)
);

ALTER TABLE t ADD CONSTRAINT pk_id PRIMARY KEY ( id );

ALTER TABLE t ADD CONSTRAINT nn_name_t CHECK ( name IS NOT NULL );

ALTER TABLE t ADD CONSTRAINT nn_age_t CHECK ( age IS NOT NULL );

ALTER TABLE t ADD CONSTRAINT ck_gtzero_age_t CHECK ( age >= 0 );

CREATE TABLE s
(
sid NUMBER(10),
id NUMBER(10),
something_unique VARCHAR2(30)
);

ALTER TABLE s ADD CONSTRAINT pk_sid PRIMARY KEY ( sid );

ALTER TABLE s
ADD CONSTRAINT fk_id_s FOREIGN KEY ( id ) REFERENCES t ( id );

ALTER TABLE s
ADD CONSTRAINT uq_somethingunique_s UNIQUE ( something_unique );


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.

* Note that I did use 5 constraint types

7 comments:

Unknown said...

The difference between inline and out-of-line constraints is just a matter of syntax and/or timing.

* Inline constraints are created by the create table statement.

* Out-of-line contraints suppose the table to preexist.

Nothing more, nothing less.

HTH

Nigel said...

Chet

No difference (except cosmetic).

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!

Regards Nigel

DomBrooks said...

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.

Asif said...

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.

--INLINE CONSTRAINTS
CREATE TABLE AXY
(EMPNO NUMBER CONSTRAINT PK_EMPNO PRIMARY KEY(EMPNO),
ENAME VARCHAR2(1000) CONSTRAINT NOT_NULL_ENAME NOT NULL,
GENDER VARCHAR2(1) CONSTRAINT Chk_constraint CHECK (GENDER IN('M','F'))
);

OR

CREATE TABLE AXY
(EMPNO NUMBER PRIMARY KEY(EMPNO),
ENAME VARCHAR2(1000) NOT NULL,
GENDER VARCHAR2(1) CHECK (GENDER IN('M','F'))
);



--OUTLINE CONSTRAINTS
CREATE TABLE AXY
(EMPNO NUMBER,
ENAME VARCHAR2(1000),
GENDER VARCHAR2(1),
CONSTRAINT PK_EMPNO PRIMARY KEY(EMPNO),
CONSTRAINT NOT_NULL_ENAME NOT NULL,
CONSTRAINT Chk_constraint
CHECK (GENDER IN('M','F'))
);

OR
CREATE TABLE AXY
(EMPNO NUMBER,
ENAME VARCHAR2(1000),
GENDER VARCHAR2(1));

ALTER TABLE AXY ADD CONSTRAINT PK_EMPNO PRIMARY KEY(EMPNO);
ALTER TABLE AXY ADD CONSTRAINT NOT_NULL_ENAME NOT NULL;
ALTER TABLE AXY ADD CONSTRAINT Chk_constraint CHECK (GENDER IN('M','F'));

Thank you
Asif.

oraclenerd said...

@Asif,

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.

chet

Anonymous said...

@ DomBrooks & Nigel,

Excellent points.

Also in my opinion, when debugging the style of syntax may become important.

Having each elementary action VISIBLE and NOT ENTANGLED with others may help us follow the logic's steps and detect what went wrong.

Also it makes one think clearer in the back of their minds, encouraging an attitude of seeking to bring up hidden details and not bury them in clutter.

With my warmest regards,
Vlad

Oren Nakdimon said...

Hi Chet.
I've just published a post that shows that sometimes the difference is not only cosmetic: http://db-oriented.com/2017/04/11/optimization-of-check-constraint-creation/

When adding a new column with a check constraint to an existing big table, then using an inline constraint outperforms using out-of-line one.

Thanks,
Oren (@DBoriented)