Database constraints are rules that prevent inconsistent data. A primary key identifies each row, a unique constraint prevents duplicate values in another identifier, and a foreign key requires a reference to match an eligible key in another table. The phrase “via Field” does not identify a database product here, so the explanations below describe general relational database concepts—not a Field-specific feature.
What does a primary key do?
A primary key is the table’s designated identifier for its rows. Its value must be unique and not null, so every row can be identified without ambiguity. A table has one primary key constraint, though that constraint can cover one column or several. PostgreSQL documents primary keys as unique and non-null: PostgreSQL 18: Constraints.
As an Amazon Associate I earn from qualifying purchases.
For example, in Customers(customer_id, email), customer_id can be the primary key. Each customer must have a different, non-null ID.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11How is a unique constraint different?
A unique constraint prevents duplicate values in a column or combination of columns. It can protect a second identifier, such as a customer’s email, while the primary key remains the table’s designated row identifier. A unique constraint is not automatically the primary key.
#1 Best Overall
Null behavior for unique constraints varies by database system. Check the documentation for the engine you use rather than assuming that every system treats multiple nulls the same way. PostgreSQL describes its unique-constraint behavior in its constraints documentation; SQL Server documents its own rules for primary and foreign key constraints.
What does a foreign key require?
A foreign key links a value in one table to an eligible key in another table. The referencing column is usually on the child side; the referenced key is on the parent side. This relationship preserves referential integrity by requiring the reference to match a key in the referenced table.
In the example, Orders(order_id, customer_id) can use customer_id as a foreign key referencing Customers.customer_id. An order must refer to an existing customer. The foreign key does not have to be unique, so many orders can refer to the same customer. SQL Server describes foreign-key relationships and eligible referenced keys in its official documentation.
What is a composite key?
A composite key uses more than one column; uniqueness applies to the combination, not necessarily to each column individually. For example, a linking table could use (product_id, vendor_id) as its primary key. A product may appear with multiple vendors, and a vendor may supply multiple products, while each product-vendor pair remains unique. SQL Server uses a product/vendor combination to illustrate composite keys in its key-constraint guidance.
Rank #3
Why does the database engine matter?
Declaring a constraint does not guarantee identical behavior across database products. PostgreSQL documents constraints that the database enforces, while Google BigQuery states that its declared primary and foreign key constraints are not enforced; data owners must keep the data conformant. BigQuery says these keys are typically used to ensure data integrity and enable query optimization, but declarations alone do not reject inconsistent writes. See BigQuery’s primary and foreign key documentation and PostgreSQL 18’s constraint documentation.
Other implementation details also depend on the product. For example, SQL Server does not automatically create an index on foreign-key columns; Microsoft notes that indexing them can help queries that use the relationship. Index decisions should reflect the workload, not the mere presence of a foreign key. SQL Server documentation.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What happens when referenced data changes?
Foreign keys can specify what happens when a referenced row is updated or deleted—for example, cascading a change or setting a reference to null. These are schema choices, not automatic consequences of every foreign key. Available actions and their exact rules vary by engine. PostgreSQL documents these options in its foreign-key rules; SQL Server and Microsoft Access also document configurable referential actions: SQL Server and Microsoft Access.
Quick way to tell the three constraints apart
- Primary key: Which value or combination identifies this row? It must be unique and non-null.
- Unique constraint: Which other value or combination must not be duplicated?
- Foreign key: Which value must match an eligible key in another table?
Before relying on a constraint, confirm how your database engine handles enforcement, composite keys, nulls, referential actions, and indexes. Those details can change the behavior readers might otherwise assume from the constraint’s name.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
