Failed when try adding primary key constraint to partition table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Failed when try adding primary key constraint to partition table

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    Failed when try adding primary key constraint to partition table

    Hi all,

    I've created a partition table and local index partition on the coulmn which suppose to be the primary key:
    SQL> CREATE TABLE TRP.PARTIAL_TBL_CDR
    2 (
    3 CDR_ID NUMBER(14) NOT NULL,
    4 SITE_NUMBER NUMBER(3),
    5 FE_NUMBER NUMBER(3),
    6 BSFW NUMBER(5),
    7 TSFW NUMBER(5),
    8 DIRECTION NUMBER(1),
    9 DNR VARCHAR2(32) DEFAULT 'N/A',
    10 CLI VARCHAR2(20) DEFAULT 'N/A',
    11 START_TIME DATE,
    12 END_TIME DATE NOT NULL,
    13 DURATION NUMBER,
    14 DTMF VARCHAR2(128),
    15 COMPLETED NUMBER(1),
    16 DPC NUMBER(10),
    17 OPC NUMBER(10),
    18 CIC NUMBER(10),
    19 TERMINATION_CAUSE NUMBER(10),
    20 LOCATION NUMBER(3),
    21 CODING_STANDARD NUMBER(3),
    22 POST_DIAL_DELAY NUMBER(10),
    23 CLASSIFICATIONS VARCHAR2(50),
    24 BLUE_BOX_PRESENT NUMBER(1) DEFAULT 0,
    25 TONES VARCHAR2(10),
    26 TSI VARCHAR2(20),
    27 REDIRECTION_PRESENT NUMBER(1) DEFAULT 0,
    28 REDIRECTION_NUMBER VARCHAR2(32),
    29 ISDN NUMBER(1) DEFAULT 0,
    30 RING_TIME NUMBER(10),
    31 SEIZURE_TIME DATE,
    32 CALLING_COUNTRY_CODE VARCHAR2(7),
    33 CALLED_COUNTRY_CODE VARCHAR2(7),
    34 TRANSIT NUMBER(1),
    35 NOA_DNR NUMBER(3),
    36 TRANSMIT_MEDIUM NUMBER(5),
    37 CALLING_PARTY_CATEGORY NUMBER(5),
    38 CHARGE_NUMBER VARCHAR2(22),
    39 JURISDICTION VARCHAR2(7),
    40 CARRIER_ID VARCHAR2(5),
    41 NETWORK_ID_PLAN NUMBER(3),
    42 TRANSIT_NET_SEL VARCHAR2(5),
    43 OUT_OF_BAND_FIELD NUMBER(2),
    44 OPTIONAL_NUMBER VARCHAR2(22),
    45 ACM_FLAG NUMBER(1),
    46 RELEASE_DIR NUMBER(1),
    47 CHARGE_IND NUMBER(1),
    48 HOLD_IND NUMBER(3),
    49 SGN_POINT_CODE NUMBER(5),
    50 CONFUSION NUMBER(3),
    51 CONT_IND NUMBER(3),
    52 SS7_LINK NUMBER(3),
    53 ADDRESS_TYPE NUMBER(3),
    54 NOA_CLI NUMBER(3),
    55 ORIGINAL_DNR VARCHAR2(32),
    56 ORIGINAL_CLI VARCHAR2(20),
    57 PRICE NUMBER(17,2),
    58 PROTOCOL_TYPE NUMBER(3),
    59 TRUNK_NUMBER NUMBER(8),
    60 NETWORK_IND NUMBER(2),
    61 CALC_CDR_ID NUMBER,
    62 OWNER_ID VARCHAR2(20) DEFAULT 'N/A'
    63 )
    64 PCTUSED 90 PCTFREE 10
    65 PARTITION BY RANGE (END_TIME)
    66 (PARTITION P01 VALUES LESS THAN (TO_DATE('2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    LOGGING TABLESPACE PARTIAL_CDR_SPC_01 storage (initial 100k next 100k maxextents 2147483645),
    67 PARTITION P02 VALUES LESS THAN (TO_DATE('2004-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
    ) LOGGING TABLESPACE PARTIAL_CDR_SPC_02 storage (initial 100k next 100k maxextents 2147483645),
    68 PARTITION P03 VALUES LESS THAN (TO_DATE('2004-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
    ) LOGGING TABLESPACE PARTIAL_CDR_SPC_03 storage (initial 100k next 100k maxextents 2147483645),
    69 PARTITION p04 VALUES LESS THAN (TO_DATE('2004-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
    ) LOGGING TABLESPACE PARTIAL_CDR_SPC_04 storage (initial 100k next 100k maxextents 2147483645),
    70 PARTITION p05 VALUES LESS THAN (TO_DATE('2004-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
    ) LOGGING TABLESPACE PARTIAL_CDR_SPC_05 storage (initial 100k next 100k maxextents 2147483645),
    71 PARTITION p06 VALUES LESS THAN (TO_DATE('2004-01-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
    ) LOGGING TABLESPACE PARTIAL_CDR_SPC_06 storage (initial 100k next 100k maxextents 2147483645),
    72 PARTITION p07 VALUES LESS THAN (TO_DATE('2004-01-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
    ) LOGGING TABLESPACE PARTIAL_CDR_SPC_07 storage (initial 100k next 100k maxextents 2147483645),
    73 PARTITION p08 VALUES LESS THAN (TO_DATE('2004-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
    ) LOGGING TABLESPACE PARTIAL_CDR_SPC_08 storage (initial 100k next 100k maxextents 2147483645));

    Table created.

    SQL> CREATE INDEX TRP.PART_CDR_CDR_ID_idx
    2 ON TRP.PARTIAL_TBL_CDR (CDR_ID)
    3 PCTFREE 15
    4 NOLOGGING
    5 LOCAL (
    6 PARTITION P01 tablespace PARTIAL_CDR_IND_01 STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTE
    NTS 2147483645 PCTINCREASE 0),
    7 PARTITION P02 tablespace PARTIAL_CDR_IND_02 STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTE
    NTS 2147483645 PCTINCREASE 0),
    8 PARTITION P03 tablespace PARTIAL_CDR_IND_03 STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTE
    NTS 2147483645 PCTINCREASE 0),
    9 PARTITION P04 tablespace PARTIAL_CDR_IND_04 STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTE
    NTS 2147483645 PCTINCREASE 0),
    10 PARTITION P05 tablespace PARTIAL_CDR_IND_05 STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTE
    NTS 2147483645 PCTINCREASE 0),
    11 PARTITION P06 tablespace PARTIAL_CDR_IND_06 STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTE
    NTS 2147483645 PCTINCREASE 0),
    12 PARTITION P07 tablespace PARTIAL_CDR_IND_07 STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTE
    NTS 2147483645 PCTINCREASE 0),
    13 PARTITION P08 tablespace PARTIAL_CDR_IND_08 STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTE
    NTS 2147483645 PCTINCREASE 0));

    Index created.

    When i'm trying to add a primary key on CDR_ID column ,which have already indexed with partition local index i'm getting the following error:

    SQL> alter table TRP.PARTIAL_TBL_CDR add constraint CDR_ID_PK primary key (cdr_id);
    alter table TRP.PARTIAL_TBL_CDR add constraint CDR_ID_PK primary key (cdr_id)
    *
    ERROR at line 1:
    ORA-01408: such column list already indexed

    Why?
    How should i build local partition index on a column and use it as PRIMARY KEY?

    Thanks in advance,
    Nir

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    you have already created an index on that column, remove the index and create a primary key in the same way (or even better put t in the create table statement) - you do know you get an index by default with a primary key right?

    If you dont know how to add the primary key in the create table - read the manual

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    try the clause using index ( am not sure if it will work )
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    alter table TRP.PARTIAL_TBL_CDR
    add constraint CDR_ID_PK primary key (cdr_id)
    using index TRP.PART_CDR_CDR_ID_idx;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi guys,

    I've realized my mistake.
    The column CDR_ID is not the partition key,so i failed to create PK local on this column. Eventuallyl,i 've succeeeded adding primary on CDR_ID+partition key (END_TIME).

    Thanks anyway.
    Nir

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    Before adding your END_TIME column to the primary key I recommand you to check if you realy need a local index for your PK. Because when adding the END_TIME column to the PK you have no guarantee anymore that the column CDR_ID will be unique (only the combination END_TIME, CDR_ID will be unique). Also you couldnt anymore reference the CDR_ID with a FK from another table.
    Last edited by mike9; 02-01-2004 at 02:58 PM.

  7. #7
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Thanks Mike!!
    I'll check it.

    Regards,
    Nir

Posting Permissions

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



Click Here to Expand Forum to Full Width