mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
35 lines
1.3 KiB
SQL
35 lines
1.3 KiB
SQL
ALTER TABLE public.owners
|
|
ADD COLUMN IF NOT EXISTS owner_type TEXT NOT NULL DEFAULT 'individual',
|
|
ADD COLUMN IF NOT EXISTS business_name TEXT NULL,
|
|
ADD COLUMN IF NOT EXISTS management_company TEXT NULL,
|
|
ADD COLUMN IF NOT EXISTS management_contact_name TEXT NULL,
|
|
ADD COLUMN IF NOT EXISTS management_contact_title TEXT NULL,
|
|
ADD COLUMN IF NOT EXISTS management_contact_email TEXT NULL,
|
|
ADD COLUMN IF NOT EXISTS management_contact_phone TEXT NULL;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'owners_owner_type_check'
|
|
) THEN
|
|
ALTER TABLE public.owners
|
|
ADD CONSTRAINT owners_owner_type_check
|
|
CHECK (owner_type IN ('individual', 'business'));
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'owners_business_name_required_check'
|
|
) THEN
|
|
ALTER TABLE public.owners
|
|
ADD CONSTRAINT owners_business_name_required_check
|
|
CHECK (owner_type <> 'business' OR NULLIF(TRIM(business_name), '') IS NOT NULL);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'owners_management_contact_email_format_check'
|
|
) THEN
|
|
ALTER TABLE public.owners
|
|
ADD CONSTRAINT owners_management_contact_email_format_check
|
|
CHECK (management_contact_email IS NULL OR management_contact_email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$');
|
|
END IF;
|
|
END $$; |