Constraints

All constraint attributes available in Socigy.OpenSource.DB v0.1.82 — unique, foreign key, check, string length, comparison constraints, and migration rename hints.

updated 3 May 20266 min readv0.1.82

Overview

Socigy.OpenSource.DB translates C# attributes directly into PostgreSQL constraint DDL. Constraints are emitted inside the CREATE TABLE statement and in subsequent ALTER TABLE migration steps. All constraint attribute classes live in the Socigy.OpenSource.DB.Attributes namespace.

`[Unique]` — Unique Constraints

Single-Column Unique

Place [Unique] on a property to emit a UNIQUE constraint for that column:

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [Unique]
    public string Email { get; set; }
}

Generated DDL fragment:

CONSTRAINT "uq_users_email" UNIQUE ("email")

Multi-Column Unique Constraint

Place [Unique] at the class level and list the property names to create a composite unique constraint:

[Table("blog_posts")]
[Unique(nameof(AuthorId), nameof(Slug))]
public partial class BlogPost
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public Guid AuthorId { get; set; }

    [StringLength(200)]
    public string Slug { get; set; }
}

To assign a readable name, pass it as a named property argument after the positional columns:

[Unique(nameof(AuthorId), nameof(Slug))]
[Unique(nameof(AuthorId), nameof(Slug), Name = "uq_author_slug")]

Generated DDL fragment (with Name specified):

CONSTRAINT "uq_author_slug" UNIQUE ("author_id", "slug")

NOTE

The multi-column form uses the positional constructor — [Unique(nameof(Col1), nameof(Col2))]. The Columns property has a private setter, so [Unique(Columns = new[] { ... })] does not compile. When Name is omitted the framework generates an opaque random name (IX_xxxxxxxx), so always specify Name for multi-column constraints.

`[ForeignKey]` — Foreign Key Constraints

[ForeignKey] can be placed on a property (for single-column FKs) or on the class (for composite FKs).

Single-Column, Auto-Matched Primary Key

The most common form: the framework looks up the primary key of the target class automatically.

[Table("user_course")]
public partial class UserCourse
{
    [PrimaryKey, ForeignKey(typeof(User))]
    public Guid UserId { get; set; }

    [PrimaryKey, ForeignKey(typeof(Course))]
    public Guid CourseId { get; set; }
}

Generated DDL:

CONSTRAINT "fk_user_course_users"   FOREIGN KEY ("user_id")   REFERENCES "users"   ("id"),
CONSTRAINT "fk_user_course_courses" FOREIGN KEY ("course_id") REFERENCES "courses" ("id")

Explicit Target Keys

When the target table does not use a simple single-column PK, or when you need to reference a non-PK unique column, specify TargetKeys:

[ForeignKey(typeof(User), TargetKeys = new[] { nameof(User.Email) })]
public string UserEmail { get; set; }

Referential Actions

Use DbValues.ForeignKey constants for OnDelete and OnUpdate:

Constant	SQL keyword
`DbValues.ForeignKey.Cascade`	`CASCADE`
`DbValues.ForeignKey.SetNull`	`SET NULL`
`DbValues.ForeignKey.SetDefault`	`SET DEFAULT`
`DbValues.ForeignKey.Restrict`	`RESTRICT`
`DbValues.ForeignKey.NoAction`	`NO ACTION`

[ForeignKey(typeof(User),
    OnDelete = DbValues.ForeignKey.Cascade,
    OnUpdate = DbValues.ForeignKey.NoAction,
    Name = "fk_posts_user")]
public Guid AuthorId { get; set; }

Named Constraint

[ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade, Name = "fk_orders_user")]
public Guid UserId { get; set; }

Class-Level Composite Foreign Key

For a composite FK referencing multiple columns, place the attribute on the class:

[Table("user_course_agreement")]
[ForeignKey(typeof(UserCourse),
    Keys = new[] { nameof(UserId), nameof(CourseId) },
    TargetKeys = new[] { nameof(UserCourse.UserId), nameof(UserCourse.CourseId) })]
public partial class UserCourseAgreement
{
    public Guid UserId { get; set; }
    public Guid CourseId { get; set; }
}

Generated DDL fragment:

CONSTRAINT "fk_user_course_agreement_user_course"
    FOREIGN KEY ("user_id", "course_id")
    REFERENCES "user_course" ("user_id", "course_id")

`[Check]` — CHECK Constraints

Raw SQL Expression

[Table("users")]
[Check("LENGTH(email) < 254")]
public partial class User
{
    public string Email { get; set; }

    [Check("LENGTH(bio) <= 500")]
    public string? Bio { get; set; }
}

Generated DDL fragments:

CONSTRAINT "chk_users_1"   CHECK (LENGTH(email) < 254),
CONSTRAINT "chk_users_bio" CHECK (LENGTH(bio) <= 500)

WARNING

PostgreSQL uses LENGTH() — not LEN(). Using LEN() will cause a runtime error when the migration is applied. Always use LENGTH(column_name) in raw CHECK expressions.

Type-Safe Expression via `IDbCheckExpression`

For complex or reusable CHECK logic, implement IDbCheckExpression and reference the type:

using Socigy.OpenSource.DB.Checks;

public class PositivePriceCheck : IDbCheckExpression
{
    public DbCheckExpr Build(string? columnName)
        => new DbCheckExpr("\"price\" > 0");
}

[Table("products")]
public partial class Product
{
    [Check(typeof(PositivePriceCheck))]
    public decimal Price { get; set; }
}

TIP

See the Check DSL article for the full IDbCheckExpression API and how to compose multi-condition checks.

`[StringLength]` — Length Constraints

[StringLength] changes the column type from TEXT to VARCHAR(n) and optionally adds a minimum-length CHECK constraint.

Maximum Length Only

[StringLength(200)]
public string Title { get; set; }

Generated DDL fragment:

"title" VARCHAR(200) NOT NULL

Minimum and Maximum Length

[StringLength(3, 50)]
public string Username { get; set; }

Generated DDL fragment:

"username" VARCHAR(50) NOT NULL,
CONSTRAINT "chk_users_username" CHECK (LENGTH(username) >= 3)

Comparison Constraints

Seven attribute classes emit CHECK constraints for numeric and date comparisons. All accept long, double, or string (for SQL literals such as date strings).

`[Min(value)]` — Greater Than or Equal

[Min(0)]
public int Quantity { get; set; }

SQL: CHECK (quantity >= 0)

`[Max(value)]` — Less Than or Equal

[Max(100)]
public double Percentage { get; set; }

SQL: CHECK (percentage <= 100)

`[Bigger(value)]` — Strictly Greater Than

[Bigger(0)]
public decimal UnitPrice { get; set; }

SQL: CHECK (unit_price > 0)

`[BiggerOrEqual(value)]` — Greater Than or Equal

[BiggerOrEqual(18)]
public int MinimumAge { get; set; }

SQL: CHECK (minimum_age >= 18)

`[Lower(value)]` — Strictly Less Than

[Lower(1000)]
public int MaxConcurrentSessions { get; set; }

SQL: CHECK (max_concurrent_sessions < 1000)

`[LowerOrEqual(value)]` — Less Than or Equal

[LowerOrEqual(5)]
public int StarRating { get; set; }

SQL: CHECK (star_rating <= 5)

`[Equal(value)]` — Equality

[Equal(1)]
public int SchemaVersion { get; set; }

SQL: CHECK (schema_version = 1)

Using String Values for SQL Literals

All comparison attributes accept a string overload that inserts the value verbatim as a SQL literal:

[Min("'2020-01-01'")]
public DateOnly ReleaseDate { get; set; }

SQL: CHECK (release_date >= '2020-01-01')

`[Renamed]` — Column Rename Migration Hint

[Renamed("old_column_name")] on a property tells the migration CLI that the column was previously named old_column_name. The CLI emits ALTER TABLE ... RENAME COLUMN instead of dropping and recreating the column, preserving all existing data.

// The column was previously "user_name"
[Renamed("user_name")]
public string Username { get; set; }

Generated migration:

ALTER TABLE "users" RENAME COLUMN "user_name" TO "username";

WARNING

Remove [Renamed] after the migration is applied and committed. Leaving it in place will cause the CLI to emit the RENAME COLUMN statement again on the next migration run.

Combined Real-World Example

using Socigy.OpenSource.DB.Attributes;

[Table("products")]
[Check("unit_price > cost_price")]
public partial class Product
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ForeignKey(typeof(Category), OnDelete = DbValues.ForeignKey.Restrict)]
    public Guid CategoryId { get; set; }

    [StringLength(6, 50), Unique(Name = "uq_products_sku")]
    public string Sku { get; set; }

    [StringLength(200)]
    public string Name { get; set; }

    [Bigger(0)]
    public decimal CostPrice { get; set; }

    [Bigger(0)]
    public decimal UnitPrice { get; set; }

    [Min(0)]
    [Max(9999)]
    public int StockQuantity { get; set; }

    [Min("'2000-01-01'")]
    public DateOnly AvailableFrom { get; set; }

    public string? Description { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }
}

Generated DDL (abbreviated):

CREATE TABLE "products" (
    "id"             UUID         NOT NULL DEFAULT gen_random_uuid(),
    "category_id"    UUID         NOT NULL,
    "sku"            VARCHAR(50)  NOT NULL,
    "name"           VARCHAR(200) NOT NULL,
    "cost_price"     NUMERIC      NOT NULL,
    "unit_price"     NUMERIC      NOT NULL,
    "stock_quantity" INTEGER      NOT NULL,
    "available_from" DATE         NOT NULL,
    "description"    TEXT         NULL,
    "created_at"     TIMESTAMP    NOT NULL DEFAULT timezone('utc', now()),

    PRIMARY KEY ("id"),

    CONSTRAINT "uq_products_sku"       UNIQUE ("sku"),
    CONSTRAINT "chk_products_1"        CHECK (unit_price > cost_price),
    CONSTRAINT "chk_products_sku"      CHECK (LENGTH(sku) >= 6),
    CONSTRAINT "chk_products_cost"     CHECK (cost_price > 0),
    CONSTRAINT "chk_products_unit"     CHECK (unit_price > 0),
    CONSTRAINT "chk_products_stock_1"  CHECK (stock_quantity >= 0),
    CONSTRAINT "chk_products_stock_2"  CHECK (stock_quantity <= 9999),
    CONSTRAINT "chk_products_avail"    CHECK (available_from >= '2000-01-01'),

    CONSTRAINT "fk_products_categories"
        FOREIGN KEY ("category_id") REFERENCES "categories" ("id") ON DELETE RESTRICT
);

← previousDefault values

next →JSON columns