Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2014
    Posts
    3

    Unanswered: DDL naming conflicts and syntax errors

    sorry new to databases and keep getting these errors and cant seem to fix them:
    ERROR at line 7:
    ORA-00907: missing right parenthesis


    CREATE TABLE vod_classification (
    *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object


    CREATE TABLE vod_actor (
    *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object


    CONSTRAINT vod_role_film_FK FOREIGN KEY (dbFilmId) REFERENCES vod_film (dbFilmId)
    *
    ERROR at line 8:
    ORA-00907: missing right parenthesis


    heres my code:


    CREATE TABLE vod_film (
    dbFilmId NUMBER(4) NOT NULL,
    dbTitle VARCHAR2(100) NOT NULL,
    dbDirector_firstname VARCHAR2(50) NOT NULL,
    dbDirector_lastname VARCHAR2(50) NOT NULL,
    dbGenre VARCHAR2(20),
    dbUK_release_date DATE,
    dbRuntime NUMBER(4),
    dbClassId CHAR(3),
    CONSTRAINT vod_film_PK PRIMARY KEY (dbFilmId)
    CONSTRAINT vod_film_class_FK FOREIGN KEY (dbClassId) REFERENCES vod_classification (dbClassId)
    ON DELETE SET NULL
    );

    CREATE TABLE vod_classification (
    dbClassId NUMBER(4),
    dbDescription VARCHAR2(50),
    CONSTRAINT vod_classification_PK PRIMARY KEY (dbClassId)
    );

    CREATE TABLE vod_actor (
    dbActorId CHAR(4) NOT NULL,
    dbFirstname VARCHAR2(50) NOT NULL,
    dbLastname VARCHAR2(50) NOT NULL,
    dbDateOfBirth DATE,
    dbNationality VARCHAR2(30),
    dbBiography CLOB,
    CONSTRAINT vod_actor_PK PRIMARY KEY (dbActorId)
    );

    CREATE TABLE vod_role (
    dbCharacterName VARCHAR2(20) NOT NULL,
    dbFilmId NUMBER(4) NOT NULL,
    dbActorId CHAR(4) NOT NULL,
    dbRole VARCHAR2(30),
    dbActivities VARCHAR2(150),
    CONSTRAINT vod_role_PK PRIMARY KEY (dbCharacterName,dbFilmId)
    CONSTRAINT vod_role_film_FK FOREIGN KEY (dbFilmId) REFERENCES vod_film (dbFilmId)
    ON DELETE CASCADE
    CONSTRAINT vod_role_actor_FK FOREIGN KEY (dbActorId) REFERENCES vod_actor (dbActorId)
    ON DELETE CASCADE
    );

    thanks for any help

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your error messages appear to be from Oracle. Based on that, I'm moving this thread to the Oracle forum where it will probably get better quality answers.

    It appears that your script (or another student using the same table names) has been at least partially successful in the past.

    Please describe your environment in a bit more detail so that we can help you better. How many students are using this database? How are you sharing and/or avoiding name conflicts?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2014
    Posts
    3
    Hi,

    I believe where using oracle 11g, but we all have our separate area and the database is not shared.
    it is part of our lab work and I have spent a good 5 hours trying to debug it and i am not getting anywhere because I haven't really got knowledge yet to understand where the errors are coming from.

    Any help or advice would be greatly appreciated.
    Jack

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    parent table must exist before creating child table with foreign key constraint.

    http://www.orafaq.com/wiki/Foreign_key
    Last edited by anacedent; 10-09-14 at 18:19.
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For someone who is learning, I'd suggest adding code to DROP everything you're trying to create at the beginning of the script. This will usually cause errors at first, since the objects won't exist but it will get you a "clean slate" to start with so that you can better follow the process of what you are trying to accomplish. Start with something like:
    Code:
    DROP TABLE vod_classification;
    DROP TABLE vod_actor;
    DROP TABLE vod_film;
    DROP TABLE vod_role;
    
    /*****  The clean-up is done, so we can start creating things  *****/
    
    CREATE TABLE vod_film (
       dbFilmId             NUMBER(4)     NOT NULL
    ,  dbTitle              VARCHAR2(100) NOT NULL
    ,  dbDirector_firstname VARCHAR2(50)  NOT NULL
    ,  dbDirector_lastname  VARCHAR2(50)  NOT NULL
    ,  dbGenre              VARCHAR2(20)
    ,  dbUK_release_date    DATE
    ,  dbRuntime            NUMBER(4)
    ,  dbClassId            CHAR(3)
       CONSTRAINT vod_film_PK
          PRIMARY KEY (dbFilmId)
       CONSTRAINT vod_film_class_FK
          FOREIGN KEY (dbClassId)
          REFERENCES vod_classification (dbClassId)
          ON DELETE SET NULL
    );
    
    CREATE TABLE vod_classification (
       dbClassId            NUMBER(4)
    ,  dbDescription        VARCHAR2(50)
       CONSTRAINT vod_classification_PK
          PRIMARY KEY (dbClassId)
    );
    
    CREATE TABLE vod_actor (
       dbActorId            CHAR(4)       NOT NULL
    ,  dbFirstname          VARCHAR2(50)  NOT NULL
    ,  dbLastname           VARCHAR2(50)  NOT NULL
    ,  dbDateOfBirth        DATE
    ,  dbNationality        VARCHAR2(30)
    ,  dbBiography CLOB
       CONSTRAINT vod_actor_PK
          PRIMARY KEY (dbActorId)
    );
    
    CREATE TABLE vod_role (
       dbCharacterName      VARCHAR2(20)  NOT NULL
    ,  dbFilmId             NUMBER(4)     NOT NULL
    ,  dbActorId            CHAR(4)       NOT NULL
    ,  dbRole               VARCHAR2(30)
    ,  dbActivities         VARCHAR2(150)
       CONSTRAINT vod_role_PK
          PRIMARY KEY (dbCharacterName,dbFilmId)
       CONSTRAINT vod_role_film_FK
          FOREIGN KEY (dbFilmId)
          REFERENCES vod_film (dbFilmId)
          ON DELETE CASCADE
       CONSTRAINT vod_role_actor_FK
          FOREIGN KEY (dbActorId)
          REFERENCES vod_actor (dbActorId)
          ON DELETE CASCADE
    );
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Dropping objects first is OK; however, you have to take care about parent-child relationship and drop child table first (otherwise, Oracle won't let you drop a table that is referenced by foreign keys).

    Furthermore, CREATE TABLE order is wrong. VOD_FILM shouldn't be the first one as it references VOD_CLASSIFICATION which is not created yet, so CREATE TABLE VOD_FILM will fail.

    Therefore, you (Hedley) should look closely to what you do, understand what you are doing, do it carefully and it'll be OK. Hopefully.

  7. #7
    Join Date
    Oct 2014
    Posts
    3
    OK, thanks for everyone's help,
    I will have look at my code and try and repair the errors

    Thanks
    Jack

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're learning, so there will be some "bumps in the road" that are part of the learning process. This really shouldn't be all that different than anything else that you're learning, and it should really be more rewarding than most of the courses that you'll take.

    As littlefoot pointed out in his post, there are many subtleties that you need to learn about databases. Databases appear simple, and if we do a good job creating the schema then the user will see the database as simple to use. The code snippet that I provided ought to work unless there are other tables with relationships that aren't shown in your posted code. The formatting of the code that I provided should show you some things that are missing... Well formatted code often shows things that were hidden by the jumble of the unformatted version of the same code.

    Databases take some work to learn to use, and quite a bit of work to learn to design well. I'm pretty sure that you'll find the effort worth while if you choose to pursue it. The sample code that you posted has minor problems, but it shows signs that you have strong potential if you choose to develop it!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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