Re: ID column naming convention - Mailing list pgsql-general
From | Jim Nasby |
---|---|
Subject | Re: ID column naming convention |
Date | |
Msg-id | 5629204E.6050304@BlueTreble.com Whole thread Raw |
In response to | Re: ID column naming convention (Karsten Hilbert <Karsten.Hilbert@gmx.net>) |
List | pgsql-general |
On 10/22/15 11:37 AM, Karsten Hilbert wrote: > On Mon, Oct 19, 2015 at 04:25:39AM -0500, Jim Nasby wrote: > >> BTW, if there's any interest, I have code that sets up a non-inheritance >> table specifically for doing foreign keys to the inheritance parent. It >> doesn't support updates right now, but should be fully safe from a FK >> standpoint. > > Can you tell me more ? I create a '_fk' table that looks like this: > CREATE TABLE _lead.lead_fk( > lead_id int NOT NULL PRIMARY KEY > , organic_lead_id int CONSTRAINT organic_lead_id_sanity CHECK( organic_lead_id IS NULLOR organic_lead_id = lead_id ) > , some_lead_provider_lead_id int CONSTRAINT some_lead_provider_lead_id_sanity CHECK( some_lead_provider_lead_idIS NULL OR some_lead_provider_lead_id = lead_id ) > -- TODO , CHECK( only one blah_lead_id field is NOT NULL using count_nulls() from PGXN ) > ); Then each table that inherits from lead.lead (as well as lead.lead itself) has: > , FOREIGN KEY (lead_id) REFERENCES _lead.lead_fk DEFERRABLE INITIALLY DEFERRED That FK is in place to ensure that when a lead record is inserted, a corresponding row is inserted into _lead.lead_fk as well. That insert is handled by this trigger function: CREATE OR REPLACE FUNCTION _lead.tg_lead_fk( ) RETURNS trigger LANGUAGE plpgsql -- !!!!!!!!! SECURITY DEFINER SET search_path = pg_catalog -- !!!!!!!!! AS $body$ BEGIN EXECUTE format( $$INSERT INTO _lead.lead_fk( lead_id, %I ) VALUES( $1, $1 )$$ , TG_TABLE_NAME || '_id' ) USING NEW.lead_id ; RETURN NEW; END $body$; Finally, a table that needs to have a FK to a lead has > , lead_id int NOT NULL REFERENCES _lead.lead_fk I also have the following in a pgTap test function to verify that the FK exists on all children of the lead.lead table. FOR r IN SELECT * FROM cat_tools.pg_class_v WHERE reloid = 'lead.lead'::regclass UNION ALL SELECT c.* FROM pg_inherits i JOIN cat_tools.pg_class_v c ON reloid = inhrelid WHERE inhparent = 'lead.lead'::regclass LOOP RETURN NEXT col_is_pk( r.relschema , r.relname , array[ 'lead_id' ] , 'lead_id is PK' ); RETURN NEXT fk_ok( r.relschema , r.relname , 'lead_id' , '_lead' , 'lead_fk' , 'lead_id' ); END LOOP; At some point I'll turn this into metacode so that setting all of this up is just a function call. I just haven't gotten to it yet. (Though, if someone wanted to pay me to do that... ;P ) -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
pgsql-general by date: