Re: inline out-of-line

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 17 Feb 2004 09:53:06 -0800
Message-ID: <1077040345.937452@yasure>

Vu Pham wrote:

>>The second reason I don't like them is that when moving code to
>>production DBAs should have the ability to alter tablespace and
>>storage parameters. Making them do so inside of a large block of
>>DDL makes it easier for mistakes to be made.

>
>
> Could you please tell me a little bit more about this ? I can't find the
> relationship between tablespace and constraints. ( You can see that I am
> just a rookie in this field ).

ALTER TABLE mytable
ADD CONSTRAINT pk_mytable
PRIMARY KEY (pk_column)
USING INDEX
PCTFREE 5
TABLESPACE tspace06;

same "USING INDEX ... " syntax for unique constraint.

>>Finally consider the possibility that a CREATE TABLE and many of
>>its in-line constraints may be valid but one may have a problem.
>>Do you want the entire CREATE to fail including all views and code
>>dependent upon the table or just the single, easy to fix, constraint
>>creation?

>
> Thanks for mentioning this. When using the out-of-line constraints, I often
> put them at the end , and included inside the CREATE clause, of the tables.
> Your reply reminds me about the ALTER TABLE ADD CONSTRAINT .
> I just made this change and the sql files look easier to understand now.

Exactly. Maintenance is as, if not more, important than the time spent writing the initial statement.

> Is it correct when I say that the advatange of the inline constraint clause
> is to imply the column type ? I mean the developers do not need to declare
> the field types that are involved into constraints at different locations.
> They need to do this for out-of-line contrainsts.
>
> Vu

I can think of no advantage to inline constraints unless one is lazy and doesn't specify the full syntax required or doesn't put constraints on all possible fields which is the recommended practice.

-- 
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 17 2004 - 11:53:06 CST