Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: how to make foreign key

    i am doing a project and i got this

    ERROR at line 6:
    ORA-00907: missing right parenthesis

    here is the code:

    Code:
    CREATE TABLE product
    ( 
     quantity varchar2(50) NOT NULL,
     price varchar2(10) NOT NULL,
     prod_name varchar2(100) NOT NULL PRIMARY KEY,
     prod_cat_num varchar2(4) FOREIGN KEY REFERENCES category (prod_cat_num)
    );
    can somebody help me?
    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When all else fails, Read The Fine Manual

    CREATE TABLE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2009
    Posts
    3
    thanks for your help

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, apparently some people think this is a forum where you're not supposed to ask questions!

    In this case, you don't need the words FOREIGN KEY:

    Code:
    CREATE TABLE product
    ( 
     quantity varchar2(50) NOT NULL,
     price varchar2(10) NOT NULL,
     prod_name varchar2(100) NOT NULL PRIMARY KEY,
     prod_cat_num varchar2(4) REFERENCES category (prod_cat_num)
    )
    In fact, you don't need the VARCHAR2(4) either with this syntax, as it can be inferred:
    Code:
    CREATE TABLE product
    ( 
     quantity varchar2(50) NOT NULL,
     price varchar2(10) NOT NULL,
     prod_name varchar2(100) NOT NULL PRIMARY KEY,
     prod_cat_num REFERENCES category (prod_cat_num)
    )
    However, best practice is to name foreign keys like this:
    Code:
    CREATE TABLE product
    (
     quantity varchar2(50) NOT NULL,
     price varchar2(10) NOT NULL,
     prod_name varchar2(100) NOT NULL CONSTRAINT product_pk PRIMARY KEY,
     prod_cat_num CONSTRAINT product_category_fk REFERENCES category (prod_cat_num)
    )
    It is better to read an exception like:

    ORA-02291: integrity constraint (MYSCHEMA.PRODUCT_CATEGORY_FK) violated - parent key not found

    than

    ORA-02291: integrity constraint (MYSCHEMA.SYS_C009074) violated - parent key not found

    and it is also easier to find particular constraints in the data dictionary when they have meaningful names.

Posting Permissions

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