CDR – Freepbx – Safe To Add Primary Key To Table ?

Home » Asterisk Users » CDR – Freepbx – Safe To Add Primary Key To Table ?
Asterisk Users 6 Comments

Hello,

I need to develop an application that will query (mostly reading) an existing MySQL CDR database. This database (named Asteriskcdrdb) was created during Freepbx 2.10 install on my asterisk 1.8 setup. This database has a single CDR table which is filled by Asterisk.

The tools I’m planning to use require this table to include a Primary Key. Is it safe to Alter this table telling it to use UniqueID column as a Primary Key ?

(Sure, I’ll test this on a database copy but I’m not confident my tests will cover everything)

Regards

6 thoughts on - CDR – Freepbx – Safe To Add Primary Key To Table ?

  • Yes, go for it. However I have added another autoincrement column and created the primary key on it. On the other columns I need to search I have created just an index.

    Leandro

    2012/12/6 Olivier

  • 2012/12/6 Leandro Dardini

    May I ask why did you choose to add a new column ?
    I’m hesitating between both solutions. For me, your solution has the advantage that, if, for any reason, a uniqueid is missing, it would still work ok.

  • It seems like a safe thing to do. You could also ask about the impact of making an existing column a
    primary key, in a MySQL forum.

    Leandro’s solution seems to be a good one as well and does guarantee uniqueness.

    Ron

  • The reason I add a new column autoincrement is due to the fact I trust more mysql about uniquness than asterisk.

    Leandro

    I am typing from my mobile phone… Il giorno 06/dic/2012 19:11, “Ron Wheeler”
    ha scritto:

  • This thread confuses me. I’ve not worked with the Asterisk MySQL CDR, but have worked with SQL for years.

    Every table should have a primary key. Is no column identified as a PK?

    If there is a PK, you will not be able to designate another column as PK.

    If there is a PK, you don’t need to worry about Asterisk duplicating keys in that column-the database will not permit non-unique PKs.

    If (and this would be really weird) the table is created with no primary key, it shouldn’t be a problem to add the primary key constraint to a column IF there are no nulls or duplicates existing.

    –Don

    From: asterisk-users-bounces@lists.digium.com
    [mailto:asterisk-users-bounces@lists.digium.com] Yes, go for it. However I have added another autoincrement column and created the primary key on it. On the other columns I need to search I have created just an index.

    Leandro

    2012/12/6 Olivier

    Hello,

    I need to develop an application that will query (mostly reading) an existing MySQL CDR database. This database (named asteriskcdrdb) was created during Freepbx 2.10 install on my asterisk 1.8 setup. This database has a single CDR table which is filled by Asterisk.

    The tools I’m planning to use require this table to include a Primary Key. Is it safe to Alter this table telling it to use UniqueID column as a Primary Key ?

    (Sure, I’ll test this on a database copy but I’m not confident my tests will cover everything)

    Regards

  • You can also (in mysql) tag a column as “unique”:

    alter table blah add unique(column_name);

    This doesn’t add a key AFAIK unless you ask it to be a key:

    alter table blah add unique key(column_name);

    If you will never refer to a row using the auto-increment column, why have it?

    No problem having a table with no keys whatsoever, if you never plan to pull up records without dumping the whole table. But agree, that would be odd in this instance, since presumably you would at least want to have key on the source or destination columns.

    j