Results 1 to 11 of 11

Thread: Error...

  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Unanswered: Error...

    Code:
    create TABLE Student (
    	student_id 		NUMBER NOT NULL PRIMARY KEY,
    	pps_id 			NUMBER NOT NULL FOREIGN KEY pps(pps_id) REFERENCES pps(pps_id),
    	p_tutor			NUMBER NOT NULL FOREIGN KEY REFERENCES tutor(tutor_id),
    	level			NUMBER NOT NULL FOREIGN KEY REFERENCES ac_level(a_level),
    	surname			VARCHAR2 (35) NOT NULL,
    	forename		VARCHAR2 (35) NOT NULL,
    	initials		VARCHAR2 (1),
    	title			VARCHAR2 (4) NOT NULL,
    	gender			VARCHAR2 (1) NOT NULL,
    	dob			DATE NOT NULL,
    	address			VARCHAR2 (50) NOT NULL,
    	address2		VARCHAR2 (50) NOT NULL,
    	city			VARCHAR2 (30) NOT NULL,
    	postcode		VARCHAR2 (10) NOT NULL,
    	telephone_home		VARCHAR2 (20),
    	telephone_mobile	VARCHAR2 (20),
    	email			VARCHAR2 (255),
    	next_of_kin		vARCHAR2 (50) NOT NULL,
    	emergency_contact	VARCHAR2 (20) NOT NULL,
    	application_date	DATE  NOT NULL,
    	comments		VARCHAR2 (4000));
    resulting in:
    pps_id NUMBER NOT NULL FOREIGN KEY pps(pps_id) REFERENCES pps(pps_id),
    *
    ERROR at line 3:
    ORA-00907: missing right parenthesis
    (I think this is oracle not my sql???)

    Anyway - I need to get this problem solved quickly if possible; it is likely to be me just missing something obvious!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you say --

    pps_id NUMBER NOT NULL FOREIGN KEY pps(pps_id) REFERENCES pps(pps_id)

    you are using the "inline" definition, i.e. you are appending the FK clause to the pps_id column definition, so the pps(pps_id) is redundant (and wrong)

    you should say instead --

    pps_id NUMBER NOT NULL FOREIGN KEY REFERENCES pps(pps_id)

    similar to the two FKs which follow


    rudy

  3. #3
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    Hi


    "pps_id NUMBER NOT NULL FOREIGN KEY REFERENCES pps(pps_id)"

    Doesn't work, pasted some examples that must make it work for you.


    [REMIVISSER@DB1.ORA-0000.COM] CREATE TABLE MASTER (K NUMBER PRIMARY KEY);

    Table created.

    [REMIVISSER@DB1.ORA-0000.COM] CREATE TABLE CHILD (K NUMBER REFERENCES MASTER(K));

    Table created.

    [REMIVISSER@DB1.ORA-0000.COM] DROP TABLE CHILD;

    Table dropped.

    [REMIVISSER@DB1.ORA-0000.COM] CREATE TABLE CHILD (K NUMBER );

    Table created.

    [REMIVISSER@DB1.ORA-0000.COM] ALTER TABLE CHILD
    2 ADD CONSTRAINT FK_CHILD_MASTER
    3 FOREIGN KEY (K) REFERENCES MASTER(K);

    Table altered.

    [REMIVISSER@DB1.ORA-0000.COM] DROP TABLE CHILD;

    Table dropped.

    [REMIVISSER@DB1.ORA-0000.COM] CREATE TABLE CHILD (K NUMBER FOREIGN KEY REFERENCES MASTER(K) );
    CREATE TABLE CHILD (K NUMBER FOREIGN KEY REFERENCES MASTER(K) )
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis







    Good Luck

    Remi
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  4. #4
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    damn thats what it is...!! ARG. Will post back and tell you if it works! thanks guys!

  5. #5
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    right it didn't work. Is it because it can't reference that table (it is further down the script) - if so it should really say. Oracle, pshh. Anyway if it is; do i have to add this reference foreign key after all tables are made??

    Code:
    SQL> create TABLE Student (
      2   student_id   NUMBER NOT NULL PRIMARY KEY,
      3   pps_id    NUMBER NOT NULL FOREIGN KEY REFERENCES pps(pps_id),
      4   p_tutor   NUMBER NOT NULL FOREIGN KEY REFERENCES tutor(tutor_id),
      5   level   NUMBER NOT NULL FOREIGN KEY REFERENCES ac_level(a_level),
      6   surname   VARCHAR2 (35) NOT NULL,
      7   forename  VARCHAR2 (35) NOT NULL,
      8   initials  VARCHAR2 (1),
      9   title   VARCHAR2 (4) NOT NULL,
     10   gender   VARCHAR2 (1) NOT NULL,
     11   dob   DATE NOT NULL,
     12   address   VARCHAR2 (50) NOT NULL,
     13   address2  VARCHAR2 (50) NOT NULL,
     14   city   VARCHAR2 (30) NOT NULL,
     15   postcode  VARCHAR2 (10) NOT NULL,
     16   telephone_home  VARCHAR2 (20),
     17   telephone_mobile VARCHAR2 (20),
     18   email   VARCHAR2 (255),
     19   next_of_kin  vARCHAR2 (50) NOT NULL,
     20   emergency_contact VARCHAR2 (20) NOT NULL,
     21   application_date DATE  NOT NULL,
     22   comments  VARCHAR2 (4000));
     pps_id    NUMBER NOT NULL FOREIGN KEY REFERENCES pps(pps_id),
                               *
    ERROR at line 3:
    ORA-00907: missing right parenthesis

  6. #6
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Exclamation

    well it's not that the table pps does not exist (i have created it now). hmm. HELP!

  7. #7
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    See

    [REMIVISSER@DB1.ORA-0000.COM] CREATE TABLE CHILD (K NUMBER FOREIGN KEY REFERENCES MASTER(K) );
    CREATE TABLE CHILD (K NUMBER FOREIGN KEY REFERENCES MASTER(K) )
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis


    What I would advice you is to first create the table and later add the constraint.

    I think it's easier...

    I would also advice you to name your constraint (when you don't name it Oracle will assign a name)

    Naming your constraints (and add some logic to the naming) will make it easier for your to look the constraints up in dba_objects.


    Good luck





    Remi
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  8. #8
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    right, removed all the stuff and got this;
    Code:
    create TABLE Student (
    	student_id 		NUMBER NOT NULL PRIMARY KEY,
    	pps_id 			NUMBER NOT NULL,
    	p_tutor			NUMBER NOT NULL,
    	a_level			NUMBER NOT NULL,
    	surname			VARCHAR2 (35) NOT NULL,
    	forename		VARCHAR2 (35) NOT NULL,
    	initials		VARCHAR2 (1),
    	title			VARCHAR2 (4) NOT NULL,
    	gender			VARCHAR2 (1) NOT NULL,
    	dob			DATE NOT NULL,
    	address			VARCHAR2 (50) NOT NULL,
    	address2		VARCHAR2 (50) NOT NULL,
    	city			VARCHAR2 (30) NOT NULL,
    	postcode		VARCHAR2 (10) NOT NULL,
    	telephone_home		VARCHAR2 (20),
    	telephone_mobile	VARCHAR2 (20),
    	email			VARCHAR2 (255),
    	next_of_kin		vARCHAR2 (50) NOT NULL,
    	emergency_contact	VARCHAR2 (20) NOT NULL,
    	application_date	DATE  NOT NULL,
    	comments		VARCHAR2 (4000));
    The above works fine (apart from level which seems to be special - so i changed it), but the constraint (bellow) doesn't;
    Code:
    SQL> ALTER TABLE Student
      2  ADD CONSTRAINT FK_Student_pps
      3  FOREIGN KEY REFERENCES pps(pps_id);
    FOREIGN KEY REFERENCES pps(pps_id)
                *
    ERROR at line 3:
    ORA-00906: missing left parenthesis
    Any ideas???

    fyi: I am using notepad to write queries - I have been told there are sometimes problems with spurious characters but don't know the truth in that
    Last edited by rhs98; 03-05-03 at 11:45.

  9. #9
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    Notepad is fine.


    3 FOREIGN KEY REFERENCES pps(pps_id);

    Should change to

    3 FOREIGN KEY (foreign_key_column_name) REFERENCES pps(pps_id);


    How should Oracle know what column has a foreign key ;-)


    Hope this helps you out.


    Remi




    http://askremi.ora-0000.com
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  10. #10
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    it was stareing me in the face! Many thanks - It seems to be working noice now!

    Cheers!

  11. #11
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    I can't spell for toast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •