Can a key contain several columns?

Yes. The complete combination can be unique even when individual columns repeat.

Does a generated identifier prevent every business duplicate?

No. Different generated identifiers can still describe duplicate business records unless another rule addresses them.

Are primary-key and unique constraints identical?

No. Their semantics differ, including null handling and the designated primary-key role; consult the database's documentation.

AI Glossary / Data engineering and architecture / Advanced

Key Constraint

Definition

A key constraint requires a specified attribute, or combination of attributes, to identify rows uniquely within a relation or table. It prevents distinct records from sharing the same complete identifying combination under the applicable rules. In relational theory, a candidate key is a minimal such combination; a superkey may contain unnecessary attributes. SQL implementations express related requirements through primary-key and unique constraints, with additional details such as null handling.

What it means

An identifier becomes dependable only when the data model and its enforcement support the intended uniqueness. Naming a column record_id is not enough. In a fictional training system, an enrollment may be identified by the combination of learner and offering. The learner can appear in several offerings, and an offering can contain several learners, while the pair must remain unique. For an AI reporting tool, understanding that combination helps distinguish legitimate repeated values from duplicate records. It also helps explain why selecting only part of a key can return several rows without indicating a database error.

How it works

The database checks the declared identifying columns according to its constraint semantics. PostgreSQL documents primary keys as unique and non-null, including keys formed from multiple columns. It also distinguishes unique constraints and their treatment of null values. A logical uniqueness requirement should therefore be translated deliberately into the chosen database's features. Declaring a combination unique does not prove that it is minimal or that it matches the real business identity. Those are modeling questions. Enforcement checks the stated rule; it cannot repair a rule that identifies the wrong thing.

Three practical examples

A catalog enforces a unique product identifier so two rows cannot accidentally use the same identifier for different catalog records.
An enrollment table uses learner and offering identifiers together when the business allows only one enrollment per learner in each offering.
An import process reports a key conflict for investigation rather than silently assuming that every incoming row represents a new entity.

Think of it this way

Imagine numbered seats in a theater with several performances. Seat 12 can exist at every performance, and one performance has many seats. The combination of performance and seat identifies the particular place being booked. Enforcing uniqueness on that combination prevents double allocation in the simplified model, while enforcing uniqueness on seat number alone would incorrectly prevent bookings across different performances.

Worked example

Consider an original fictional Enrollment table with columns Learner, Offering and Status. Its rule permits at most one row for each Learner - Offering pair, and both identifying values are required. Existing rows are (L1,O1,active), (L2,O1,active) and (L1,O2,active). These are three distinct key combinations, so repeated L1 and repeated O1 individually are acceptable. An attempted row (L1,O1,canceled) conflicts with the existing pair even though Status differs. A change to the existing enrollment may be appropriate, depending on the business operation, rather than inserting a second row. If the system later needs several attempts by one learner in one offering, this key no longer expresses the revised requirement. Adding Attempt to the identifying combination may be a candidate design, but it requires explicit rules about what an attempt represents.

Illustrative case study

In a fictional AI-assisted data import, the assistant suggests assigning a new generated identifier to every incoming row. The identifiers are unique, yet two rows describe the same learner's enrollment in the same offering. Reviewers distinguish technical row identification from the business rule against duplicate enrollments. They retain the generated identifier where useful and separately model the required learner - offering uniqueness. They test a repeated import, a legitimate enrollment in another offering and a status correction. This scenario illustrates why one unique column does not automatically enforce every identity rule. It does not prescribe one key design for all training systems.

Common misconceptions and mistakes

A composite key does not require each component to be unique independently. Another mistake is assuming a unique combination observed in a small sample will remain unique for every permitted future record. Key design should follow the data's meaning and rules. A foreign-key constraint serves a different purpose: it checks a reference to an appropriate target rather than simply declaring the referencing values unique. Finally, uniqueness does not establish factual accuracy. A table can assign distinct identifiers to inaccurate records or to two representations of the same real-world entity if the modeled rule does not detect that duplication.

Business use

For an AI application that reads or writes structured data, document the unit each row represents and how that unit is identified. In the fictional enrollment example, distinguish a learner, an offering and an enrollment before selecting identifying columns. Decide how retries and corrections should be handled when the same key is encountered. Test both an actual duplicate and a legitimate repeated component value. When requirements change, review dependent joins, imports and reports as well as the constraint itself. This prevents a local schema adjustment from silently changing the meaning of counts or update operations.

Check your understanding

Why are the first three toy rows valid?

Answer: Their learner - offering pairs differ.

Why is the canceled row rejected as an insertion?

Answer: It repeats the pair L1,O1.

Would making Status part of the unique combination preserve the original one-enrollment rule?

Answer: No; it could allow several rows for the same pair with different statuses.

What must be reconsidered when multiple attempts become legitimate?

Answer: The row's meaning and identifying rule.

Related terms

Learn next

Describe a small table and its intended row meaning. Propose one single-column key and one composite alternative, then challenge each with realistic examples. Include a repeated component value, a duplicate business record and a correction. State which requirements the database can enforce directly and which require a clearer definition of business identity.

Sources

PostgreSQL documentation, Constraints, primary-key and unique-constraint sections: https://www.postgresql.org/docs/current/ddl-constraints.html The enrollment example, theater analogy and import scenario are original educational illustrations. The supplied term is interpreted as an intra-table uniqueness requirement. PostgreSQL supplies a concrete implementation reference; null behavior and enforcement details are not assumed identical across all SQL systems.

Previous: Kernel trick Next: Key management