The constraints to be added to our RDS database which has had its tables created by DMS

addconstraints.sql

ALTER TABLE public.annual_billing ADD CONSTRAINT "annual_billing_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.annual_billing ADD CONSTRAINT "uix_service_id_financial_year_start" UNIQUE (service_id, financial_year_start);
ALTER TABLE public.api_keys ADD CONSTRAINT "api_keys_key_type_fkey" FOREIGN KEY (key_type) REFERENCES key_types(name);
ALTER TABLE public.api_keys ADD CONSTRAINT "api_keys_secret_key" UNIQUE (secret);
ALTER TABLE public.api_keys ADD CONSTRAINT "api_keys_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.api_keys ADD CONSTRAINT "fk_api_keys_created_by_id" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.broadcast_event ADD CONSTRAINT "broadcast_event_broadcast_message_id_fkey" FOREIGN KEY (broadcast_message_id) REFERENCES broadcast_message(id);
ALTER TABLE public.broadcast_event ADD CONSTRAINT "broadcast_event_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.broadcast_message ADD CONSTRAINT "broadcast_message_approved_by_id_fkey" FOREIGN KEY (approved_by_id) REFERENCES users(id);
ALTER TABLE public.broadcast_message ADD CONSTRAINT "broadcast_message_cancelled_by_api_key_id_fkey" FOREIGN KEY (cancelled_by_api_key_id) REFERENCES api_keys(id);
ALTER TABLE public.broadcast_message ADD CONSTRAINT "broadcast_message_cancelled_by_id_fkey" FOREIGN KEY (cancelled_by_id) REFERENCES users(id);
ALTER TABLE public.broadcast_message ADD CONSTRAINT "broadcast_message_created_by_api_key_id_fkey" FOREIGN KEY (created_by_api_key_id) REFERENCES api_keys(id);
ALTER TABLE public.broadcast_message ADD CONSTRAINT "broadcast_message_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.broadcast_message ADD CONSTRAINT "broadcast_message_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.broadcast_message ADD CONSTRAINT "broadcast_message_status_fkey" FOREIGN KEY (status) REFERENCES broadcast_status_type(name);
ALTER TABLE public.broadcast_message ADD CONSTRAINT "broadcast_message_template_id_fkey" FOREIGN KEY (template_id, template_version) REFERENCES templates_history(id, version);
ALTER TABLE public.broadcast_message ADD CONSTRAINT "ck_broadcast_message_created_by_not_null" CHECK (((created_by_id IS NOT NULL) OR (created_by_api_key_id IS NOT NULL)));
ALTER TABLE public.broadcast_provider_message ADD CONSTRAINT "broadcast_provider_message_broadcast_event_id_fkey" FOREIGN KEY (broadcast_event_id) REFERENCES broadcast_event(id);
ALTER TABLE public.broadcast_provider_message ADD CONSTRAINT "broadcast_provider_message_broadcast_event_id_provider_key" UNIQUE (broadcast_event_id, provider);
ALTER TABLE public.complaints ADD CONSTRAINT "complaints_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.daily_sorted_letter ADD CONSTRAINT "uix_file_name_billing_day" UNIQUE (file_name, billing_day);
ALTER TABLE public.domain ADD CONSTRAINT "domain_organisation_id_fkey" FOREIGN KEY (organisation_id) REFERENCES organisation(id);
ALTER TABLE public.email_branding ADD CONSTRAINT "ck_email_branding_one_of_alt_text_or_text_is_null" CHECK ((((text IS NOT NULL) AND (alt_text IS NULL)) OR ((text IS NULL) AND (alt_text IS NOT NULL))));
ALTER TABLE public.email_branding ADD CONSTRAINT "email_branding_brand_type_fkey" FOREIGN KEY (brand_type) REFERENCES branding_type(name);
ALTER TABLE public.email_branding ADD CONSTRAINT "email_branding_created_by_id_fkey" FOREIGN KEY (created_by) REFERENCES users(id);
ALTER TABLE public.email_branding ADD CONSTRAINT "email_branding_updated_by_id_fkey" FOREIGN KEY (updated_by) REFERENCES users(id);
ALTER TABLE public.email_branding ADD CONSTRAINT "uq_email_branding_name" UNIQUE (name);
ALTER TABLE public.email_branding_to_organisation ADD CONSTRAINT "email_branding_to_organisation_email_branding_id_fkey" FOREIGN KEY (email_branding_id) REFERENCES email_branding(id);
ALTER TABLE public.email_branding_to_organisation ADD CONSTRAINT "email_branding_to_organisation_organisation_id_fkey" FOREIGN KEY (organisation_id) REFERENCES organisation(id);
ALTER TABLE public.inbound_numbers ADD CONSTRAINT "inbound_numbers_number_key" UNIQUE (number);
ALTER TABLE public.inbound_numbers ADD CONSTRAINT "inbound_numbers_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.inbound_sms ADD CONSTRAINT "inbound_sms_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.inbound_sms_history ADD CONSTRAINT "inbound_sms_history_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.invited_organisation_users ADD CONSTRAINT "invited_organisation_users_invited_by_id_fkey" FOREIGN KEY (invited_by_id) REFERENCES users(id);
ALTER TABLE public.invited_organisation_users ADD CONSTRAINT "invited_organisation_users_organisation_id_fkey" FOREIGN KEY (organisation_id) REFERENCES organisation(id);
ALTER TABLE public.invited_organisation_users ADD CONSTRAINT "invited_organisation_users_status_fkey" FOREIGN KEY (status) REFERENCES invite_status_type(name);
ALTER TABLE public.invited_users ADD CONSTRAINT "invited_users_auth_type_fkey" FOREIGN KEY (auth_type) REFERENCES auth_type(name);
ALTER TABLE public.invited_users ADD CONSTRAINT "invited_users_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.invited_users ADD CONSTRAINT "invited_users_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE public.jobs ADD CONSTRAINT "jobs_contact_list_id_fkey" FOREIGN KEY (contact_list_id) REFERENCES service_contact_list(id);
ALTER TABLE public.jobs ADD CONSTRAINT "jobs_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.jobs ADD CONSTRAINT "jobs_job_status_fkey" FOREIGN KEY (job_status) REFERENCES job_status(name);
ALTER TABLE public.jobs ADD CONSTRAINT "jobs_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.jobs ADD CONSTRAINT "jobs_template_id_fkey" FOREIGN KEY (template_id) REFERENCES templates(id);
ALTER TABLE public.letter_branding ADD CONSTRAINT "letter_branding_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.letter_branding ADD CONSTRAINT "letter_branding_filename_key" UNIQUE (filename);
ALTER TABLE public.letter_branding ADD CONSTRAINT "letter_branding_name_key" UNIQUE (name);
ALTER TABLE public.letter_branding ADD CONSTRAINT "letter_branding_updated_by_id_fkey" FOREIGN KEY (updated_by_id) REFERENCES users(id);
ALTER TABLE public.letter_branding_to_organisation ADD CONSTRAINT "letter_branding_to_organisation_letter_branding_id_fkey" FOREIGN KEY (letter_branding_id) REFERENCES letter_branding(id);
ALTER TABLE public.letter_branding_to_organisation ADD CONSTRAINT "letter_branding_to_organisation_organisation_id_fkey" FOREIGN KEY (organisation_id) REFERENCES organisation(id);
ALTER TABLE public.notification_history ADD CONSTRAINT "fk_notification_history_notification_status" FOREIGN KEY (notification_status) REFERENCES notification_status_types(name);
ALTER TABLE public.notification_history ADD CONSTRAINT "notification_history_api_key_id_fkey" FOREIGN KEY (api_key_id) REFERENCES api_keys(id);
ALTER TABLE public.notification_history ADD CONSTRAINT "notification_history_job_id_fkey" FOREIGN KEY (job_id) REFERENCES jobs(id);
ALTER TABLE public.notification_history ADD CONSTRAINT "notification_history_key_type_fkey" FOREIGN KEY (key_type) REFERENCES key_types(name);
ALTER TABLE public.notification_history ADD CONSTRAINT "notification_history_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.notification_history ADD CONSTRAINT "notification_history_templates_history_fkey" FOREIGN KEY (template_id, template_version) REFERENCES templates_history(id, version);
ALTER TABLE public.notifications ADD CONSTRAINT "fk_notifications_notification_status" FOREIGN KEY (notification_status) REFERENCES notification_status_types(name);
ALTER TABLE public.notifications ADD CONSTRAINT "notifications_api_key_id_fkey" FOREIGN KEY (api_key_id) REFERENCES api_keys(id);
ALTER TABLE public.notifications ADD CONSTRAINT "notifications_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.notifications ADD CONSTRAINT "notifications_job_id_fkey" FOREIGN KEY (job_id) REFERENCES jobs(id);
ALTER TABLE public.notifications ADD CONSTRAINT "notifications_key_type_fkey" FOREIGN KEY (key_type) REFERENCES key_types(name);
ALTER TABLE public.notifications ADD CONSTRAINT "notifications_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.notifications ADD CONSTRAINT "notifications_templates_history_fkey" FOREIGN KEY (template_id, template_version) REFERENCES templates_history(id, version);
ALTER TABLE public.notifications ADD CONSTRAINT "uq_notifications_job_row_number" UNIQUE (job_id, job_row_number);
ALTER TABLE public.organisation ADD CONSTRAINT "fk_organisation_agreement_user_id" FOREIGN KEY (agreement_signed_by_id) REFERENCES users(id);
ALTER TABLE public.organisation ADD CONSTRAINT "fk_organisation_email_branding_id" FOREIGN KEY (email_branding_id) REFERENCES email_branding(id);
ALTER TABLE public.organisation ADD CONSTRAINT "fk_organisation_letter_branding_id" FOREIGN KEY (letter_branding_id) REFERENCES letter_branding(id);
ALTER TABLE public.organisation ADD CONSTRAINT "organisation_organisation_type_fkey" FOREIGN KEY (organisation_type) REFERENCES organisation_types(name);
ALTER TABLE public.permissions ADD CONSTRAINT "permissions_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.permissions ADD CONSTRAINT "permissions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE public.permissions ADD CONSTRAINT "uix_service_user_permission" UNIQUE (service_id, user_id, permission);
ALTER TABLE public.provider_details ADD CONSTRAINT "provider_details_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.provider_details_history ADD CONSTRAINT "provider_details_history_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.returned_letters ADD CONSTRAINT "returned_letters_notification_id_key" UNIQUE (notification_id);
ALTER TABLE public.returned_letters ADD CONSTRAINT "returned_letters_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_broadcast_provider_restriction ADD CONSTRAINT "service_broadcast_provider_restriction_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_broadcast_settings ADD CONSTRAINT "service_broadcast_settings_channel_fkey" FOREIGN KEY (channel) REFERENCES broadcast_channel_types(name);
ALTER TABLE public.service_broadcast_settings ADD CONSTRAINT "service_broadcast_settings_provider_fkey" FOREIGN KEY (provider) REFERENCES broadcast_provider_types(name);
ALTER TABLE public.service_broadcast_settings ADD CONSTRAINT "service_broadcast_settings_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_callback_api ADD CONSTRAINT "service_callback_api_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_callback_api ADD CONSTRAINT "service_callback_api_type_fk" FOREIGN KEY (callback_type) REFERENCES service_callback_type(name);
ALTER TABLE public.service_callback_api ADD CONSTRAINT "service_callback_api_updated_by_id_fkey" FOREIGN KEY (updated_by_id) REFERENCES users(id);
ALTER TABLE public.service_callback_api ADD CONSTRAINT "uix_service_callback_type" UNIQUE (service_id, callback_type);
ALTER TABLE public.service_contact_list ADD CONSTRAINT "service_contact_list_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.service_contact_list ADD CONSTRAINT "service_contact_list_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_data_retention ADD CONSTRAINT "service_data_retention_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_data_retention ADD CONSTRAINT "uix_service_data_retention" UNIQUE (service_id, notification_type);
ALTER TABLE public.service_email_branding ADD CONSTRAINT "service_email_branding_email_branding_id_fkey" FOREIGN KEY (email_branding_id) REFERENCES email_branding(id);
ALTER TABLE public.service_email_branding ADD CONSTRAINT "service_email_branding_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_email_reply_to ADD CONSTRAINT "service_email_reply_to_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_inbound_api ADD CONSTRAINT "service_inbound_api_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_inbound_api ADD CONSTRAINT "service_inbound_api_updated_by_id_fkey" FOREIGN KEY (updated_by_id) REFERENCES users(id);
ALTER TABLE public.service_letter_branding ADD CONSTRAINT "service_letter_branding_letter_branding_id_fkey" FOREIGN KEY (letter_branding_id) REFERENCES letter_branding(id);
ALTER TABLE public.service_letter_branding ADD CONSTRAINT "service_letter_branding_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_letter_contacts ADD CONSTRAINT "service_letter_contacts_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_permissions ADD CONSTRAINT "service_permissions_permission_fkey" FOREIGN KEY (permission) REFERENCES service_permission_types(name);
ALTER TABLE public.service_permissions ADD CONSTRAINT "service_permissions_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_sms_senders ADD CONSTRAINT "service_sms_senders_inbound_number_id_fkey" FOREIGN KEY (inbound_number_id) REFERENCES inbound_numbers(id);
ALTER TABLE public.service_sms_senders ADD CONSTRAINT "service_sms_senders_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.service_whitelist ADD CONSTRAINT "service_whitelist_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.services ADD CONSTRAINT "fk_service_organisation" FOREIGN KEY (organisation_id) REFERENCES organisation(id);
ALTER TABLE public.services ADD CONSTRAINT "fk_services_created_by_id" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.services ADD CONSTRAINT "fk_services_go_live_user" FOREIGN KEY (go_live_user_id) REFERENCES users(id);
ALTER TABLE public.services ADD CONSTRAINT "services_email_from_key" UNIQUE (email_from);
ALTER TABLE public.services ADD CONSTRAINT "services_name_key" UNIQUE (name);
ALTER TABLE public.services ADD CONSTRAINT "services_organisation_type_fkey" FOREIGN KEY (organisation_type) REFERENCES organisation_types(name);
ALTER TABLE public.template_folder ADD CONSTRAINT "ix_id_service_id" UNIQUE (id, service_id);
ALTER TABLE public.template_folder ADD CONSTRAINT "template_folder_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES template_folder(id);
ALTER TABLE public.template_folder ADD CONSTRAINT "template_folder_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.template_folder_map ADD CONSTRAINT "template_folder_map_template_folder_id_fkey" FOREIGN KEY (template_folder_id) REFERENCES template_folder(id);
ALTER TABLE public.template_folder_map ADD CONSTRAINT "template_folder_map_template_id_fkey" FOREIGN KEY (template_id) REFERENCES templates(id);
ALTER TABLE public.template_redacted ADD CONSTRAINT "template_redacted_template_id_fkey" FOREIGN KEY (template_id) REFERENCES templates(id);
ALTER TABLE public.template_redacted ADD CONSTRAINT "template_redacted_updated_by_id_fkey" FOREIGN KEY (updated_by_id) REFERENCES users(id);
ALTER TABLE public.templates ADD CONSTRAINT "fk_templates_created_by_id" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.templates ADD CONSTRAINT "templates_process_type_fkey" FOREIGN KEY (process_type) REFERENCES template_process_type(name);
ALTER TABLE public.templates ADD CONSTRAINT "templates_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.templates ADD CONSTRAINT "templates_service_letter_contact_id_fkey" FOREIGN KEY (service_letter_contact_id) REFERENCES service_letter_contacts(id);
ALTER TABLE public.templates_history ADD CONSTRAINT "templates_history_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES users(id);
ALTER TABLE public.templates_history ADD CONSTRAINT "templates_history_process_type_fkey" FOREIGN KEY (process_type) REFERENCES template_process_type(name);
ALTER TABLE public.templates_history ADD CONSTRAINT "templates_history_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.templates_history ADD CONSTRAINT "templates_history_service_letter_contact_id_fkey" FOREIGN KEY (service_letter_contact_id) REFERENCES service_letter_contacts(id);
ALTER TABLE public.user_folder_permissions ADD CONSTRAINT "user_folder_permissions_template_folder_id_fkey1" FOREIGN KEY (template_folder_id) REFERENCES template_folder(id);
ALTER TABLE public.user_folder_permissions ADD CONSTRAINT "user_folder_permissions_template_folder_id_fkey" FOREIGN KEY (template_folder_id, service_id) REFERENCES template_folder(id, service_id);
ALTER TABLE public.user_folder_permissions ADD CONSTRAINT "user_folder_permissions_user_id_fkey" FOREIGN KEY (user_id, service_id) REFERENCES user_to_service(user_id, service_id);
ALTER TABLE public.user_to_organisation ADD CONSTRAINT "uix_user_to_organisation" UNIQUE (user_id, organisation_id);
ALTER TABLE public.user_to_organisation ADD CONSTRAINT "user_to_organisation_organisation_id_fkey" FOREIGN KEY (organisation_id) REFERENCES organisation(id);
ALTER TABLE public.user_to_organisation ADD CONSTRAINT "user_to_organisation_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE public.user_to_service ADD CONSTRAINT "uix_user_to_service" UNIQUE (user_id, service_id);
ALTER TABLE public.user_to_service ADD CONSTRAINT "user_to_service_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id);
ALTER TABLE public.user_to_service ADD CONSTRAINT "user_to_service_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE public.users ADD CONSTRAINT "ck_user_has_mobile_or_other_auth" CHECK ((((auth_type)::text = ANY ((ARRAY['email_auth'::character varying, 'webauthn_auth'::character varying])::text[])) OR (mobile_number IS NOT NULL)));
ALTER TABLE public.users ADD CONSTRAINT "users_auth_type_fkey" FOREIGN KEY (auth_type) REFERENCES auth_type(name);
ALTER TABLE public.verify_codes ADD CONSTRAINT "verify_codes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE public.webauthn_credential ADD CONSTRAINT "webauthn_credential_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id);