A unique constraint prevents duplicate non-null values in a column. If an inserted or updated value already appears in another row, the database reports an error. section 19.1.3
Keeping shipment tracking numbers distinct
A shipment table can declare TrackingNumber as UNIQUE. That rule ensures one tracking number is never assigned to two shipments, while a primary key still identifies each shipment row. section 19.1.3 section 18.3.2
CREATE TABLE shipment (
ShipmentID int NOT NULL,
OrderID int NOT NULL,
ShipmentCost decimal(5,2) NOT NULL,
ShipmentMethod char(1) NOT NULL,
TrackingNumber varchar(20) NOT NULL UNIQUE,
ShipmentDate datetime NOT NULL DEFAULT (CURRENT_DATE()),
CONSTRAINT PK_shipment PRIMARY KEY (ShipmentID),
CONSTRAINT FK_shipment_orderheader FOREIGN KEY (OrderID)
REFERENCES orderheader(OrderID)
);
This complete table definition applies a UNIQUE constraint directly to the required TrackingNumber column. section 19.1.3
Keep these differences in mind:
- A table may have multiple UNIQUE constraints, but only one PRIMARY KEY constraint. section 19.1.3
- UNIQUE constraints can allow NULL values; the permitted number varies by database system. section 19.1.3
- A UNIQUE constraint may create an index automatically in MySQL, whereas other systems may require a separate index. section 19.3.2
