Changeset: ad3d61b9f227 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/ad3d61b9f227
Modified Files:
sql/test/information-schema/Tests/check_constraints.test
sql/test/information-schema/Tests/table_constraints.test
Branch: Aug2024
Log Message:
Extending integrity checks for check_constraints and table_constraints view
data.
diffs (275 lines):
diff --git a/sql/test/information-schema/Tests/check_constraints.test
b/sql/test/information-schema/Tests/check_constraints.test
--- a/sql/test/information-schema/Tests/check_constraints.test
+++ b/sql/test/information-schema/Tests/check_constraints.test
@@ -8,6 +8,31 @@ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINT
WHERE CONSTRAINT_SCHEMA = '' OR CONSTRAINT_NAME = ''
----
+query TTTTIITI rowsort
+SELECT
+ CONSTRAINT_CATALOG,
+ CONSTRAINT_SCHEMA,
+ CONSTRAINT_NAME,
+ CHECK_CLAUSE,
+ schema_id,
+ table_id,
+ table_name,
+ key_id
+FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+WHERE CONSTRAINT_SCHEMA IS NULL
+ OR CONSTRAINT_SCHEMA = ''
+ OR CONSTRAINT_NAME IS NULL
+ OR CONSTRAINT_NAME = ''
+ OR CHECK_CLAUSE IS NULL
+ OR CHECK_CLAUSE = ''
+ OR schema_id IS NULL
+ OR table_id IS NULL
+ OR table_name IS NULL
+ OR table_name = ''
+ OR key_id IS NULL
+----
+
+-- entity integrity checks
query ITTT rowsort
SELECT COUNT(*) AS duplicates, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,
CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
@@ -15,3 +40,72 @@ SELECT COUNT(*) AS duplicates, CONSTRAIN
HAVING COUNT(*) > 1
----
+-- as CONSTRAINT_CATALOG is always NULL the CONSTRAINT_SCHEMA, CONSTRAINT_NAME
combo should be unique also
+query ITT rowsort
+SELECT COUNT(*) AS duplicates, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+ GROUP BY CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ HAVING COUNT(*) > 1
+----
+
+-- it should also be unique when using schema_id instead of CONSTRAINT_SCHEMA
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, schema_id, CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+ GROUP BY schema_id, CONSTRAINT_NAME
+ HAVING COUNT(*) > 1
+----
+
+-- key_id alone should be unique also
+query II rowsort
+SELECT COUNT(*) AS duplicates, key_id
+ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+ GROUP BY key_id
+ HAVING COUNT(*) > 1
+----
+
+-- referential integrity checks
+query TTT rowsort
+SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+ WHERE (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA)
+ NOT IN (SELECT CATALOG_NAME, SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA)
+----
+
+-- as CONSTRAINT_CATALOG is always NULL leave it out of the check
+query TT rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+ WHERE (CONSTRAINT_SCHEMA)
+ NOT IN (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA)
+----
+
+-- it should also be correct when using schema_id instead of CONSTRAINT_SCHEMA
+query ITT rowsort
+SELECT schema_id, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+ WHERE (schema_id)
+ NOT IN (SELECT schema_id FROM INFORMATION_SCHEMA.SCHEMATA)
+----
+
+-- check schema_id reference
+query TTI rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, schema_id
+ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+ WHERE (schema_id) NOT IN (SELECT id FROM sys.schemas)
+----
+
+-- check table_id reference
+query TTI rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, table_id
+ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+ WHERE (table_id) NOT IN (SELECT id FROM sys.tables)
+----
+
+-- check key_id reference
+query TTI rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, key_id
+ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+ WHERE (key_id) NOT IN (SELECT id FROM sys.keys UNION ALL SELECT id FROM
tmp.keys)
+----
+
diff --git a/sql/test/information-schema/Tests/table_constraints.test
b/sql/test/information-schema/Tests/table_constraints.test
--- a/sql/test/information-schema/Tests/table_constraints.test
+++ b/sql/test/information-schema/Tests/table_constraints.test
@@ -32,9 +32,24 @@ SELECT
key_type,
is_system
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
-WHERE CONSTRAINT_SCHEMA = '' OR CONSTRAINT_NAME = ''
+WHERE CONSTRAINT_SCHEMA IS NULL
+ OR CONSTRAINT_SCHEMA = ''
+ OR CONSTRAINT_NAME IS NULL
+ OR CONSTRAINT_NAME = ''
+ OR TABLE_SCHEMA IS NULL
+ OR TABLE_SCHEMA = ''
+ OR TABLE_NAME IS NULL
+ OR TABLE_NAME = ''
+ OR CONSTRAINT_TYPE IS NULL
+ OR CONSTRAINT_TYPE = ''
+ OR schema_id IS NULL
+ OR table_id IS NULL
+ OR key_id IS NULL
+ OR key_type IS NULL
+ OR is_system IS NULL
----
+-- entity integrity checks
query ITTT rowsort
SELECT COUNT(*) AS duplicates, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,
CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
@@ -42,3 +57,137 @@ SELECT COUNT(*) AS duplicates, CONSTRAIN
HAVING COUNT(*) > 1
----
+-- as CONSTRAINT_CATALOG is always NULL the CONSTRAINT_SCHEMA, CONSTRAINT_NAME
combo should be unique also
+query ITT rowsort
+SELECT COUNT(*) AS duplicates, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ GROUP BY CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ HAVING COUNT(*) > 1
+----
+
+-- it should also be unique when using schema_id instead of CONSTRAINT_SCHEMA
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, schema_id, CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ GROUP BY schema_id, CONSTRAINT_NAME
+ HAVING COUNT(*) > 1
+----
+
+-- key_id alone should be unique also
+query II rowsort
+SELECT COUNT(*) AS duplicates, key_id
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ GROUP BY key_id
+ HAVING COUNT(*) > 1
+----
+
+-- referential integrity checks
+query TTT rowsort
+SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA)
+ NOT IN (SELECT CATALOG_NAME, SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA)
+----
+
+-- as CONSTRAINT_CATALOG is always NULL leave it out of the check
+query TT rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (CONSTRAINT_SCHEMA)
+ NOT IN (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA)
+----
+
+-- it should also be correct when using schema_id instead of CONSTRAINT_SCHEMA
+query ITT rowsort
+SELECT schema_id, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (schema_id)
+ NOT IN (SELECT schema_id FROM INFORMATION_SCHEMA.SCHEMATA)
+----
+
+query TTT rowsort
+SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (TABLE_CATALOG, TABLE_SCHEMA)
+ NOT IN (SELECT CATALOG_NAME, SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA)
+----
+
+-- as TABLE_CATALOG is always NULL leave it out of the check
+query TT rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (TABLE_SCHEMA)
+ NOT IN (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA)
+----
+
+-- it should also be correct when using schema_id instead of TABLE_SCHEMA
+query ITT rowsort
+SELECT schema_id, TABLE_SCHEMA, TABLE_NAME
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (schema_id)
+ NOT IN (SELECT schema_id FROM INFORMATION_SCHEMA.SCHEMATA)
+----
+
+-- check schema_id reference
+query TTI rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, schema_id
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (schema_id) NOT IN (SELECT id FROM sys.schemas)
+----
+
+-- check table_id reference
+query TTI rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, table_id
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (table_id) NOT IN (SELECT id FROM sys.tables)
+----
+
+-- check key_id reference
+query TTI rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, key_id
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (key_id) NOT IN (SELECT id FROM sys.keys UNION ALL SELECT id FROM
tmp.keys)
+----
+
+-- check key_type reference
+query TTI rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, key_type
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (key_type) NOT IN (SELECT key_type_id FROM sys.key_types)
+----
+
+-- check CONSTRAINT_TYPE allowed values
+query TTT rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CONSTRAINT_TYPE
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (CONSTRAINT_TYPE) NOT IN ('PRIMARY KEY', 'UNIQUE', 'FOREIGN KEY',
'UNIQUE NULLS NOT DISTINCT', 'CHECK')
+----
+
+-- check IS_DEFERRABLE allowed values
+query TTT rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, IS_DEFERRABLE
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (IS_DEFERRABLE) NOT IN ('NO', 'YES')
+----
+
+-- check INITIALLY_DEFERRED allowed values
+query TTT rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, INITIALLY_DEFERRED
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (INITIALLY_DEFERRED) NOT IN ('NO', 'YES')
+----
+
+-- check ENFORCED allowed values
+query TTT rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, ENFORCED
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (ENFORCED) NOT IN ('NO', 'YES')
+----
+
+-- check is_system allowed boolean values
+query TTI rowsort
+SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, is_system
+ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE (is_system) NOT IN (FALSE, TRUE)
+----
+
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
