Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2013
    Posts
    36

    Unanswered: duplicate name error

    Can a table have 1 primary key and 2 foreign keys? .....

    In Oracle Application Express, in one of my tables basically includes: download dates, customer id & movie id.

    I want to make download dates primary and the other two foreign. I keep getting duplicate name error.. Well I've tried to join customer id to another primary customer id in another table and the same with movies id so, I've renamed the columns slightly different to "unique custome id" etc. but still get the error.

    I tried making a composite key but firstly that gives me the option to have 2 primary keys but I only need 1 plus primary and 2 foreign keys but oracle still won't produce the table either way. what do I do?
    Here's the SQL code;

    CREATE table "M_MOVIES_DOWNLOADED" (
    "DOWNLOAD_DATE" DATE,
    "CUSTOMER'S_ID" NUMBER,
    "MOVIES_ID" NUMBER
    )
    /

    alter table "M_MOVIES_DOWNLOADED" add constraint "M_MOVIES_DOWNLOADED_PK" primary key ("DOWNLOAD_DATE","DOWNLOAD_DATE")
    /

    CREATE sequence "M_MOVIES_DOWNLOADED_SEQ"
    /

    CREATE trigger "BI_M_MOVIES_DOWNLOADED"
    before insert on "M_MOVIES_DOWNLOADED"
    for each row
    begin
    if :NEW."DOWNLOAD_DATE" is null then
    select "M_MOVIES_DOWNLOADED_SEQ".nextval into :NEW."DOWNLOAD_DATE" from dual;
    end if;
    end;
    /

    ALTER TABLE "M_MOVIES_DOWNLOADED" ADD CONSTRAINT "M_MOVIES_DOWNLOADED_FK"
    FOREIGN KEY ("CUSTOMER'S_ID")
    REFERENCES "M_CUSTOMER_DETAILS" ("CUSTOMER_ID")

    /
    ALTER TABLE "M_MOVIES_DOWNLOADED" ADD CONSTRAINT "M_MOVIES_DOWNLOADED_FK2"
    FOREIGN KEY ("MOVIES_ID")
    REFERENCES "M_AVAILABLE_MOVIES" ("MOVIE_ID")

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I advise you to avoid using double quote marks around table or column names.
    This advice has nothing to do with problem at hand; just on going advice.
    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
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> @fk
    SQL> CREATE TABLE M_CUSTOMER_DETAILS (CUSTOMER_ID NUMBER);
    
    Table created.
    
    SQL> alter table M_CUSTOMER_DETAILS       add constraint M_CUSTOMER_DETAILS_PK primary key (CUSTOMER_ID)
      2  /
    
    Table altered.
    
    SQL> CREATE TABLE M_AVAILABLE_MOVIES (MOVIE_ID NUMBER);
    
    Table created.
    
    SQL> alter table M_AVAILABLE_MOVIES     add constraint M_AVAILABLE_MOVIES_PK primary key (MOVIE_ID)
      2  /
    
    Table altered.
    
    SQL> CREATE table M_MOVIES_DOWNLOADED (
      2  DOWNLOAD_DATE DATE,
      3  CUSTOMER_ID NUMBER,
      4  MOVIES_ID NUMBER
      5  )
      6  /
    
    Table created.
    
    SQL> 
    SQL> alter table M_MOVIES_DOWNLOADED add constraint M_MOVIES_DOWNLOADED_PK primary key (DOWNLOAD_DATE)
      2  /
    
    Table altered.
    
    SQL> 
    SQL> ALTER TABLE M_MOVIES_DOWNLOADED ADD CONSTRAINT M_MOVIES_DOWNLOADED_FK
      2  FOREIGN KEY (CUSTOMER_ID)
      3  REFERENCES M_CUSTOMER_DETAILS (CUSTOMER_ID)
      4  /
    
    Table altered.
    
    SQL> ALTER TABLE M_MOVIES_DOWNLOADED ADD CONSTRAINT M_MOVIES_DOWNLOADED_FK2
      2  FOREIGN KEY (MOVIES_ID)
      3  REFERENCES M_AVAILABLE_MOVIES (MOVIE_ID)
      4  /
    
    Table altered.
    
    SQL>
    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.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    At what point do you get the error? The statements you show do not have any name conflicts among themselves. Could there be objects in the same schema with one or more of these names already existing?

  5. #5
    Join Date
    Oct 2013
    Posts
    36
    Quote Originally Posted by MCrowley View Post
    At what point do you get the error? The statements you show do not have any name conflicts among themselves. Could there be objects in the same schema with one or more of these names already existing?

    I get the error just when clicking Create table. I've also tried to create the table without the foreign keys then add them after but I get the same error. I don't have conflicting names in any other tables.

    One more question, is it normal to have 3 primary keys in a single table? This is one thing oracle is allowing me to do but not sure if it will work with my queries.

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by london34 View Post
    One more question, is it normal to have 3 primary keys in a single table? This is one thing oracle is allowing me to do but not sure if it will work with my queries.
    Not only is it not normal, it is not possible to have 3 primary keys in a single table. You can have One Primary Key made up of three columns though.

  7. #7
    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

    Glossary
    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.

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    Code:
    SQL> CREATE table "M_MOVIES_DOWNLOADED" (
      2  "DOWNLOAD_DATE" DATE,
      3  "CUSTOMER'S_ID" NUMBER,
      4  "MOVIES_ID" NUMBER
      5  )
      6  /
    
    Table created.
    
    SQL>
    SQL> alter table "M_MOVIES_DOWNLOADED" add constraint "M_MOVIES_DOWNLOADED_PK" primary key ("DOWNLOAD_DATE","DOWNLOAD_DATE")
      2  /
    alter table "M_MOVIES_DOWNLOADED" add constraint "M_MOVIES_DOWNLOADED_PK" primary key ("DOWNLOAD_DATE","DOWNLOAD_DATE")
                                                                                                           *
    ERROR at line 1:
    ORA-00957: duplicate column name
    Why are you listing the same column twice in your constraint? This makes no sense

  9. #9
    Join Date
    Dec 2007
    Posts
    253
    OK, I've read your OP a few times and I think I might understand where you are getting confused.
    I want to make download dates primary
    No, you really, really don't. If you do that, then you are effectively saying that only 1 movie can be downloaded on any particular day (ok, more specifically any particular date and time, but you get the point). If you make download_date a primary key, then each value can appear ONLY ONCE in that table, and you don't want to be restricted like that.
    Well I've tried to join customer id to another primary customer id in another table and the same with movies id so, I've renamed the columns slightly different to "unique custome id" etc. but still get the error.
    How, you don't say how you tried that, show us the code. I would also point out that customer_id cannot be the primary key in the movies_downloaded table either, if it was, the customers would only be able to download 1 movie, EVER, because they cannot appear in that table more than once.
    Same with movie id, if that, on ut's own, is made the PK, then each movie could only ever be downloaded once.
    You need all three of those columns to be part of the Primary Key.

    I tried making a composite key
    Spot On! Exactly what you need.

    but firstly that gives me the option to have 2 primary keys but I only need 1 plus primary and 2 foreign keys
    No it doesn't, it gives you the option to have multiple columns as part of the primary key

    Code:
    alter table "M_MOVIES_DOWNLOADED" add constraint M_MOVIES_DOWNLOADED_PK primary key (DOWNLOAD_DATE, CUSTOMERS_ID,
    MOVIES_ID)
    As Anacedent posted, spend some time immersed in the documentation to understand the concepts of Primary and foreign keys.

  10. #10
    Join Date
    Oct 2013
    Posts
    36
    Ok thank you you've definitely taken some confusion away, but then what do I put down as the primary key in my Downloads table then?

    I only have download_dates, movie_id & customer_id columns available in the downloads table so I have to choose one of them. This is a part of a scenario so I can't added any more columns.

    For now, I have made Customer ID the primary key and added the other 2 as constraints to it. I renamed movie_id to unique_movie_id & customer_id to unique_customer_id so I don't get the duplicate name error. I tried to make unique_movie_id as foreign key to movie_id which is a primary key in my M_AVAILABLE_MOVIES table but it isn't working...

    this is the error I get:

    The TABLE operation was not successful for the following reason:
    ORA-02264: name already used by an existing constraint

    and this is the sql code:

    alter table "M_DOWNLOAD_OF_MOVIE" add constraint "M_DOWNLOAD_OF_MOVIE_CON" foreign key ("UNIQUE_MOVIE_ID") references "M_AVAILABLE_MOVIES" ("MOVIE_ID") /

    Same thing when I tried to link the unqiue_customer_id to customer id.
    Last edited by london34; 10-31-13 at 13:40.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    02264, 00000, "name already used by an existing constraint"
    // *Cause: The specified constraint name has to be unique.
    // *Action: Specify a unique constraint name for the constraint.


    which part of above do you NOT understand?

    >alter table "M_DOWNLOAD_OF_MOVIE" add constraint "M_DOWNLOAD_OF_MOVIE_CON" foreign key ("UNIQUE_MOVIE_ID") references "M_AVAILABLE_MOVIES" ("MOVIE_ID")

    It means that you previously made a constraint named "M_DOWNLOAD_OF_MOVIE_CON"
    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.

  12. #12
    Join Date
    Dec 2007
    Posts
    253
    For now, I have made Customer ID the primary key
    Don't do that, it's pointless. You should be making ALL THREE columns part of the Primary Key. 1 Primary Key, Three Columns. You can then ALSO set the appropriate columns to reference Primary Keys in other table e.g. the customer id in the downloads table would reference the customer id in the ustomer table. The cutomer id in the customer table is a primary key, the customer id in the downloads table is part of the download table's primary key it is also a foreign key that point to the customer table.

  13. #13
    Join Date
    Oct 2013
    Posts
    36
    Quote Originally Posted by anacedent View Post
    02264, 00000, "name already used by an existing constraint"
    // *Cause: The specified constraint name has to be unique.
    // *Action: Specify a unique constraint name for the constraint.


    which part of above do you NOT understand?

    >alter table "M_DOWNLOAD_OF_MOVIE" add constraint "M_DOWNLOAD_OF_MOVIE_CON" foreign key ("UNIQUE_MOVIE_ID") references "M_AVAILABLE_MOVIES" ("MOVIE_ID")

    It means that you previously made a constraint named "M_DOWNLOAD_OF_MOVIE_CON"
    Sorry that's my stupidity got to me. I just renamed the table and it's working. I do appreciate you guys trying to help, I'm just not feeling Oracle right now and also in a hurry to do a few little things.

    Quote Originally Posted by pablolee View Post
    Don't do that, it's pointless. You should be making ALL THREE columns part of the Primary Key. 1 Primary Key, Three Columns. You can then ALSO set the appropriate columns to reference Primary Keys in other table e.g. the customer id in the downloads table would reference the customer id in the ustomer table. The cutomer id in the customer table is a primary key, the customer id in the downloads table is part of the download table's primary key it is also a foreign key that point to the customer table.

    Ok, but then what should I use as the primary key in this table if I'm going to make all of them as columns?
    Still learning..

  14. #14
    Join Date
    Dec 2007
    Posts
    253
    Ok, but then what should I use as the primary key in this table if I'm going to make all of them as columns?
    I said
    You should be making ALL THREE columns part of the Primary Key
    I thought that was about as clear and explicit as I could get...

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Redundancy is the best way to teach idiots.
    Redundancy is the best way to teach idiots.
    Redundancy is the best way to teach idiots.
    Redundancy is the best way to teach idiots.

    sooner or later I'll understand what you are saying.
    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.

Posting Permissions

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