On 3/11/25 13:24, Adrian Klaver wrote:
On 3/11/25 12:55, mark bradley wrote:
It happened again. Now there are no sequences (although there once was).
Read my previous post and provide the information requested.
Mark sent me the below, which answers some of the questions, namely
there is inheritance going on:
Universal Metadata Schema=# \d node
Table "public.node"
Column | Type | Collation | Nullable | Default
-----------+-----------+-----------+----------+---------
node_id | integer | | not null |
node_type | node_type | | not null |
Indexes:
"node_pkey" PRIMARY KEY, btree (node_id)
"node_id" UNIQUE CONSTRAINT, btree (node_id) INCLUDE (node_id)
Referenced by:
TABLE "user_role" CONSTRAINT "a" FOREIGN KEY (node_id) REFERENCES
node(node_i
d) NOT VALID
TABLE "dataset" CONSTRAINT "node_id" FOREIGN KEY (node_id) REFERENCES
node(no
de_id) NOT VALID
Number of child tables: 2 (Use \d+ to list them.)
Universal Metadata Schema=# \d dataset
Table "public.dataset"
Column | Type | Collation | Nullable | Default
---------------------------+---------------------------+-----------+----------+--
-------
node_id | integer | | not null |
dataset_name | character varying(25) | | not null |
notes | text | | |
dataset_type | database_type | | |
dataset_maturity | database_maturity_type | | |
disposition | disposition_type | | |
start_date | date | | |
end_date | date | | |
most_recent_update | date | | |
update_periodicity | interval | | |
system_of_record | text | | |
point_of_contact | integer | | |
dataset_url | text | | |
classification_level | classification_level_type | | |
physical_location | text | | |
quality_control | yes_no_type | | |
dataset_documentation_url | text | | |
description | text | | |
node_type | node_type | | |
Indexes:
"dataset_pkey" PRIMARY KEY, btree (node_id)
Foreign-key constraints:
"node_id" FOREIGN KEY (node_id) REFERENCES node(node_id) NOT VALID
"poc" FOREIGN KEY (point_of_contact) REFERENCES poc(poc_id) NOT VALID
Referenced by:
TABLE "dataset_table" CONSTRAINT "dataset" FOREIGN KEY (node_id)
REFERENCES d
ataset(node_id) NOT VALID
TABLE "system_dataset" CONSTRAINT "system_dataset_node_id_fkey" FOREIGN
KEY (
node_id) REFERENCES dataset(node_id) NOT VALID
Inherits: node
Universal Metadata Schema=# \d processing_node
Table "public.processing_node"
Column | Type | Collation | Nullable | Default
-----------------------+-----------------------+-----------+----------+---------
node_id | integer | | not null |
processing_node_name | character varying(25) | | |
description | text | | |
notes | text | | |
point_of_contact | integer | | not null |
is_a_user_application | yes_no_type | | not null |
node_type | node_type | | |
Indexes:
"processing_node_pkey" PRIMARY KEY, btree (node_id)
Foreign-key constraints:
"processing_node_point_of_contact_fkey" FOREIGN KEY (point_of_contact)
REFERE
NCES poc(poc_id)
Referenced by:
TABLE "system_processing_node" CONSTRAINT
"system_processing_node_processing_
node_id_fkey" FOREIGN KEY (processing_node_id) REFERENCES
processing_node(node_id
) NOT VALID
Inherits: node
Universal Metadata Schema=# ALTER TABLE node VALID
ATE CONSTRAINT node_id;
ERROR: constraint "node_id" of relation "node" is
not a foreign key or check constraint
Universal Metadata Schema=# ALTER TABLE dataset VA
LIDATE CONSTRAINTnode_id;
ERROR: syntax error at or near "CONSTRAINTnode_id
"
LINE 1: ALTER TABLE dataset VALIDATE CONSTRAINTnod
e_id;
^
> Did you ever run VALIDATE CONSTRAINT against them?
Here is the run
Universal Metadata Schema=# ALTER TABLE node VALID
ATE CONSTRAINT node_id;
ERROR: constraint "node_id" of relation "node" is
not a foreign key or check constraint
Universal Metadata Schema=#
Universal Metadata Schema=# ALTER TABLE dataset VA
LIDATE CONSTRAINT node_id;
ALTER TABLE
Universal Metadata Schema=#
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
