Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Urgent!! - Deferred constraints
Compared to older versions you would expect the correct statement to be
alter table DEPARTMENT add constraint mgrssn_fk foreign key (mgrssn)
references
EMPLOYEE initially deferred deferrable ;
ie no quotes and no reference to a column name in the pk holding table.
Hth,
--
Sybrand Bakker, Oracle DBA
Evelyn <onezero_at_post1.com> wrote in message
news:38009c8e_2_at_news.ausmail.com...
> Hi, thank you for taking your time to read my posting. i have these 2
tables
> that i have problem with.
>
> Employee {ssn, name, bdate, address, superssn, dno}PK=ssn, FK=dno,
> Department {dname, dnumber, mgrssn}PK=dnumber, FK=mgrssn
> where dno = dnumber = department number
>
> the problem is, i can't reference a column of a table that has not yet
been
> defined. An EMPLOYEE must work for a DEPARTMENT. A DEPARTMENT must have a
> manager who is an EMPLOYEE.
>
> One solution is to create the DEPARTMENT table first without referencing
the
> EMPLOYEE table. Then, create the EMPLOYEE table and have the DNO attribute
> reference DEPARTMENT(DNUMBER). After both tables have been successfully
> created, use the following ALTER command to add the constraint:
>
> alter table DEPARTMENT add (
> constraint mgrssn_fk foreign key (mgrssn) references
> EMPLOYEE(ssn) initially deferred deferrable );
>
> however, i always have problem with this alter statement, it will return
an
> error message saying that "right bracket expected".. if i removed the
> "initially deferred deferrable", everything is fine..
>
> any suggestion what's goes wrong?? Thanks for your time.
>
> --
> Cheers
> Evelyn
>
>
Received on Sun Oct 10 1999 - 15:44:42 CDT