Re: Creating Table as select...

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.

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