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

Question

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

I would add an auto-generated column and then the appropriate unique constraints. — Gordon Linoff, Mar 12 '19 at 22:11
Will you be referencing this table's primary key from child tables through FK constraints? — Sam M, Mar 13 '19 at 03:51
Explain how we are to read that code block. Also, write a clear statement of what you are trying to say by it. You comment that col1-col5 combinations are unique. Clarify via edits, not comments. You say "normalization" but your question doesn't show that you know what it means. What design process are you following from what reference & where are you in it? You don't seem to know basics. You could declare (col1,col2,col3,col4,col5) UNIQUE. There can be at most one PK & it means UNIQUE NOT NULL. But this question is too broad--it asks for a tutorial on normalization--yet is a faq. [ask] — philipxy, Mar 13 '19 at 08:14
... Before considering posting please always google many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. (Here, both your questions--what to declare & whether to introduce a surrogate.) PS Manuals for languages & tools to record & use designs are not textbooks on doing information modeling & database design. — philipxy, Mar 13 '19 at 08:14

Lluxed · Answer 1 · 2019-03-13T02:42:39.663

If you set the columns as UNIQUE, will fail because col1 cannot be equal on 2 diferent rows.

But if you set the columns as PRIMARY KEY but not UNIQUE, the database assumes that the combination of all the primary keys must be the 'UNIQUE' value, so col1+col2+col3+col4+col5 cannot be found on any other row.

Hope it helps.

EDIT

Here an example:

create table example (
col1    bigint  not null unique,
col2    bigint  not null,
primary key (col1,col2));

insert into example values(1,1);  ==> Success
insert into example values(1,2);  ==> Failure - col1 is unique and '1' was used
insert into example values(2,1);  ==> Success - '2' was never used on col1
insert into example values(2,7);  ==> Failure - '2' was already used on col1

But if you use instead:

create table example (
col1    bigint  not null,
col2    bigint  not null,
primary key (col1,col2));

insert into example values(1,1);  ==> Success
insert into example values(1,2);  ==> Success
insert into example values(2,1);  ==> Success
insert into example values(1,2);  ==> Failure - '1','2' combination was used

Thank you. The columns don't need to be unique individually. I want to set the combination of col 1-5 unique. Is setting the columns primary better than this approach? — Prasana Ramesh, Mar 12 '19 at 23:01

score 0 · Accepted Answer · answered Mar 17 '19 at 20:00

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.)

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

2 Answers2

EDIT