ORACLENERD
Time I've had a job (start on 04/10/2009 10:00 pm)
 
  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

Labels: ,

 
Comments:
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
 
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
 
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.
 
Post a Comment



Links to this post:

Create a Link



<< Home
Google


About Me || twitter/oraclenerd || View chet justice's profile on LinkedIn



Code Projects
Poor Man's Data Vault
DBA Utilities
Download Source
log4ora

How To
Parallel Processing: DBMS_JOB
Write File to Disk
Populate Time Dimension
DBMS_CRYPTO
PL/SQL: Split URL Parameters
Instrumentation: DBMS_APPLICATION_INFO

Popular
AppDev vs DataDev
Coding is Easy
Fun With Linux
Code Style Index
Better than Tom Kyte?

Previous Posts

Archives
August 2007 / September 2007 / October 2007 / November 2007 / December 2007 / January 2008 / February 2008 / March 2008 / April 2008 / May 2008 / June 2008 / July 2008 / August 2008 / September 2008 / October 2008 / November 2008 / December 2008 / January 2009 / February 2009 / March 2009 / April 2009 / May 2009 / June 2009 /



Powered by Blogger Aggregated by OraNA