I would like to enforce a rule such that when people are creating table without primary key, it throws an error. Is it possible to be done from within pgdb?
Question posted in PostgreSQL
The official documentation can be found here.
The official documentation can be found here.
2
Answers
EDIT: Someone else has answered regarding how to test the existence of primary keys, which completes Part 2 below. You will have to combine both answers for the full solution.
The logic fits inside several event triggers (also see documentation for the create command).
First point to note is the DDL commands this can apply to, all documented here.
Part 1:
CREATE TABLE AS
&SELECT INTO
If I am not wrong,
CREATE TABLE AS
andSELECT INTO
never add constraints on the created table, they must be blocked with an event trigger that always raises an exception.Note your could define the trigger to be
ON
ddl_command_start`. It makes it a little bit faster but does not go well with the full code I posted at the end.See the next, less straightforward part for the rest of the explanations.
Part 2: Regular
CREATE TABLE
&ALTER TABLE
This case is more complex, as we want to block only some commands but not all.
The function and event trigger below do:
To do it, it uses the
pg_event_trigger_ddl_commands()
(documentation here), which BTW is the reason why this trigger had to be onddl_command_end
.You will note that when adding a primary key, a
CREATE INDEX
is caught too.Here is the code:
Additional notes:
You can prevent these constraints from being enforced on a temporary table by tested the
schema_name
is notpg_temp
.The full code, including this test and with credit to jian for the function he posted:
demo:
Only table
a4
will be created.related post: PL/pgSQL checking if a row exists
https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns