# Constraints

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

## 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:

```csharp
[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:

```sql
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:

```csharp
[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:

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

Generated DDL fragment (with Name specified):

```sql
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.

```csharp
[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:

```sql
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`:

```csharp
[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` |

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

### Named Constraint

```csharp
[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:

```csharp
[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:

```sql
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

```csharp
[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:

```sql
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:

```csharp
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

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

Generated DDL fragment:

```sql
"title" VARCHAR(200) NOT NULL
```

### Minimum and Maximum Length

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

Generated DDL fragment:

```sql
"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

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

SQL: `CHECK (quantity >= 0)`

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

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

SQL: `CHECK (percentage <= 100)`

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

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

SQL: `CHECK (unit_price > 0)`

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

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

SQL: `CHECK (minimum_age >= 18)`

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

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

SQL: `CHECK (max_concurrent_sessions < 1000)`

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

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

SQL: `CHECK (star_rating <= 5)`

### `[Equal(value)]` — Equality

```csharp
[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:

```csharp
[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.

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

Generated migration:

```sql
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

```csharp
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):

```sql
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
);
```