Re: Creating Table as select...

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 09 Feb 2004 16:17:23 -0800
Message-ID: <1076372192.932013@yasure>

Jon wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1076121020.197412_at_yasure>...
>

>>Jon wrote:
>>

>
> [snip]
>

>>You should never build unique indexes ... rather unique constraints.
>>Unique indexes lack many of the capabilities of unique constraints while
>>creating the same functionality.
>>
>>Create the table then create the index.

>
>
>
> Thank you for the responses.
>
> I realize that I can create the table then the index/constraint in
> seperate statements. What I would like to do is to create them in the
> SAME STATEMENT. I don't have a reason other than curiosity, I think
> it can be done, but I do not know, nor have I been able to figure out
> the syntax on how to do it. If you are able to provide information on
> how to do it that would be nice. What I don't care about is the
> philisophical debate on what is right or what is wrong. I just want
> to know if I can do it and how to - period.
>
> Regards
> Jon

As Jonathan points out your can and the syntax is documented both by Jonathan and at http://tahiti.oracle.com. That said I would like to discourage you from doing so.

The reason is that schema objects change. And when people write a single create table statement with primary key, unique constraints, foreign key constraints, and check constraints they get something that is not maintainable. It is always better to CREATE TABLE and then user ALTER TABLE to implement the constraints. Keep in mind there is more you want to do than just specify the USING INDEX clause ... you may want DEFERABLE or other options. And why drop the table just to change a constraint.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon Feb 09 2004 - 18:17:23 CST