Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Posts
    5

    Unhappy Unanswered: Problem with primary key

    Hi,


    can anybody tell me the following information.

    Can i use same primary key for more than two different tables.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Table <-> Primary key = one to one relationship.

    What do you mean "same primary key"?

    Primary key(s) are defined on a table's column(s):

    Table <-> Primary key = one to one relationship.

    If two tables have same column defined, yes you can create a primary key on that column for each table.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2008
    Posts
    5

    Red face

    You said that If two tables have same column defined, yes you can create a primary key on that column for each table. Say for example i have the following:


    1.Cashdeposit (accountno,customerId,amount,date,message)

    2.Cashwithdraw (accountno,customerId,amount,date,message)

    can i make accountno as a primary key for both cashdeposit and cashwithdraw tables?

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation Same key - separate index

    Yes, but be aware that each table will have their own separate index on their corresponding PK column.

    PS: On the example tables you posted, you may need some additional columns to make the PK unique, like: (accountno, customerId and date).
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jun 2008
    Posts
    5
    thank you so much

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What prevents you from testing it yourself?

    Here's an example: we'll try to create both tables with the same constraint name:
    Code:
    SQL> CREATE TABLE cashdeposit
      2    (accountno NUMBER CONSTRAINT pk_cd PRIMARY KEY,
      3     message   VARCHAR2(20)
      4    );
    
    Table created.
    
    SQL> CREATE TABLE cashwithdraw
      2    (accountno NUMBER CONSTRAINT pk_cd PRIMARY KEY,
      3     message   VARCHAR2(20)
      4    );
      (accountno NUMBER CONSTRAINT pk_cd PRIMARY KEY,
                                   *
    ERROR at line 2:
    ORA-02264: name already used by an existing constraint
    Nope, we can't do that - constraint names must be unique. Let's do that:
    Code:
    SQL> l2
      2*   (accountno NUMBER CONSTRAINT pk_cd PRIMARY KEY,
    SQL> c/pk_cd/pk_cw
      2*   (accountno NUMBER CONSTRAINT pk_cw PRIMARY KEY,
    SQL> l
      1  CREATE TABLE cashwithdraw
      2    (accountno NUMBER CONSTRAINT pk_cw PRIMARY KEY,
      3     message   VARCHAR2(20)
      4*   )
    SQL> /
    
    Table created.
    
    SQL>
    That's OK - we have created primary keys for two different tables on the same column name.

    If you think that we could fool Oracle by not specifying primary key name, we can try that too:
    Code:
    SQL> DROP TABLE cashdeposit;
    
    Table dropped.
    
    SQL> DROP TABLE cashwithdraw;
    
    Table dropped.
    
    SQL> CREATE TABLE cashdeposit
      2    (accountno NUMBER PRIMARY KEY,
      3     message   VARCHAR2(20)
      4    );
    
    Table created.
    
    SQL> CREATE TABLE cashwithdraw
      2    (accountno NUMBER PRIMARY KEY,
      3     message   VARCHAR2(20)
      4    );
    
    Table created.
    
    SQL>
    Cool, isn't it? Two tables sharing the same column name, having primary keys created on these columns! However, constraint names are still unique:
    Code:
    SQL> SELECT table_name, constraint_type, constraint_name
      2  FROM USER_CONSTRAINTS
      3  WHERE table_name LIKE 'CASH%';
    
    TABLE_NAME                     C CONSTRAINT_NAME
    ------------------------------ - ------------------------------
    CASHDEPOSIT                    P SYS_C0016132
    CASHWITHDRAW                   P SYS_C0016133
    
    SQL>
    You may check USER_INDEXES - the result will be the same.

    Once again, why didn't you do that yourself?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Once again, why didn't you do that yourself?
    It is easier to have you do the work rather than me.
    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
  •