Many-to-many relationship table doesn’t work with varchar primary keys

  • Hi Peter,
    I’ve again tested many-to-many relationships using the plugin version 4.5.7, and my conclusion is, that NO relationship is displayed when using varchars as Primary Key in the bridged table.
    Example: In my test I relate one table “Language” with primary keys of type varchar(2) (e.g. en, de, fr, etc.) with another table “Translation Master” of strings using Integer as primary key and create the bridged table as follows (compound keys).
    language_id | tm_id
    ————|——
    en | 1
    de | 1
    …
    However, the plugin doesn’t show any relations after adding the links in the corresponding “Project Templates”.
    Only after introducing an integer as “language_id” the desired functionality could be obtained:
    language_id | tm_id
    ————|——
    1 | 1
    2 | 1
    …`
    Since I haven’t read, that using varchar is not allowed or bad practice, I wonder whether this behavior is intended or not?
    Thank you for this great plug-in! Best, Alex.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Alex,

    >>> Since I haven’t read, that using varchar is not allowed or bad practice, I wonder whether this behavior is intended or not?

    Sorry to read it does not work. This was certainly was intended! It should work with text columns just like numeric columns.

    If you still have your create table scripts with the varchar keys, can you mail them? You can use the contactform on the plugin website if you prefer to send it in private.

    Thanks,
    Peter

    Thread Starter alfastic

    (@alfastic)

    Hi Peter,

    good to know it should work. I have tested it with these scripts:

    Parent table for the n:m relationship:
    CREATE TABLEds_languages`
    (lang_id int(11) NOT NULL auto_increment
    ,lang_code varchar(2) NOT NULL
    ,lang_name varchar(20) NOT NULL
    ,PRIMARY KEY (lang_id)
    ) ENGINE InnoDB DEFAULT CHARACTER SET utf8 COLLATE=utf8_general_ci;`

    Bridged table:
    CREATE TABLEds_bridge_varcharint`
    (lang_code varchar(2) NOT NULL
    ,tm_id int(11) NOT NULL
    ,PRIMARY KEY (lang_code,tm_id)
    ) ENGINE InnoDB DEFAULT CHARACTER SET utf8 COLLATE=utf8_general_ci;`

    Child table of the n:m relationship:
    CREATE TABLEds_transmaster`
    (tm_id int(11) NOT NULL auto_increment
    ,tm_format enum(‘TST’,’TSV’) NOT NULL
    ,tm_name varchar(255) NOT NULL
    ,PRIMARY KEY (tm_id)
    ) ENGINE InnoDB DEFAULT CHARACTER SET utf8 COLLATE=utf8_general_ci;`

    I connect them via Project Templates as follows:
    Manage relationships for table ds_languages
    Type: n:m
    Source column name: lang_code
    Target table name: ds_bridge_varcharint
    Target column name: lang_code
    Relation table name (only n:m): ds_translations

    and:
    Manage relationships for table ds_transmaster
    Type: 1:n
    Source column name: tm_id
    Target table name: ds_bridge_varcharint
    Target column name: tm_id
    Relation table name (only n:m): –

    As a result: there is no child table at all
    Solution: Redefine lang_code as Integer and it will work.

    Have a good day! Best, Alex.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Many-to-many relationship table doesn’t work with varchar primary keys’ is closed to new replies.