sql / intermediate
Snippet
Multi-Column Candidate Keys using Composite UNIQUE Constraints
Defining a composite UNIQUE constraint enforces uniqueness across a combination of multiple columns. Relational database engines automatically back this constraint with an underlying composite index to accelerate search performance and guarantee data integrity.
snippet.sql
sql
1
2
3
4
5
6
CREATE TABLE product_variants (product_id INT NOT NULL,sku_code VARCHAR(32) NOT NULL,color_code VARCHAR(10) NOT NULL,CONSTRAINT uq_product_variant UNIQUE (product_id, color_code));
Breakdown
1
CREATE TABLE product_variants (
Initiates the schema definition for the product variants table.
2
product_id INT NOT NULL,
Declares an integer column for the parent product reference that cannot be NULL.
3
sku_code VARCHAR(32) NOT NULL,
Defines a variable-length string column storing the stock keeping unit identifier.
4
color_code VARCHAR(10) NOT NULL,
Defines a variable-length string column storing the product color variant designation.
5
CONSTRAINT uq_product_variant UNIQUE (product_id, color_code)
Enforces uniqueness on the combination of product ID and color code while generating a supporting composite index.
6
);
Closes the table definition block.