Reading your question literally, "if we delete 1 of 2 records pointing to Table2 from Table1 would also delete referenced record from Table2", you just need an ordinary ON DELETE CASCADE, as you've shown: Table2 references Table1, and deleting a record in Table1 causes all related records in Table2 to be deleted, even if those records are being pointed to by other records in Table1.
However, if your goal is instead to delete from Table2 when there are no more associated records in Table1, whereas there are normally two such records, you'll need something more elaborate.
I think better in concrete terms, so based on your pre-edit question, I'll call Table1 Guardians (because "parents" can be confusing for SQL-heads when they're actually the child half of a relationship) and Table2 Students (similarly, avoiding the word "children"). The goal is to ensure that any students that do not have any guardians get deleted.
If polygamy and divorce are unheard on, one option would be to have two NULLable fields in Students, FatherID and MotherID (or Parent1 and Parent2, to allow for step-parents, gay marriage, etc.). Set these to reference Guardians with ON DELETE SET NULL, and add an ON UPDATE trigger to Students to delete a record if both fields become NULL. If Bob's father is deleted but he still has a mother, he's OK, but if his mother is deleted the poor orphan gets wiped from the database. This assumes that a CASCADE UPDATE event triggers triggers, I don't know that for a fact.
A more robust solution would be to create a third table relating Guardians to Students. It would include fields GuardianID, StudentID, EffectiveDate (these three making up a candidate key), StopDate, and maybe fields to indicate the rights of the guardian; perhaps only one has legal custody, and the other should not be allowed to remove the student from school grounds. This would allow for death or divorce: just set the old record's StopDate. Insert a new record if the remaining guardian ever remarries. You could create an ON UPDATE trigger and an ON DELETE trigger on this third table, checking to see if there are now any students which lack active guardians.
However, before putting too much engineering into this, consider: do you really need to cascade? Keep the referential integrity, of course, but just enforce deletion logic in your business logic layer. For that matter, does it even make sense to delete students? If you're asked how many students took a specific class in 2011, but you've deleted some because they've subsequently dis-enrolled, you'll have an undercount.
I was able to make this work with the below setup
Process:
1. Loop through the pg_constraint table and understand the constraints defined on the table that has to delete a record
2. Before deleting a record verify no reference record exists in any of the dependent table
Detailed implementation can be found below.
Let me know your thoughts on this.
#1: DDL
CREATE TABLE core.images
(
id serial NOT NULL,
name character varying(1024) NOT NULL
CONSTRAINT "PK_images" PRIMARY KEY (id)
);
create table core.app_images
(
id serial,
image_id int,
CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id)
REFERENCES core.images (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
);
create table core.app_images1
(
id serial,
image_id int,
CONSTRAINT "FK_foo_image1" FOREIGN KEY (image_id)
REFERENCES core.images (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
#2: Funciton to check ref data integrity
CREATE OR REPLACE FUNCTION core.check_ref_exists (
p_schema TEXT,
p_table TEXT,
p_column TEXT,
p_value TEXT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
r record ;
v_sql TEXT ;
v_return BOOLEAN DEFAULT FALSE ;
BEGIN
FOR r IN (
SELECT
pn.nspname s_name,
r_tab.relname AS t_name,
pa.attname AS c_name
FROM
pg_constraint pc
INNER JOIN pg_class tab ON tab.oid = pc.confrelid
INNER JOIN pg_class r_tab ON r_tab.oid = pc.conrelid
INNER JOIN pg_namespace pn ON pn.oid = r_tab.relnamespace
INNER JOIN pg_namespace pn_drv ON pn_drv.oid = tab.relnamespace
INNER JOIN pg_attribute pa ON (
pa.attrelid = pc.conrelid
AND pa.attnum = ANY (pc.conkey)
)
INNER JOIN pg_attribute pa_d ON (
pa_d.attrelid = pc.confrelid
AND pa_d.attnum = ANY (pc.confkey)
)
WHERE
contype = 'f'
AND tab.relname = p_table
AND pn_drv.nspname = p_schema
AND pa_d.attname = p_column
) loop
v_sql := 'SELECT TRUE FROM ' || p_schema || '.' || p_table || ' s
WHERE s. ID = ' || p_value || '
AND EXISTS (
SELECT 1 FROM ' || r.s_name || '.' || r.t_name || ' d WHERE s.' || p_column || ' = d.' || r.c_name || ')' ; EXECUTE v_sql INTO v_return ;
v_return := COALESCE (v_return, FALSE) ;
IF v_return = TRUE THEN
RETURN v_return ;
END IF ;
END loop ;
RETURN v_return ;
END ; $$
#3: Trigger Function
CREATE OR REPLACE FUNCTION core.images_tr_func()
RETURNS TRIGGER AS
$BODY$
DECLARE
v_ref_exists boolean;
BEGIN
select core.check_ref_exists (
p_schema := 'core',
p_table :='images',
p_column :='id',
p_value := old.id::text
) into v_ref_exists;
if v_ref_exists then
return null;
else
return old;
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;
#4: Trigger
DROP TRIGGER if exists images_tr ON core.images;
CREATE TRIGGER images_tr BEFORE DELETE
ON core.images FOR EACH ROW
EXECUTE PROCEDURE core.images_tr_func();
#5: Sample Data
insert into core.images(name) values('image1'),('image2'),('image3'),('image4');
select * from core.images;
insert into core.app_images(image_id) values
(1),(1),(2)
select * from core.app_images
insert into core.app_images1(image_id) values
(2),(3),(3)
select * from core.app_images1
#6: Testing
delete from core.images where id = 1; --Nothing happened as it is still referred
delete from core.images where id = 4; --Deleted as it is an orphan record
Best Answer
If you have the ability to create a foreign key constraint, you are actually getting permission to check for the existence/non-existence of a certain value in a certain column (or set of columns) of a table. You may not have the privilege to
SELECTthe whole list of values in this column, but you can already know something about them. You could try to insert values on the referencing table. The ones that are allowed, you already know exist in the referenced table.If you should know absolutely nothing about those values, you cannot be granted the privilege to make a
foreign key constrainton the column.Add to that the considerations pointed out by @Erwin, WRT to being able to prevent deletes from the referenced tables.