Re: Creating Table as select...

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 10 Feb 2004 06:59:13 -0800
Message-ID: <1076425104.718284@yasure>

Richard Foote wrote:

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

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

>
>
> Hi Daniel,
>
> Please forgive me as it's getting a little late so I'm possibly missing
> something really obvious here, but why precisely do you need to drop a table
> to just change a constraint if you created both the table, constraints and
> indexes in the one statement ? And also why can't you use deferrable and
> other options at the same time ?
>
> BTW, I'm currently drinking a blended whiskey, not single malt I know but at
> least there's no diet coke ;)
>
> Cheers
>
> Richard

You don't: It was, as you said, very late. I always think in terms of scripting in development, moving the script to test and then to production.

What I don't like is that if the DBA wants to change something they are fiddling with the code for a table and multiple constraints rather than just modifying a single DDL statement: More chance for error. Just like in most post. ;-)

-- 
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 Tue Feb 10 2004 - 08:59:13 CST