1

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.

Anna K.
  • 1,887
  • 6
  • 26
  • 38

2 Answers2

3

SQLite has a limit of 2000 columns in an index.

The performance of a 4-column index will not be much different from that of a 3-column index.

However, generic performance statement are not interesting. What matters for you is whether this table structure is performant enough for you, which you can find out only by measuring it.

Please note that if you do lookups on only one column, that column must be the first one in an index to allow the index to be used.

CL.
  • 173,858
  • 17
  • 217
  • 259
1

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
Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • but the ID is worthless to me because I need to query by name, type and parent mostly. `slug` doesn't really matter that much, but it needs to be unique – Anna K. Jun 06 '13 at 16:02
  • Is the table ever being joined to other tables? I should have asked that first I guess, just not used to thinking about DB's with one table. – Hart CO Jun 06 '13 at 16:03
  • I'm not using joins yet, but the parent column is linked to the "name" column of another table. But now that you mentioned IDs, I think I could change the code to use IDs somehow :s – Anna K. Jun 06 '13 at 16:08
  • If doing any linking I'd say it's safe to say that ID's would be better than names. – Hart CO Jun 06 '13 at 16:14
  • 1
    While adding id fields might be usually recommended, I recommend that you don't unless you have a good reason for doing so. Composite primary keys are fairly normal in data warehousing fact tables and in tables that set up many to many relationships. Basically, you have to know why you are considering a composite key in the first place. – Dan Bracuk Jun 06 '13 at 16:31
  • If there's not much performance impact, could an ID save space? I mean if I use the ID to reference foreign columns instead of `name` (what I have now), I would have a number that references a table that has the name. And not a name that references a table that has the same name as column. So I can avoid having duplicate strings? – Anna K. Jun 06 '13 at 17:13
  • You can definitely save space with ID's over composite keys in situations where you'll be storing a foreign key. – Hart CO Jun 06 '13 at 17:18