Unique composite index with primary key

1
votes

I am using postgresql.

In each table, uuid is set as the primary key in the "id" column.

At this time, in some conditional clause When using "id" and "name" as conditions,

Is a composite index of "id" and "name" ["unique"] meaningful?

Since the "id" is already used as a unique key, I think the "name" behind it is meaningless.

If this is the case, is there no need for a composite index for all unique columns?

Thank you!

[Edit]

Postgresql version : v9.1

[I did]

I created two indexes "id" with "name" composite unique index and only "id" unique index.

And if I look at the query with "id" and "name" as conditions and the query with "id" as the condition as execution plans,

I noticed that the two execution plans use different indexes.

1
postgresqlindexingprimary-keycomposite-key
It's true that a superset of UNIQUE columns must be unique, and the DBMS will enforce that. But also a FK must reference reference a declared UNIQUE column set. That's just how the language is defined. PS What do you mean, "meaningless"? It's meaningful, it says, unique. It's just redundant for saying "unique". But it still might be needed for other reasons. PS It would help if you gave a minimal reproducible example. We don't know why you are asking. We don't know whether you are in one of these FK cases where declaration is needed even though the language could have required something not logicall redundant. - philipxy
Does this answer your question? For a composite foreign key, is a/why is a composite UNIQUE constraint in the referenced table required for a column combination with a primary key? - philipxy
We can expect that this is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See How to Ask & the voting arrow mouseover texts. If you post a question, use one phrasing as title. - philipxy
For code debugging questions & even to be very clear in any code question: Please in code questions give a minimal reproducible example--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. How to Ask For debugging: Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. - philipxy
Googling with site:stackoverflow.com, I don't find it easy to find duplicates about one declared PK/UNIQUE within/containing another or whether it's redundant or meaningless or (dis)allowed. - philipxy

1 Answers

1
votes

Yes, that additional constraint is meaningless: if id is unique by virtue of being the primary key, the combination of id and name is unique as well.

The need for this conceptually unnecessary unique constraint arises because a foreign key has to reference a primary key or unique constraints that contains exactly the targeted rows. Otherwise it might not be clear which of several constraints is referenced by a certain foreign key.