Primary key error in INFORMATION_SCHEMA views

Hi everyone,

In the SQL standard, the INFORMATION_SCHEMA.TABLE_CONSTRAINTS has a primary
key compound of:
- CONSTRAINT_CATALOG: the database name
- CONSTRAINT_SCHEMA: the constraint's SQL schema
- CONSTRAINT_NAME; the name of the constraint

This part of the standard ISO/IEC 9075-11:2003
Information technology -- Database languages -- SQL -- Part 11: Information
and Definition Schemas (SQL/Schemata) is show bellow:

<http://www.postgresql-archive.org/file/t343155/SQL_ISO_standard_WD_9075-11-Part_11_-_SQL_Schemata.jpg>


In PostgreSQL VERSION() --> 10.3 version 64 bits on Windows (and probably in
oldest versions) this PRIMARY KEY does not exists.
The Following script demonstrate the bug:

CREATE SCHEMA s;
CREATE TABLE s.foo (C INT);
ALTER TABLE s.foo ADD CONSTRAINT k CHECK(C>0);
CREATE TABLE s.foo2 (C INT);
ALTER TABLE s.foo2 ADD CONSTRAINT k CHECK(C>=1);

This last SQL command should fail because the database name, the schema name
and the constraint name is the same for those two constraints, but did'nt !

This is confirm by the query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA =
's';

Such a bug introduce major confusion in scripting or using the metadata.

Thanks for correcting this bug in order to make exploitable the information
views of schemas that are stable in time because going through the systems
views PG _... is subject to caution and has no guarantee of stability in
time!

Thanks




-----
Frédéric BROUARD - Data Architect - Paris / PACA, France - SARL SQL SPOT
Teacher : CNAM PACA, ISEN Toulon, CESI/EXIA Aix en Provence, EPITA Paris
Expert on SQL and Relational DBMS : Microsoft SQL Server / PostGreSQL...
My French site about SQL language and RDBMS http://sqlpro.developpez.com
The books I wrote :  https://www.amazon.fr/Frédéric-Brouard/e/B004MQ3NVQ
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html