sqldatabase-designmariadbcomposite-primary-keyunique-key

what is the convention for normalizing a table with many primary keys

I have a database table like so :

col1 PRI
col2 PRI
col3 PRI
col4 PRI
col5 PRI
col6 
col7
col8

So, it looks like all the columns from 1 to 5 need to be unique and that it makes 'sense' to just make those keys primary. Is this the right way of designing or should we just add a new auto generated column with a unique constraint on the 5 columns? We will query with either a subset of those columns (col1 - col3) or all 5 columns

Solution

  • This is fine; I see no need to have a 'generated' column:

    PRIMARY KEY(a,b,c,d,e)

    If you have this, it will work efficiently:

    WHERE b=22 AND c=333 AND a=4444  -- in any order

    Most other combinations will be less efficient.

    (Please use real column names so we can discuss things in more detail.)

    Yes, you could instead have UNIQUE(a,b,c,d,e), but then what would the PRIMARY KEY be?