Re: How To Insert DATE

Hello Howard.

"Howard J. Rogers" <howardjr_at_www.com> schrieb im Newsbeitrag news:3a7c21e5_at_news.iprimus.com.au...
>
> "Harald Henkel" <Harald_at_Henkel.DAH.UUnet.DE> wrote in message
> news:95h3t0$h9o6s$1_at_ID-56141.news.dfncis.de...

<SNIP>

> > ALTER TABLE employee
> > ADD CONSTRAINT employee_PK PRIMARY KEY
> > (emp_id) ;
 

> > Of course in the real world, you would have to add tablespace and table
 size
> > clauses to the create table and alter table statements for indexes.

> Can't let that one pass! In the real, real world, you ought to get out of
> the habit of specifying a storage clause (and hence 'table size' things
 like
> iniital and next extents) at segment level. If you've done your job
> properly, the tablespace will be sitting there with an appropriate default
> storage clause that can fill in the gaps.

One default, that will be OK for any kind of table ? Or do you suggest to create many different tablespaces for different "types" of tables ?

> On the other hand, I don't like to see any syntactical advice given on
> Primary or Unique keys that doesn't EXPLICITLY include a tablespace clause
> for the indexes associated with those constraints, because fishing indexes
> out of DATA tablespace is a royal pain in the butt to have to keep doing,
 is
> one of the simplest things to avoid, and all newbies should be taught
 about
> it properly upfront.

Hmm. Perhaps you're right. But I thought, that he's a real beginner, just starting with Oracle's default database, which has only one table space (USER_DATA). I didn't want to confuse somebody, who doesn't even know the base syntax, with information he didn't request.

> Hence, the CORRECT syntax for declaring something to be a primary key
 after
> the event is:
 

> Alter table employee
> add constraint employee_empid_pk Primary Key (emp_id)
> Using Index
> Tablespace INDEXBLAH ;
>
> And for doing it at the time of creating the table in the first place:
>
> Create table employee (
> emp_id number (4) constraint employee_empid_pk Primary Key
> using index
> Tablespace INDEXBLAH,
> name varchar2 (15),
> age number (3),
> date_hired date,
> address varchar2 (30))
> Tablespace DATABLAH;

If I would have written that, I'm sure the next reply would have been: "Why am I getting the error:

Tablespace DATABLAH

           *
FEHLER in Zeile 9:
ORA-00959: Tablespace 'DATABLAH' nicht vorhanden"

(Sorry this is in German ;-) )

> In short, you are asking for trouble if you don't specify tablespaces for
> both the table and any associated constraint-inspired indexes, and the
> sensible thing to do is (a) to get into the habit of always declaring them
> and (b) always including those parts of the syntax when advising others.
>
> It is probably a bit picky of me, I know: but honestly, the amount of time
 I
> spend advising on how to split tables apart from their indexes beggars
> belief -and it's largely because few seem to realise that certain
> constraints imply indexes, and those indexes must be housed appropriately.

Sure.
But I think to understand this, the guy has still to learn a lot and I didn't think it's been appropriate to be to explicit here. I gave a hint, that he would have to declare tablespaces in a real database. That seemed to be enough information.
After all, there are some books about Oracle syntax out there, he could look up.
I didn't intend to give him a complete Oracle DB setup and administration course in here, but simply answer his questions.

With kind regards,
Harald Henkel Received on Sat Feb 03 2001 - 10:57:28 CST