I have several tables in which “whole” items are stored across multiple columns for each of their “parts,” and I wish to ensure that the “whole” is unique. These tables each contain a generated column that return the “whole” in its proper format.
For example, I have an “addresses” table with columns “address_id, line1, line2, city, state, ZIP, ZIP4” and the generated column “full_address” as
CONCAT(line1, CHAR(10 using utf), IFNULL(line2, '', CONCAT(line2, CHAR(10 using utf))), city, ', ', state, IFNULL(zip4, zip, CONCAT_WS('-', zip, zip4)))
My question is: is it better to use a multi-column unique constraint across line1, line2, city, state and ZIP or a single-column unique constraint on full_address? What differences/tradeoffs occur under the hood? Are the differences proportional to the number of columns being combined?