SafeDispatch/UpdateHelper/CreateMissingForeignKeys.sql

696 lines
28 KiB
MySQL
Raw Permalink Normal View History

CREATE OR REPLACE FUNCTION public.CreateMissingForeignKeys()
RETURNS void AS
$BODY$
BEGIN
--===================
--VoiceRecordings
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'VoiceRecordings' and constraint_type = 'FOREIGN KEY' and constraint_name = 'voice_recordings_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'VoiceRecordings' and column_name = 'subscriber_id') then
ALTER TABLE public."VoiceRecordings"
ADD CONSTRAINT voice_recordings_subscriber_fk FOREIGN KEY (subscriber_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : voice_recordings_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key voice_recordings_subscriber_fk already exists between tables (VoiceRecordings, subscriber) !';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'VoiceRecordings' and constraint_type = 'FOREIGN KEY' and constraint_name = 'voice_recordings_gateway_fk')
and exists (select * from information_schema.columns where table_name = 'VoiceRecordings' and column_name = 'gw_id') then
ALTER TABLE public."VoiceRecordings"
ADD CONSTRAINT voice_recordings_gateway_fk FOREIGN KEY (gw_id)
REFERENCES public.gateways ("ID") MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : voice_recordings_gateway_fk !';
else
RAISE NOTICE '%', 'Foreign key voice_recordings_gateway_fk already exists between tables (VoiceRecordings, gateways) !';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'VoiceRecordings' and constraint_type = 'FOREIGN KEY' and constraint_name = 'voice_recordings_groups_fk')
and exists (select * from information_schema.columns where table_name = 'VoiceRecordings' and column_name = 'group_id') then
ALTER TABLE public."VoiceRecordings"
ADD CONSTRAINT voice_recordings_groups_fk FOREIGN KEY (group_id)
REFERENCES public.groups (id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : voice_recordings_groups_fk !';
else
RAISE NOTICE '%', 'Foreign key voice_recordings_groups_fk already exists between tables (VoiceRecordings, groups) !';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'VoiceRecordings' and constraint_type = 'FOREIGN KEY' and constraint_name = 'voice_recordings_dispatchers_fk')
and exists (select * from information_schema.columns where table_name = 'VoiceRecordings' and column_name = 'dispatcher_id') then
ALTER TABLE public."VoiceRecordings"
ADD CONSTRAINT voice_recordings_dispatchers_fk FOREIGN KEY (dispatcher_id)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : voice_recordings_dispatchers_fk !';
else
RAISE NOTICE '%', 'Foreign key voice_recordings_dispatchers_fk already exists between tables (VoiceRecordings, users) !';
end if;
--===================
--alarm
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'alarm' and constraint_type = 'FOREIGN KEY' and constraint_name = 'alarm_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'alarm' and column_name = 'sc_id') then
ALTER TABLE public.alarm
ADD CONSTRAINT alarm_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : alarm_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key alarm_subscriber_fk exists between tables (alarm, subscriber) !';
end if;
--===================
--call_patch
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'call_patch' and constraint_type = 'FOREIGN KEY' and constraint_name = 'call_patch_user_fk')
and exists (select * from information_schema.columns where table_name = 'call_patch' and column_name = 'userid') then
ALTER TABLE public.call_patch
ADD CONSTRAINT call_patch_user_fk FOREIGN KEY (userid)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : call_patch_user_fk !';
else
RAISE NOTICE '%', 'Foreign key call_patch_user_fk exists between tables (call_patch, user) !';
end if;
--===================
--definesms
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'definesms' and constraint_type = 'FOREIGN KEY' and constraint_name = 'definesms_user_fk')
and exists (select * from information_schema.columns where table_name = 'definesms' and column_name = 'user_id') then
if exists (select * from public.definesms where user_id = 0) then
UPDATE public.definesms SET user_id = 1
WHERE user_id = 0;
end if;
ALTER TABLE public.definesms
ADD CONSTRAINT definesms_user_fk FOREIGN KEY (user_id)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : definesms_user_fk !';
else
RAISE NOTICE '%', 'Foreign key definesms_user_fk exists between tables (definesms, user) !';
end if;
--===================
--emergalarm
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'emergalarm' and constraint_type = 'FOREIGN KEY' and constraint_name = 'emergalarm_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'emergalarm' and column_name = 'sc_id') then
ALTER TABLE public.emergalarm
ADD CONSTRAINT emergalarm_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : emergalarm_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key emergalarm_subscriber_fk exists between tables (emergalarm, subscriber)!';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'emergalarm' and constraint_type = 'FOREIGN KEY' and constraint_name = 'emergalarm_user_fk')
and exists (select * from information_schema.columns where table_name = 'emergalarm' and column_name = 'user_id') then
ALTER TABLE public.emergalarm
ADD CONSTRAINT emergalarm_user_fk FOREIGN KEY (user_id)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : emergalarm_user_fk !';
else
RAISE NOTICE '%', 'Foreign key emergalarm_user_fk exists between tables (emergalarm, user) !';
end if;
--===================
--emergdi
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'emergdi' and constraint_type = 'FOREIGN KEY' and constraint_name = 'emergdi_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'emergdi' and column_name = 'sc_id') then
ALTER TABLE public.emergdi
ADD CONSTRAINT emergdi_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : emergdi_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key emergdi_subscriber_fk exists between tables (emergdi, subscriber) !';
end if;
--===================
--geozoneinout
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'geozoneinout' and constraint_type = 'FOREIGN KEY' and constraint_name = 'geozoneinout_user_fk')
and exists (select * from information_schema.columns where table_name = 'geozoneinout' and column_name = 'user_id') then
ALTER TABLE public.geozoneinout
ADD CONSTRAINT geozoneinout_user_fk FOREIGN KEY (user_id)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : geozoneinout_user_fk !';
else
RAISE NOTICE '%', 'Foreign key geozoneinout_user_fk exists between tables (geozoneinout, user) !';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'geozoneinout' and constraint_type = 'FOREIGN KEY' and constraint_name = 'geozoneinout_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'geozoneinout' and column_name = 'sc_id') then
ALTER TABLE public.geozoneinout
ADD CONSTRAINT geozoneinout_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : geozoneinout_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key geozoneinout_subscriber_fk exists between tables (geozoneinout, subscriber) !';
end if;
--===================
--groups
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'groups' and constraint_type = 'FOREIGN KEY' and constraint_name = 'groups_radio_gw_fk')
and exists (select * from information_schema.columns where table_name = 'groups' and column_name = 'r_gw_id') then
ALTER TABLE public.groups
ADD CONSTRAINT groups_radio_gw_fk FOREIGN KEY (r_gw_id)
REFERENCES public.radio_gw ("ID") MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : groups_radio_gw_fk !';
else
RAISE NOTICE '%', 'Foreign key groups_radio_gw_fk exists between tables (groups, radio_gw)!';
end if;
--===================
--jobtickets
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'jobtickets' and constraint_type = 'FOREIGN KEY' and constraint_name = 'jobtickets_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'jobtickets' and column_name = 'sc_id') then
ALTER TABLE public.jobtickets
ADD CONSTRAINT jobtickets_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : jobtickets_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key jobtickets_subscriber_fk exists between tables (jobtickets, subscriber)!';
end if;
--===================
--jobtickets_log
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'jobtickets_log' and constraint_type = 'FOREIGN KEY' and constraint_name = 'jobtickets_log_jobtickets_fk')
and exists (select * from information_schema.columns where table_name = 'jobtickets_log' and column_name = 'ticket_id') then
ALTER TABLE public.jobtickets_log
ADD CONSTRAINT jobtickets_log_jobtickets_fk FOREIGN KEY (ticket_id)
REFERENCES public.jobtickets (ticket_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : jobtickets_log_jobtickets_fk !';
else
RAISE NOTICE '%', 'Foreign key jobtickets_log_jobtickets_fk exists between tables (jobtickets_log, jobtickets)!';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'jobtickets_log' and constraint_type = 'FOREIGN KEY' and constraint_name = 'jobtickets_log_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'jobtickets_log' and column_name = 'sc_id') then
ALTER TABLE public.jobtickets_log
ADD CONSTRAINT jobtickets_log_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : jobtickets_log_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key jobtickets_log_subscriber_fk exists between tables (jobtickets_log, subscriber)!';
end if;
--===================
--messages
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'messages' and constraint_type = 'FOREIGN KEY' and constraint_name = 'messages_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'messages' and column_name = 'sc_id') then
ALTER TABLE public.messages
ADD CONSTRAINT messages_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : messages_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key messages_subscriber_fk exists between tables (messages, subscriber)!';
end if;
--===================
--mobile_contacts
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'mobile_contacts' and constraint_type = 'FOREIGN KEY' and constraint_name = 'mobile_contacts_subscriber_dest_fk')
and exists (select * from information_schema.columns where table_name = 'mobile_contacts' and column_name = 'sc_id') then
ALTER TABLE public.mobile_contacts
ADD CONSTRAINT mobile_contacts_subscriber_dest_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : mobile_contacts_subscriber_dest_fk !';
else
RAISE NOTICE '%', 'Foreign key mobile_contacts_subscriber_dest_fk exists between tables (mobile_contacts, subscriber)!';
end if;
--===================
--passenger
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'passenger' and constraint_type = 'FOREIGN KEY' and constraint_name = 'passenger_subscriber_dest_fk')
and exists (select * from information_schema.columns where table_name = 'passenger' and column_name = 'sc_id') then
ALTER TABLE public.passenger
ADD CONSTRAINT passenger_subscriber_dest_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : passenger_subscriber_dest_fk !';
else
RAISE NOTICE '%', 'Foreign key passenger_subscriber_dest_fk exists between tables (passenger, subscriber)!';
end if;
--===================
--sms
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'sms' and constraint_type = 'FOREIGN KEY' and constraint_name = 'sms_user_fk')
and exists (select * from information_schema.columns where table_name = 'sms' and column_name = 'user_id') then
ALTER TABLE public.sms
ADD CONSTRAINT sms_user_fk FOREIGN KEY (user_id)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : sms_user_fk !';
else
RAISE NOTICE '%', 'Foreign key sms_user_fk exists between tables (sms, user)!';
end if;
--if NOT exists (select * from information_schema.table_constraints where table_name = 'sms' and constraint_type = 'FOREIGN KEY' and constraint_name = 'sms_subscriber_source_fk')
-- and exists (select * from information_schema.columns where table_name = 'sms' and column_name = 'sc_id_sour') then
--
--ALTER TABLE public.sms
-- ADD CONSTRAINT sms_subscriber_source_fk FOREIGN KEY (sc_id_sour)
-- REFERENCES public.subscriber (sc_id) MATCH SIMPLE
-- ON DELETE CASCADE;
--
-- RAISE NOTICE '%', 'Created foreign key : sms_subscriber_source_fk !';
--else
-- RAISE NOTICE '%', 'Foreign key sms_subscriber_source_fk exists between tables (sms, subscriber)!';
--
--end if;
--
--if NOT exists (select * from information_schema.table_constraints where table_name = 'sms' and constraint_type = 'FOREIGN KEY' and constraint_name = 'sms_subscriber_dest_fk')
-- and exists (select * from information_schema.columns where table_name = 'sms' and column_name = 'sc_id_dest') then
--ALTER TABLE public.sms
-- ADD CONSTRAINT sms_subscriber_dest_fk FOREIGN KEY (sc_id_dest)
-- REFERENCES public.subscriber (sc_id) MATCH SIMPLE
-- ON DELETE CASCADE;
--
-- RAISE NOTICE '%', 'Created foreign key : sms_subscriber_dest_fk !';
--else
-- RAISE NOTICE '%', 'Foreign key sms_subscriber_dest_fk exists between tables (sms, subscriber)!';
--
--end if;
--===================
--soundcards
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'soundcards' and constraint_type = 'FOREIGN KEY' and constraint_name = 'soundcards_user_fk')
and exists (select * from information_schema.columns where table_name = 'soundcards' and column_name = 'user_id') then
ALTER TABLE public.soundcards
ADD CONSTRAINT soundcards_user_fk FOREIGN KEY (user_id)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : soundcards_user_fk !';
else
RAISE NOTICE '%', 'Foreign key soundcards_user_fk exists between tables (soundcards, user)!';
end if;
--===================
--speedalarm
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'speedalarm' and constraint_type = 'FOREIGN KEY' and constraint_name = 'speedalarm_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'speedalarm' and column_name = 'sc_id') then
ALTER TABLE public.speedalarm
ADD CONSTRAINT speedalarm_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : speedalarm_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key speedalarm_subscriber_fk exists between tables (speedalarm, subscriber)!';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'speedalarm' and constraint_type = 'FOREIGN KEY' and constraint_name = 'speedalarm_user_fk')
and exists (select * from information_schema.columns where table_name = 'speedalarm' and column_name = 'user_id') then
ALTER TABLE public.speedalarm
ADD CONSTRAINT speedalarm_user_fk FOREIGN KEY (user_id)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : speedalarm_user_fk !';
else
RAISE NOTICE '%', 'Foreign key speedalarm_user_fk exists between tables (speedalarm, user)!';
end if;
--===================
--subs_gateway
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'subs_gateway' and constraint_type = 'FOREIGN KEY' and constraint_name = 'subs_gateway_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'subs_gateway' and column_name = 'sc_id') then
ALTER TABLE public.subs_gateway
ADD CONSTRAINT subs_gateway_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : subs_gateway_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key subs_gateway_subscriber_fk exists between tables (subs_gateway, subscriber)!';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'subs_gateway' and constraint_type = 'FOREIGN KEY' and constraint_name = 'subs_gateway_radio_gw_fk')
and exists (select * from information_schema.columns where table_name = 'subs_gateway' and column_name = 'radio_gw_id') then
ALTER TABLE public.subs_gateway
ADD CONSTRAINT subs_gateway_radio_gw_fk FOREIGN KEY (radio_gw_id)
REFERENCES public.radio_gw ("ID") MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : subs_gateway_radio_gw_fk !';
else
RAISE NOTICE '%', 'Foreign key subs_gateway_radio_gw_fk exists between tables (subs_gateway, radio_gw)!';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'subs_gateway' and constraint_type = 'FOREIGN KEY' and constraint_name = 'subs_gateway_gateways_fk')
and exists (select * from information_schema.columns where table_name = 'subs_gateway' and column_name = 'gateway_id') then
ALTER TABLE public.subs_gateway
ADD CONSTRAINT subs_gateway_gateways_fk FOREIGN KEY (gateway_id)
REFERENCES public.gateways ("ID") MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : subs_gateway_gateways_fk !';
else
RAISE NOTICE '%', 'Foreign key subs_gateway_gateways_fk exists between tables (subs_gateway, gateways)!';
end if;
--===================
--subscriber_history
--===================
if NOT exists (select * from information_schema.table_constraints where table_name = 'subscriber_history' and constraint_type = 'FOREIGN KEY' and constraint_name = 'subscriber_history_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'subscriber_history' and column_name = 'sc_id') then
ALTER TABLE public.subscriber_history
ADD CONSTRAINT subscriber_history_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : subscriber_history_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key subscriber_history_subscriber_fk exists between tables (subscriber_history, subscriber)!';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'subscriber_history' and constraint_type = 'FOREIGN KEY' and constraint_name = 'subscriber_history_vehicle_fk')
and exists (select * from information_schema.columns where table_name = 'subscriber_history' and column_name = 'veh_id') then
ALTER TABLE public.subscriber_history
ADD CONSTRAINT subscriber_history_vehicle_fk FOREIGN KEY (veh_id)
REFERENCES public.vehicle (id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : subscriber_history_vehicle_fk !';
else
RAISE NOTICE '%', 'Foreign key subscriber_history_vehicle_fk exists between tables (subscriber_history, vehicle)!';
end if;
--================
--telemetry_pos
--================
if NOT exists (select * from information_schema.table_constraints where table_name = 'telemetry_pos' and constraint_type = 'FOREIGN KEY' and constraint_name = 'telemetry_pos_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'telemetry_pos' and column_name = 'sc_id') then
ALTER TABLE public.telemetry_pos
ADD CONSTRAINT telemetry_pos_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : telemetry_pos_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key telemetry_pos_subscriber_fk exists between tables (telemetry_pos, subscriber)!';
end if;
--================
-- users_radio
--================
if NOT exists (select * from information_schema.table_constraints where table_name = 'user_radio' and constraint_type = 'FOREIGN KEY' and constraint_name = 'user_radio_users_fk')
and exists (select * from information_schema.columns where table_name = 'user_radio' and column_name = 'user_id') then
ALTER TABLE public.user_radio
ADD CONSTRAINT user_radio_users_fk FOREIGN KEY (user_id)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : users_radio_user_fk !';
else
RAISE NOTICE '%', 'Foreign key users_radio_user_fk exists between tables (user_radio, users)!';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'user_radio' and constraint_type = 'FOREIGN KEY' and constraint_name = 'user_radio_radio_gw_fk')
and exists (select * from information_schema.columns where table_name = 'user_radio' and column_name = 'radio_id') then
ALTER TABLE public.user_radio
ADD CONSTRAINT user_radio_radio_gw_fk FOREIGN KEY (radio_id)
REFERENCES public.radio_gw ("ID") MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : user_radio_radio_gw_fk !';
else
RAISE NOTICE '%', 'Foreign key user_radio_radio_gw_fk exists between tables (user_radio, radio_gw)!';
end if;
--================
-- users_group
--================
if NOT exists (select * from information_schema.table_constraints where table_name = 'users_group' and constraint_type = 'FOREIGN KEY' and constraint_name = 'users_group_user_fk')
and exists (select * from information_schema.columns where table_name = 'users_group' and column_name = 'userid') then
ALTER TABLE public.users_group
ADD CONSTRAINT users_group_user_fk FOREIGN KEY (userid)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : users_group_user_fk !';
else
RAISE NOTICE '%', 'Foreign key users_group_user_fk exists between tables (users_group, users)!';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'users_group' and constraint_type = 'FOREIGN KEY' and constraint_name = 'users_group_groups_fk')
and exists (select * from information_schema.columns where table_name = 'users_group' and column_name = 'grp_id') then
ALTER TABLE public.users_group
ADD CONSTRAINT users_group_groups_fk FOREIGN KEY (grp_id)
REFERENCES public.groups (id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : users_group_groups_fk !';
else
RAISE NOTICE '%', 'Foreign key users_group_groups_fk exists between tables (users_group, groups)!';
end if;
--================
-- vehicle_group
--================
if NOT exists (select * from information_schema.table_constraints where table_name = 'vehicle_group' and constraint_type = 'FOREIGN KEY' and constraint_name = 'vehicle_group_subscriber_fk')
and exists (select * from information_schema.columns where table_name = 'vehicle_group' and column_name = 'sc_id') then
ALTER TABLE public.vehicle_group
ADD CONSTRAINT vehicle_group_subscriber_fk FOREIGN KEY (sc_id)
REFERENCES public.subscriber (sc_id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : vehicle_group_subscriber_fk !';
else
RAISE NOTICE '%', 'Foreign key vehicle_group_subscriber_fk exists between tables (vehicle_group, subscriber)!';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'vehicle_group' and constraint_type = 'FOREIGN KEY' and constraint_name = 'vehicle_group_groups_fk')
and exists (select * from information_schema.columns where table_name = 'vehicle_group' and column_name = 'grp_ip') then
ALTER TABLE public.vehicle_group
ADD CONSTRAINT vehicle_group_groups_fk FOREIGN KEY (grp_ip)
REFERENCES public.groups (id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : vehicle_group_groups_fk !';
else
RAISE NOTICE '%', 'Foreign key vehicle_group_groups_fk exists between tables (vehicle_group, groups)!';
end if;
--================
-- vehicle_user
--================
if NOT exists (select * from information_schema.table_constraints where table_name = 'vehicle_user' and constraint_type = 'FOREIGN KEY' and constraint_name = 'vehicle_user_users_fk')
and exists (select * from information_schema.columns where table_name = 'vehicle_user' and column_name = 'user_id') then
ALTER TABLE public.vehicle_user
ADD CONSTRAINT vehicle_user_users_fk FOREIGN KEY (user_id)
REFERENCES public.users (userid) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : vehicle_user_users_fk !';
else
RAISE NOTICE '%', 'Foreign key vehicle_user_users_fk exists between tables (vehicle_group, users)!';
end if;
if NOT exists (select * from information_schema.table_constraints where table_name = 'vehicle_user' and constraint_type = 'FOREIGN KEY' and constraint_name = 'vehicle_user_vehicule_fk')
and exists (select * from information_schema.columns where table_name = 'vehicle_user' and column_name = 'veh_id') then
ALTER TABLE public.vehicle_user
ADD CONSTRAINT vehicle_user_vehicule_fk FOREIGN KEY (veh_id)
REFERENCES public.vehicle (id) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : vehicle_user_vehicule_fk !';
else
RAISE NOTICE '%', 'Foreign key vehicle_user_vehicule_fk exists between tables (vehicle_group, vehicle)!';
end if;
--================
-- zonepoints
--================
if NOT exists (select * from information_schema.table_constraints where table_name = 'zonepoints' and constraint_type = 'FOREIGN KEY' and constraint_name = 'zonepoints_zone_fk')
and exists (select * from information_schema.columns where table_name = 'zonepoints' and column_name = 'zone_id') then
ALTER TABLE public.zonepoints
ADD CONSTRAINT zonepoints_zone_fk FOREIGN KEY (zone_id)
REFERENCES public.zonename (idx) MATCH SIMPLE
ON DELETE CASCADE;
RAISE NOTICE '%', 'Created foreign key : zonepoints_zone_fk !';
else
RAISE NOTICE '%', 'Foreign key zonepoints_zone_fk exists between tables (zonepoints, zonename)!';
end if;
END;
$BODY$
LANGUAGE plpgsql VOLATILE