snippetModerate
Oracle, Create table with inline constraint declarations using a different tablespace
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;
-
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:
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
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.