How to define a composite key table in Sqlite?

Question

I am making an embedded database as a field medium in the integration between two systems. The system A, where you have the data, export all your data to the system B already properly mapped.

However, if I want performance, A should only send to B the changes made. So, here comes the middle of the field, M.

B informs its structure to M, for example:

{
  "colunas": [
    {"chave":true,"nome":"cd_meio_pgto"},
    {"nome":"ds_meio_pgto","chave":true}
  ],
  "nome_tabela":"meio_pagamento"
}

But composite keys can be rolled. For example:

{
  "colunas": [
    {"chave":true,"nome":"cd_meio_pgto"},
    {"nome":"ds_meio_pgto","chave":true},
    {"nome":"chave_01","chave":true}
  ],
  "nome_tabela":"meio_pagamento_exemplo"
}

For the integration to work perfectly, I need M is able to, on board, keep a good enough copy of A' (the data in A after the mappings have been applied) with the data reported by B.

For embedded base, we are studying using Sqlite. However, how to create composite primary key in Sqlite?

Considering the columns all of the type TEXT (or totally ignoring, since Sqlite allows this), as would be the tables meio_pagamento? And meio_pagamento_exemplo?

Answer 1

I found it following the path of taming across of that other answer.

Basically, if there is only one column belonging to the primary key, we can set the expression reserved PRIMARY KEY in the specific column.

If the Keywords PRIMARY KEY are Added to a column Definition, then the Primary key for the table consists of that single column

If the reserved words PRIMARY KEY were added to the definition of a column, then the table primary key consists of that single column

CREATE TABLE meio_pagamento (
  cd_meio_pgto TEXT PRIMARY KEY,
  ds_meio_pgto TEXT
)

However, we cannot extrapolate this behavior to multiple columns:

An error is Raised if more than one PRIMARY KEY clause appears in a CREATE TABLE statement

An error is thrown if more than one PRIMARY KEY clause appears in a CREATE TABLE statement

So the following is wrong:

CREATE TABLE meio_pagamento_exemplo (
  cd_meio_pgto TEXT PRIMARY KEY,
  chave_01 TEXT PRIMARY KEY,
  ds_meio_pgto TEXT
)

The correct alternative to making multiple columns in the same primary key is to place the primary key as a table constraint.

if a PRIMARY KEY clause is specified as a table-Constraint, then the Primary key of the table consists of the list of Columns specified as part of the PRIMARY KEY clause

if a PRIMARY KEY clause is specified as a table constraint, then the table primary key consists of the list of columns specified as part of the PRIMARY KEY clause

CREATE TABLE meio_pagamento_exemplo (  
  cd_meio_pgto TEXT,  
  chave_01 TEXT,  
  ds_meio_pgto TEXT,
  PRIMARY KEY (cd_meio_pgto, chave_01)
)

Note that this way of writing also works for primary keys of a single column. It’s like putting PRIMARY KEY next to the column definition was a syntactic sugar to the shape of the PRIMARY KEY as a constraint. So the following code is also valid:

CREATE TABLE meio_pagamento (  
  cd_meio_pgto TEXT,
  ds_meio_pgto TEXT,
  PRIMARY KEY (cd_meio_pgto)
)

---

If you are working with Java, you are assured that there will always be primary key and want to do this programmatically on a org.json.JSONObject:

JSONObject descricaoTabela = "{\r\n" + 
            "  \"colunas\": [\r\n" + 
            "    {\"chave\":true,\"nome\":\"cd_meio_pgto\"},\r\n" + 
            "    {\"nome\":\"ds_meio_pgto\",\"chave\":true}\r\n" + 
            "  ],\r\n" + 
            "  \"nome_tabela\":\"meio_pagamento\"\r\n" + 
            "}\r\n" + 
            "";
String nomeTabela = descricaoTabela.getString("nome_tabela");

List<Coluna> colunas = new ArrayList<>();
JSONArray descricaoColunas = descricaoTabela.getJSONArray("colunas");
for (int i = 0; i < descricaoColunas.length(); i++) {
  JSONObject descricaoColuna= descricaoColunas.getJSONObject(i);
  colunas.add(new Coluna(descricaoColuna.getString("nome"), descricaoColuna.optBoolean("chave")));
}
String sql = String.format("CREATE TABLE %s (%s, PRIMARY KEY (%s))",
            descricaoTabela.getString("nome_tabela"),
            colunas.stream().map(Coluna::getNome).map(c -> c + " TEXT").collect(Collectors.joining(",")),
            colunas.stream().filter(Coluna::isChave).map(Coluna::getNome).collect(Collectors.joining(","))
     );

Using the smarter way Collectors.joining, we can use as prefix ", PRIMARY KEY (" and suffix ")", so we can remove the restriction that the primary key should be mandatory:

JSONObject descricaoTabela = "{\r\n" + 
            "  \"colunas\": [\r\n" + 
            "    {\"chave\":true,\"nome\":\"cd_meio_pgto\"},\r\n" + 
            "    {\"nome\":\"ds_meio_pgto\",\"chave\":true}\r\n" + 
            "  ],\r\n" + 
            "  \"nome_tabela\":\"meio_pagamento\"\r\n" + 
            "}\r\n" + 
            "";
String nomeTabela = descricaoTabela.getString("nome_tabela");

List<Coluna> colunas = new ArrayList<>();
JSONArray descricaoColunas = descricaoTabela.getJSONArray("colunas");
for (int i = 0; i < descricaoColunas.length(); i++) {
  JSONObject descricaoColuna= descricaoColunas.getJSONObject(i);
  colunas.add(new Coluna(descricaoColuna.getString("nome"), descricaoColuna.optBoolean("chave")));
}
String sql = String.format("CREATE TABLE %s (%s%s)",
            descricaoTabela.getString("nome_tabela"),
            colunas.stream().map(Coluna::getNome).map(c -> c + " TEXT").collect(Collectors.joining(",")),
            colunas.stream().filter(Coluna::isChave).map(Coluna::getNome)
                .collect(Collectors.joining(/*separador*/",", /*prefixo*/", PRIMARY KEY (", /*sufixo*/")"))
     );