Postgresql – Advantages to column constraints over table constraints

constraintdatabase-internalsddlforeign keypostgresql

Is there any advantages whatsoever, other than style to writing this,

CREATE TABLE foo (
  a int PRIMARY KEY,
  b int
);

And then,

CREATE TABLE bar (
  a int REFERENCES foo,
  c int
);

Over,

BEGIN;
  CREATE TABLE bar (
    a int,
    c int
  );
  ALTER TABLE bar
    ADD FOREIGN KEY (a)
    REFERENCES foo;
COMMIT;

I'm trying to build a DDL generator, so I'm wondering if pays to keep constraints on the column (where I was generating them before), or to move them all outside to the table? I know these results produce the same table, I'm just wondering if there is any advantage under the hood — less wal? etc?

Best Answer

There's no difference both DDLs result in the same database structure.

The second form (or the following form) is required when the FOREIGN KEY relationship uses a compound key.

CREATE TABLE bar (
    a int,
    c int,
    FOREIGN KEY (a,c) REFERENCES foo(a,c)
);

Related Solutions

Sql-server – With MS SQL Server are the generated constraint names predictable

No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:

CREATE TABLE dbo.foo(bar INT PRIMARY KEY);

CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));

(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6 and FK__blat__bar__1B1EE1BE.)

You would say:

CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));

CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar) 
  REFERENCES dbo.foo(bar));

Postgresql – Do not reject entire statement when deleting rows in batch

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

Related Solutions

Sql-server – With MS SQL Server are the generated constraint names predictable

Postgresql – Do not reject entire statement when deleting rows in batch

Related Question