The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

CREATE TABLE and constraints: expressing rules from the schema

A table is not just a list of columns. It must also express how each row is identified, what relationships are valid, and what values ​​it can accept.

Constraints are rules enforced by the table. They protect the model even when data arrives from different applications, imports, or scripts.

Main elements

IDENTITY: generate a numeric value

IDENTITY asks SQL Server to generate a number for every new row. The first argument sets the initial value and the second sets the increment.

Id BIGINT IDENTITY(1, 1) NOT NULL

PRIMARY KEY: identify every row

PRIMARY KEY requires the column or column combination to be unique and not accept NULL. A table can have only one primary key.

CONSTRAINT PK_Tabla
    PRIMARY KEY (Id)

FOREIGN KEY: require a valid reference

FOREIGN KEY relates columns in the current table to a candidate key in another table and rejects references to nonexistent rows.

CONSTRAINT FK_Tabla_Relacion
    FOREIGN KEY (IdRelacionado)
    REFERENCES dbo.TablaRelacionada (IdRelacionado)

A candidate key is a column or column combination capable of identifying a row through a primary or unique constraint.

UNIQUE: prevent repeated combinations

UNIQUE prevents the specified value combination from repeating. It can express a rule other than the primary key, such as preventing a customer from marking the same product twice.

CONSTRAINT UQ_Tabla_Columnas
    UNIQUE (Columna1, Columna2)

CHECK: validate a condition

CHECK accepts a row when its condition is not false. We must therefore consider what happens with NULL and combine it with nullability when the rule requires a value.

CONSTRAINT CK_Tabla_Regla
    CHECK (Condicion)

DEFAULT: provide a value when a column is omitted

DEFAULT supplies a value when an INSERT does not include the column. It does not replace an explicitly supplied NULL.

CONSTRAINT DF_Tabla_Columna
    DEFAULT Expresion

The example uses SYSDATETIME() as the registry default value. The function gets the current date and time from the server and returns a value compatible with DATETIME2.

Example: table with explicit rules

CREATE TABLE dbo.ProductoFavorito
(
    IdFavorito BIGINT IDENTITY(1, 1) NOT NULL,
    IdCliente INT NOT NULL,
    IdProducto INT NOT NULL,
    Prioridad TINYINT NOT NULL
        CONSTRAINT DF_ProductoFavorito_Prioridad DEFAULT 1,
    FechaRegistro DATETIME2(0) NOT NULL
        CONSTRAINT DF_ProductoFavorito_Fecha DEFAULT SYSDATETIME(),

    CONSTRAINT PK_ProductoFavorito
        PRIMARY KEY (IdFavorito),

    CONSTRAINT UQ_ProductoFavorito_ClienteProducto
        UNIQUE (IdCliente, IdProducto),

    CONSTRAINT CK_ProductoFavorito_Prioridad
        CHECK (Prioridad BETWEEN 1 AND 5),

    CONSTRAINT FK_ProductoFavorito_Cliente
        FOREIGN KEY (IdCliente)
        REFERENCES dbo.Cliente (IdCliente),

    CONSTRAINT FK_ProductoFavorito_Producto
        FOREIGN KEY (IdProducto)
        REFERENCES dbo.Producto (IdProducto)
);

The primary key identifies each row uniquely. The unique constraint prevents a combination of values from being repeated. Foreign keys require a reference to point to an existing row; here, to valid customers and products.

IDENTITY does not replace a key

IDENTITY generates a number, but does not automatically create a PRIMARY KEY constraint nor does it itself guarantee that the value represents the business rule.

Breaks in a IDENTITY column are normal. We should not promise a sequence without gaps or use it as a legal or documentary consecutive number without specifically designing that requirement.

Default values

DEFAULT is used when the column is omitted in INSERT. It does not automatically replace an explicitly sent NULL.

Naming constraints makes it easier to find and modify them later. Names should follow a consistent convention within the database.

Choose types and nullability

The type must represent actual range, precision, and length. NOT NULL expresses that a valid row always needs that data; allowing nulls for convenience carries ambiguity to all subsequent queries.

A text should also not be given a maximum length out of habit. The choice affects storage, indexes, validation, and integration with other systems.

What should be checked

Before creating the table, we review names, schema, collation, expected growth, business keys, and query patterns. Collation defines how text is compared and sorted. A business key identifies an entity with domain data, such as an official number, rather than only an internal identifier. Constraints must represent true rules, not temporary coincidences in current data.

A foreign key protects referential integrity: it prevents references to nonexistent rows. It does not necessarily create the most suitable index on child columns; an index organizes values to make rows easier to access. We must evaluate expected access and deletions.