Is there a way to check if all foreign key columns data_type are the same as the column they point to?
This code is valid and works until a user have an ID bigger than what int4 can handle.
CREATE SCHEMA test;
CREATE TABLE test.users (
id bigserial NOT NULL,
name varchar NULL,
CONSTRAINT user_pk PRIMARY KEY (id)
);
CREATE TABLE test.othertable (
blabla varchar NULL,
userid int4 NULL
);
ALTER TABLE test.othertable ADD CONSTRAINT newtable_fk FOREIGN KEY (userid) REFERENCES test.users(id);
CodePudding user response:
An (incomplete) version, using the bare pg_catalogs instead of the information_schema wrapper:
SELECT version();
DROP SCHEMA test CASCADE;
CREATE SCHEMA test;
SET search_path = test;
CREATE TABLE users (
id bigserial NOT NULL CONSTRAINT user_pk PRIMARY KEY
, name varchar NULL
);
CREATE TABLE othertable (
blabla varchar NULL
, userid int4 NULL CONSTRAINT bad_fk REFERENCES users(id)
, goodid bigint NULL CONSTRAINT good_fk REFERENCES users(id)
);
PREPARE insert_two(bigint, text, text) AS
WITH one AS (
INSERT INTO users (id, name)
VALUES ( $1, $2)
RETURNING id
)
INSERT INTO othertable (userid, goodid, blabla)
SELECT id, id, $3
FROM one
;
EXECUTE insert_two(1, 'one', 'bla1' );
EXECUTE insert_two(2, 'two', 'bla2' );
EXECUTE insert_two(10000000000::bigint, 'toobig', 'bigbla' );
SELECT * FROM users;
SELECT * FROM othertable;
SET search_path = pg_catalog;
-- EXPLAIN ANALYZE
WITH cat AS ( -- Class Attribute Type
SELECT cl.oid AS coid, cl.relname
, at.attnum AS cnum, at.attname
, ty.oid AS toid, ty.typname
FROM pg_class cl
JOIN pg_attribute at ON at.attrelid = cl.oid AND at.attnum > 0 -- suppres system columns
JOIN pg_type ty ON ty.oid = at.atttypid
)
SELECT ns.nspname
, co.*
, source.relname AS source_table, source.attname AS source_column, source.typname AS source_type
, target.relname AS target_table, target.attname AS target_column, target.typname AS target_type
FROM pg_constraint co
JOIN pg_namespace ns ON co.connamespace = ns.oid
-- NOTE: this only covers single-column FKs
JOIN cat source ON source.coid = co.conrelid AND co.conkey[1] = source.cnum
JOIN cat target ON target.coid = co.confrelid AND co.confkey[1] = target.cnum
WHERE 1=1
AND co.contype = 'f'
AND ns.nspname = 'test'
-- commented out the line below, to show the differences between "good" and "bad" FK constraints.
-- AND source.toid <> target.toid
;
Rsults (look at the operators, it is a feature, not a bug!)
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 11.6 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Raspbian 8.3.0-6 rpi1) 8.3.0, 32-bit
(1 row)
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table test.users
drop cascades to table test.othertable
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
CREATE TABLE
PREPARE
INSERT 0 1
INSERT 0 1
ERROR: integer out of range
id | name
---- ------
1 | one
2 | two
(2 rows)
blabla | userid | goodid
-------- -------- --------
bla1 | 1 | 1
bla2 | 2 | 2
(2 rows)
SET
nspname | conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | conparentid | confrelid | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | conkey | confkey | conpfeqop | conppeqop | conffeqop | conexclop | conbin | consrc | source_table | source_column | source_type | target_table | target_column | target_type
--------- --------- -------------- --------- --------------- ------------- -------------- ---------- ---------- ---------- ------------- ----------- ------------- ------------- --------------- ------------ ------------- -------------- -------- --------- ----------- ----------- ----------- ----------- -------- -------- -------------- --------------- ------------- -------------- --------------- -------------
test | good_fk | 211305 | f | f | f | t | 211317 | 0 | 211315 | 0 | 211308 | a | a | s | t | 0 | t | {3} | {1} | {410} | {410} | {410} | | | | othertable | goodid | int8 | users | id | int8
test | bad_fk | 211305 | f | f | f | t | 211317 | 0 | 211315 | 0 | 211308 | a | a | s | t | 0 | t | {2} | {1} | {416} | {410} | {96} | | | | othertable | userid | int4 | users | id | int8
(2 rows)
CodePudding user response:
I made this query that check this :
select
tc.table_schema,
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_schema AS foreign_table_schema,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name,
sc.data_type AS data_type,
dc.data_type AS foreign_data_type
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.columns sc ON sc.table_schema = kcu.table_schema and sc.table_name = kcu.table_name and sc.column_name = kcu.column_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
JOIN information_schema.columns dc ON dc.table_schema = ccu.table_schema and dc.table_name = ccu.table_name and dc.column_name = ccu.column_name
WHERE tc.constraint_type = 'FOREIGN KEY'
and sc.data_type <> dc.data_type;
It is quite slow, any tips for optimisation is welcome.