How to implement a 'check' constraint
I'd like to implement a 'check' constraint that implements these psql table constraints below. What is the best way to add this constraint in native SQL alongside my drizzle table definitions if it is not yet supported as a native Drizzle piece of functionality?
-- User Table
CREATE TABLE "User" (
UserID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
UserName VARCHAR(255) NOT NULL,
Email VARCHAR(255) NOT NULL
-- Other user attributes
);
-- Organization Table
CREATE TABLE Organization (
OrgID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
OrgName VARCHAR(255) NOT NULL
-- Other organization attributes
);
-- Application Table
CREATE TABLE Application (
AppID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
OwnerType VARCHAR(20) CHECK (OwnerType IN ('User', 'Organization')) NOT NULL,
OwnerID UUID NOT NULL,
Visibility VARCHAR(20) CHECK (Visibility IN ('Individual', 'Team', 'Organization')) NOT NULL,
CONSTRAINT AtLeastOneOwner CHECK (OwnerType = 'User' AND OwnerID IN (SELECT UserID FROM "User") OR OwnerType = 'Organization' AND OwnerID IN (SELECT OrgID FROM Organization))
-- Other application attributes
);
-- OwnerReferences Table
CREATE TABLE OwnerReferences (
ID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
Type VARCHAR(20) CHECK (Type IN ('User', 'Organization')) NOT NULL
);
-- User Table
CREATE TABLE "User" (
UserID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
UserName VARCHAR(255) NOT NULL,
Email VARCHAR(255) NOT NULL
-- Other user attributes
);
-- Organization Table
CREATE TABLE Organization (
OrgID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
OrgName VARCHAR(255) NOT NULL
-- Other organization attributes
);
-- Application Table
CREATE TABLE Application (
AppID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
OwnerType VARCHAR(20) CHECK (OwnerType IN ('User', 'Organization')) NOT NULL,
OwnerID UUID NOT NULL,
Visibility VARCHAR(20) CHECK (Visibility IN ('Individual', 'Team', 'Organization')) NOT NULL,
CONSTRAINT AtLeastOneOwner CHECK (OwnerType = 'User' AND OwnerID IN (SELECT UserID FROM "User") OR OwnerType = 'Organization' AND OwnerID IN (SELECT OrgID FROM Organization))
-- Other application attributes
);
-- OwnerReferences Table
CREATE TABLE OwnerReferences (
ID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
Type VARCHAR(20) CHECK (Type IN ('User', 'Organization')) NOT NULL
);
