Primary key composite not self incremental or simple primary key not auto incremental in MySQL?

Question

Primary key composite not self incremental or simple primary key not auto incremental in MySQL?

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have a table with the following structure:

CAMPO           TIPO                        NULL    KEY     DEAFULT     EXTRA
id_laudes       int(11)                     NO      PRI     NULL        auto_increment
id_liturgia     int(11)                     NO      UNI     NULL    
la_himno        int(10) unsigned zerofill   NO      MUL     NULL    
la_salmos       int(10) unsigned zerofill   NO              NULL    
la_biblica      int(10) unsigned zerofill   NO              NULL    
la_benedictus   int(10) unsigned zerofill   NO              NULL    
la_preces       int(10) unsigned zerofill   NO              NULL    
la_oracion      int(10) unsigned zerofill   NO              NULL

The index information shows the following:

Key_name    Seq_in_index    Column_name     Collation   Cardinality Sub_part    Packed      Index_type  
PRIMARY         1           id_laudes       A           241         NULL        NULL        BTREE       
unique_index    1           la_himno        A           120         NULL        NULL        BTREE       
unique_index    2           la_salmos       A           241         NULL        NULL        BTREE       
unique_index    3           la_biblica      A           241         NULL        NULL        BTREE       
unique_index    5           la_benedictus   A           241         NULL        NULL        BTREE       
unique_index    6           la_preces       A           241         NULL        NULL        BTREE       
unique_index    7           la_oracion      A           241         NULL        NULL        BTREE       
id_liturgia     1           id_liturgia     A           241         NULL        NULL        BTREE

the id_laudes column is the primary, auto-incremental key.
column id_liturgia is a unique index
the other columns are part of a multiple index

The doubt

The id_laudes column, incremental auto that acts as the primary key, is not related to any other table. For its part, the column id_liturgia can not be repeated more than once in the table, therefore, it could be a good candidate for primary key, but could not be incremental, since that column used to relate to another table in the database.

On the other hand, all the columns of the table, less id_laudes could form a unique index, since in the table you can never repeat a record that has the same values in all those columns.

Also, all columns, minus id_laudes are each related to another table in the database.

I think I could do without id_laudes . But I doubt if:

The primary key (should and / or can be) id_liturgia , taking into account that it would not be auto incremental ?. In that case, would a single index be better combined with the other columns or an index for each one?
Or can I have a composite primary key that combines all the columns in the table?

Actually I do not know what would be the best option, taking into account the relationships of each column with the other tables.

P. D.:

To shed more light on the problem, this would be the query of the laudes table, in which you can see how each column of this table relates to its respective tables:

SELECT  
        l.id_tiempo, lc.cod_liturgia, ls.santo, lsv.vida, lha.antifona_i, lhh.himno, 
        GROUP_CONCAT(COALESCE(lhsg.orden, '') ORDER BY  id_salmos  SEPARATOR '|') AS ordenes, 
        GROUP_CONCAT(COALESCE(lhsa.antifonas, '') ORDER BY  id_salmos SEPARATOR '|') AS antifonas, 
        GROUP_CONCAT(COALESCE(lhs.salmo_ref, '') ORDER BY  id_salmos SEPARATOR '|') AS salmos_ref, 
        GROUP_CONCAT(COALESCE(lhst.tema, '') ORDER BY  id_salmos SEPARATOR '|') AS temas, 
        GROUP_CONCAT(COALESCE(lhsi.intro, '') ORDER BY  id_salmos SEPARATOR '|') AS intros, 
        GROUP_CONCAT(COALESCE(lhsg.parte, '') ORDER BY  id_salmos SEPARATOR '|') AS partes, 
        GROUP_CONCAT(COALESCE(lhs.salmo, '') ORDER BY  id_salmos SEPARATOR '|') AS salmos,  
        lhlb.ref, lhlb.biblica, biblica_resp, id_forma, antifona_ce, preces_intro, preces, lo.oracion,lh.la_salmos

    FROM liturgia_calendario lc     
    JOIN liturgia l ON lc.id_liturgia=l.id_celebracion 
    LEFT JOIN liturgia_horas_2 lh ON l.id_celebracion=lh.id_liturgia    
    LEFT JOIN liturgia_horas_1 lh1 ON lc.id_liturgia=lh1.id_liturgia
    LEFT OUTER JOIN liturgia_santos ls ON lc.id_liturgia=ls.id_liturgia 
    LEFT OUTER JOIN liturgia_horas_santos_vidas lsv ON lsv.id_santo=ls.id_santo 
    LEFT JOIN liturgia_horas_antifonas_join lhaj ON lh1.ol_antifona=lhaj.id_liturgia 
    LEFT JOIN liturgia_horas_antifonas lha ON lhaj.id_antifona=lha.id_antifona 
    LEFT JOIN liturgia_horas_himnos_join lhhj ON lh.la_himno=lhhj.id_liturgia
    LEFT JOIN liturgia_horas_himnos lhh ON lhhj.id_himno=lhh.id_himno 
    LEFT JOIN liturgia_horas_salmos_grupos lhsg ON lh.la_salmos=lhsg.id_liturgia 
    LEFT JOIN liturgia_horas_salmos_antifonas lhsa ON lhsg.id_antifonas=lhsa.id_antifonas
    LEFT JOIN liturgia_horas_salmos_temas lhst ON lhsg.id_tema=lhst.id_tema
    LEFT JOIN liturgia_horas_salmos_intros lhsi ON lhsg.id_intro=lhsi.id_intro
    LEFT JOIN liturgia_horas_salmos lhs ON lhsg.id_salmo=lhs.id_salmo 
    LEFT JOIN liturgia_horas_lbiblicas_join lhlbg ON lh.la_biblica=lhlbg.id_liturgia
    LEFT JOIN liturgia_horas_lbiblicas_breves lhlb ON lhlbg.id_biblica=lhlb.id_biblica
    LEFT JOIN liturgia_horas_lbiblicas_responsorios lhlbr ON lhlbg.id_responsorio=lhlbr.id_responsorio
    LEFT JOIN liturgia_horas_ce_join lhcej ON lh.la_benedictus=lhcej.id_liturgia 
    LEFT JOIN liturgia_horas_ce lhce ON lhcej.id_antifonace=lhce.id_antifonace
    LEFT JOIN liturgia_horas_preces_join lhpj  ON lh.la_preces=lhpj.id_liturgia     
    LEFT JOIN liturgia_horas_preces lhp  ON lhpj.id_preces=lhp.id_preces    
    LEFT JOIN liturgia_horas_oraciones_join lhoj ON lh.la_oracion=lhoj.id_liturgia
    LEFT JOIN liturgia_oraciones lo ON lhoj.id_oracion=lo.id_oracion 

    WHERE       lc.f_yy=:fyy    AND lc.f_mm=:fmm AND lc.f_dd=:fdd

