Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: foreign keys between 2 tables - newbie question

Re: foreign keys between 2 tables - newbie question

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Thu, 19 May 2005 00:24:09 +0100
Message-ID: <s9in81plbahkleb8jge0ei82crkjp8hjuh@4ax.com>


On Wed, 18 May 2005 15:29:40 -0700, DA Morgan <damorgan_at_psoug.org> wrote:

>Andy Hassall wrote:
>
>> SQL> create table employee (
>> 2 emp_id number not null,
>> 3 dept_id number not null,
>> 4 constraint employee_pk primary key (emp_id)
>> 5 );
>
>Andy ... please don't take this as a criticism, that is not the intent,
>but from an education standpoint I would never teach someone to create
>a constraint in the way you have demonstrated here.
>
>What tablespace ist he index placed in?
>With what name (assuming not 10g)?

 EMPLOYEE_PK - I explicitly named the constraint, and the automatically-created index gets the same name - not a 10g new feature, that's certainly how it works in 9i. Memory of 8i is fading somewhat nowadays ;-) but I believe that was how it worked back then too.

>With what storage parameters?
>etc.
>
>And then for maintenance purposes the DDL does not exist to
>recreate it if it is dropped. That is why I always teach CREATE
>TABLE followed by ALTER TABLE.

 Yep, indeed, or in this case even another step; CREATE TABLE followed by CREATE INDEX (probably as non-unique in case you ever want to make that PK deferrable, or even disabled for some reason) followed by ALTER TABLE for the constraint on top. Or a USING INDEX clause to specify the full DDL for the index in the ALTER TABLE.

 The contrary view would be why get bogged down in storage clauses when just trying to demo deferrable constraints with a single row... but as far as any "real" DDL goes, I agree.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Received on Wed May 18 2005 - 18:24:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US