Re: Creating Table as select...

Hi Daniel

Can you please exaplin me why..that i should prefer unique constraints over unique indexes.

regards
Hrishy

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1076121020.197412_at_yasure>...
> Jon wrote:
>
> > Hi All,
> > I'm trying to create a table with an 'as select' clause, but I would
> > also like to specify a table constraint on the new table - a unique
> > index. One of the versions that I have tried without success is
> > below. Can this be done in one statment or do I have to do an alter
> > table afterwords? If it can be done in one statement what would that
> > statement be?
> >
> > Thanks & Regards
> > Jon
> >
> >
> > create table obs_last_logon
> > tablespace bv_data
> > CONSTRAINT obs_last_logon_unique UNIQUE (user_id)
> > USING INDEX TABLESPACE bv_index
> > as
> > select distinct a.user_id, c.user_alias, max(timestamp) "timestamp"
> > from obs1 a, obs_bv_user_profile b, obs_bv_user c
> > where not a.user_id in (1301,984,3408,1262)
> > and a.user_id = c.user_id
> > and c.user_id = b.user_id
> > group by a.user_id, c.user_alias
>
> 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.
Received on Sat Feb 07 2004 - 02:19:44 CST