mysql

asked by A. Cedano 09.09.2017 в 04:30

source

2 answers

Login with user type (administrator / normal) using c # How to add a table dynamically with jquery?

score 2 · Answer 1

The composite primary keys have fallen into disuse, because for very large systems, you would have to take the primary key to all the related tables. That is, if the primary key composed of 6 fields and you have 40 tables related to it you would have to repeat the insertion, update, deletion or query of those 6 fields in the 40 tables, that is not practical.

Therefore I recommend id_laudes as incremental auto primary key and id_liturgia as unique . In addition, the search operations in composite primary keys are more expensive if there are mostly non-numeric fields in the key. On the other hand, the search operations on whole-type keys are much less expensive and faster.

Update:

This is my idea that I apply for a system:

score 1 · Answer 2

Let's analyze the table, without knowing the data context, which can be really very important, especially more than how to save the data, how to recover them.

Because of what you see in the table, having a primary key that is one on one with a unique key is a mismatch. What would it do?

Does not serve as a search key, the only key is already search
It does not work to unite with other tables, you made it clear that the one that unites is the only key.

Then it is just taking up space, or saving because it is a autoincremental. But in that case, what saves us ???

The system that refers to this table and wants to relate it to others, is not interested in the auto-incremental key at all.

Until now, there is not a single reason why to keep it, then it has no sense of existence.

That leaves us the table:

~~id_lauds int (11) NO PRI NULL auto_increment~~

id_liturgia     int(11)                     NO      PRI     NULL    
la_himno        int(10) unsigned zerofill   NO      MUL     NULL    
la_salmos       int(10) unsigned zerofill   NO              NULL    
la_biblica      int(10) unsigned zerofill   NO              NULL    
la_benedictus   int(10) unsigned zerofill   NO              NULL    
la_preces       int(10) unsigned zerofill   NO              NULL    
la_oracion      int(10) unsigned zerofill   NO              NULL

The key id_liturgia is not auto-incremental, I suppose because you discharge it when you generate the liturgy. See my note below, if it should not be all in the table that (and I'm assuming your model). Equal beyond that, if there is already a table that creates that id, there would be no problem in replicating it to this table.

As far as your question is concerned, this is a pivoting table. A multiple index could be generated for all the columns, which would repeat the data in the table at least twice to generate the index. I do not see it badly, I do not know if it will be performant with a lot of data (and the word many is relative to hardware and other system issues). But the idea of the index is to control only the repetitions? I'm not throwing business problems into the database (even if it leaves you). But in this case, it might be necessary to analyze the number of keys (id) of each of the related tables, and in case of wanting to get that problem out of the DB, make a key reduced to the denser IDs. (for example if the field la_oracion, had only 7 keys and la_himno 700, indexing by la_himno is a good idea, since after a full scan of the result would be in the worst case 7 records).

NOTE: I do not know if I had armed this table as a pivot, because I did not leave this data in the same table as lit_lite_id? If, after all, the relationship is 1 to 1 between the liturgy and each of its parts.