Created
February 21, 2023 10:58
-
-
Save idavidmcdonald/e5c9b1401090787962e5ebc5d18b344e to your computer and use it in GitHub Desktop.
The constraints to be added to our RDS database which has had its tables created by DMS
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment