Xref: alice comp.databases.oracle.misc:43899 Path: alice!news-feed.fnsi.net!oleane!isdnet!newsfeed.nacamar.de!news-x.support.nl!bullseye.news.demon.net!demon!news.demon.nl!demon!sybrandb.demon.nl!not-for-mail From: "Sybrand Bakker" Newsgroups: comp.databases.oracle.misc Subject: Re: Urgent!! - Deferred constraints Date: Sun, 10 Oct 1999 22:44:42 +0200 Message-ID: <939588383.7079.0.pluto.d4ee154e@news.demon.nl> References: <38009c8e_2@news.ausmail.com> Reply-To: "Sybrand Bakker" X-Trace: news.demon.nl 939588383 pluto:7079 NO-IDENT sybrandb.demon.nl:212.238.21.78 X-Complaints-To: abuse@demon.net X-Newsreader: Microsoft Outlook Express 5.00.2014.211 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211 Lines: 53 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 wrote in message news:38009c8e_2@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 > >