Recently our DBA recommended the following for creating tables.

1. All constraints (primary keys, unique keys, foreign keys, etc….) should be declared outside of the CREATE TABLE …. statements, and instead done as ALTER TABLE statements.
2. All tables must have table and column comments to provide information for the data dictionary/schema metadata.

Not Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL,
  PRIMARY KEY (id)
);

Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL
);
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY (id);
COMMENT ON TABLE items IS 'repository items';
COMMENT ON COLUMN items.id IS 'primary key (repository id)';
COMMENT ON COLUMN items.type IS 'item type';

I asked the DBA why this is considered best practice and this is what he said.

The DBA’s put indexes into a different tablespace than the table itself for storage, admin, and somewhat performance reasons (NetApp spreads out the I/O so does not quite apply to Upromise environment).

If the PK is part of the table create statement, they have to break out the statement in order to put the PK into a different tablespace or different storage parameters than the table. Having the statements separate from the start makes things smoother.

– Jeff Janousek, Upromise DBA