HiveBrain v1.2.0
Get Started
← Back to all entries
snippetModerate

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tablespacecreatewithdeclarationsdifferentusingconstraintinlineoracletable

Problem

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

-
Is it a bad practice? If so, why?

-
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;

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#49182, answer score: 11

Revisions (0)

No revisions yet.