Oracle, Create table with inline constraint declarations using a different tablespace

constraintoracletablespaces

I have two questions about inline constraints declarations for Oracle tables:

  1. Is it a bad practice? If so, why?

  2. How could one declare a different tablespace for the primary key and index like it's done when using the outline declaration? Something like 

    create table THIS_TABLE (
        id number, 
        constraint THIS_TABLE_PK (id) tablespace INDEX_TABLESPACE
    ) tablespace DATA_TABLESPACE;

Best Answer

Like this:

create table THIS_TABLE (
  id number NOT NULL, 
  constraint THIS_TABLE_PK PRIMARY KEY(id) 
             USING INDEX TABLESPACE INDEX_TABLESPACE
) tablespace DATA_TABLESPACE;

USING INDEX TABLESPACE is the syntax - you weren't far off.

As far as good/bad practice is concerned, that's opinion-based, so not really something that should be asked here. The alternative is to use the ALTER TABLE .... syntax after table creation.

Related Question