CODEWITHSUNDEEP
sqloracle-databaseddl
anger
anger

Reputation: 1018

What is the best practice DDL for creating Tables? Single statement with all objects or many individual statements creating and altering?

Is there a best practice in that is closest to one of these examples?

CREATE TABLE TABLE1 
(
ID   NUMBER(18)     CONSTRAINT TABLE1_PK PRIMARY KEY,
NAME VARCHAR2(10)   CONSTRAINT NAME_NN NOT NULL
);

or

CREATE TABLE TABLE1 
(
ID   NUMBER(18),
NAME VARCHAR2(10)   CONSTRAINT NAME_NN NOT NULL
);

ALTER TABLE TABLE1  ADD CONSTRAINT TABLE1_PK
PRIMARY KEY (ID)
USING INDEX (CREATE UNIQUE INDEX IDX_TABLE1_PK ON TABLE1 (ID));

Is either scenario going to result in a better outcome in general? The first option is much more readable, but perhaps there are reasons why the latter is preferable.

Upvotes: 3

Views: 2492

Answers (3)

ConsultantChris
ConsultantChris

Reputation: 1

It's a personal preference to define any attributes or defaults for a field in the actual create statement. One thing I noticed is your second statement won't work since you haven't specified the id field is NOT NULL.

I guess it's a personal best practice for readability that I specify the table's primary key upfront.

Another thing to consider when creating the table is how you want items identified, uniquely or composite. ALTER TABLE is good for creating composite keys after the fact.

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

When building a deployment script that is eventually going to be run by someone else later on, I prefer splitting the scripts a fair bit. If something goes wrong, it's a bit easier to tell from the logs what exactly failed.

My table creation script will usually only have NOT NULL constraints. The PK, unique and FK constraints will be added afterwards.

This is a minor point though, and I don't have anything in particular against combining it all in one big CREATE TABLE statement.

You may find that your workplace already has a standard in place. e.g. my current client requires separate scripts for the CREATE TABLE, then more separate scripts for constraints, indexes, etc.

The exception, of course, is index-organized tables which must have a PK constraint declared upfront.

Upvotes: 5

Will Hartung
Will Hartung

Reputation: 118593

Definitely personal preference. I prefer to do as much as I can in the single CREATE TABLE statement simply because I find it more concise. Most everything I need is described right there.

Sometimes that's not possible. Say you have two tables with references to each, or you want to load up a table with a bunch of data first, so you add the additional indexes after the table is loaded.

You'll find many tool that create schemas from DBs will separate them (mostly because it's always correct -- define all the tables, then define all of the relationships).

But personally, if practical, I find having it all in one place is best.

Upvotes: 5

Related Questions