1

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.

hyundeock
  • 445
  • 1
  • 6
  • 15
  • 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 [mre]. 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 Oct 07 '20 at 05:26
  • 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?](https://stackoverflow.com/questions/38183102/for-a-composite-foreign-key-is-a-why-is-a-composite-unique-constraint-in-the-re) – philipxy Oct 07 '20 at 05:34
  • 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 [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. – philipxy Oct 07 '20 at 05:34
  • For code debugging questions & even to be very clear in any code question: Please in code questions give a [mre]--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. [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 Oct 07 '20 at 05:37
  • 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 Oct 07 '20 at 05:49
  • @philipxy First of all, thank you for your detailed answer and advice. However, it's not that I haven't looked for some documents and resources. I organized my thoughts on the basis of the data I looked up and asked a question, but it seems that a misunderstanding has arisen due to lack of understanding of the data. Sorry for this part – hyundeock Oct 07 '20 at 06:00
  • What misunderstanding? Your question is reasonable. (Except for "meaningless" vs "redundant".) – philipxy Oct 07 '20 at 06:04

1 Answers1

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263