Hi Xano Community,
I'm working on designing my database schema and want to ensure robust data integrity using specific uniqueness constraints commonly found in relational databases. I'd appreciate clarification on whether Xano natively supports the following:
Context:
I have the following tables (simplified):
users: Stores user information.
users_emails: Stores user email addresses (id, user_id, email, is_primary (boolean)).
workspaces: Stores workspace information.
workspaces_users: A join table connecting users and workspaces (id, users_id, workspaces_id, role).
My Requirements & Questions:
Composite Unique Keys (Unique constraint on multiple columns):
Need: In the workspaces_users table, I need to ensure that a specific user (users_id) can only be associated with a specific workspace (workspaces_id) once. The combination of users_id and workspaces_id must be unique across the table to prevent duplicate membership entries.
Question: Does Xano provide a built-in way to define a unique constraint on a pair (or combination) of columns, specifically (users_id, workspaces_id) in my workspaces_users table?
Partial / Filtered Unique Indexes:
Need: In the users_emails table, a user can have multiple email addresses, but only one can be designated as their primary email for login (is_primary = true). I need to enforce that for any given user_id, there can be at most one record where is_primary is true. Standard unique constraints on user_id alone or on (user_id, is_primary) don't solve this correctly. In SQL databases, this is often handled with a partial/filtered unique index (CREATE UNIQUE INDEX ... WHERE is_primary = true).
Question: Does Xano support creating a unique index or constraint that applies only to a subset of rows based on a specific condition (like is_primary = true)? For example, ensuring user_id is unique only among rows where is_primary is true.
Supplement / Why this is important:
These constraints are crucial for maintaining logical data consistency directly at the database level:
The composite key prevents invalid duplicate relationships between users and workspaces.
The partial unique constraint ensures that the business rule of "only one primary email per user" is strictly enforced, preventing data conflicts or ambiguity, especially critical if this email is used for login or primary communication.
If these specific constraint types aren't directly supported, what are the recommended best practices or idiomatic ways within Xano (e.g., using API logic, Pre-/Post-operation hooks, Lambdas) to reliably enforce these kinds of uniqueness rules before data is committed?
Thanks in advance for any insights or guidance!