Search code examples
sqlsqliteindexingprimary-keycomposite

Composite primary key limit?

Is it OK if I create a composite primary key from 4 columns? Like:

PRIMARY KEY(name, slug, type, parent)

So there should not be more than one row with the same name, slug, type and parent. Are there too many columns? Will it affect performance?

I'm using sqlite btw.

Solution

  • It's usually recommended to have an ID field that is unique on its own. Comparing INTEGER values is faster than comparing strings, so your composite key will affect performance negatively.

    Adding a column with the following as the datatype would be ideal if you will be joining to other tables:

     INTEGER PRIMARY KEY AUTOINCREMENT