With PostgreSQL, PK detection fails when PK + FK constraint on same column

[jmichelf]

Hi
For me, the function schema($table,$fields=NULL,$ttl=0) in sql.php wrongly gives 'pkey'=false when using it with PostgreSQL on a table that have both PK and FK constraints on the same column. In same condition with MySQL, it gives correct result.
The reason might be that the query used for 'pgsql' returns a duplicate row for the same field. Here after the illustration on 'pgsql' :

SELECT
    C.COLUMN_NAME AS field,
    C.DATA_TYPE AS type,
    C.COLUMN_DEFAULT AS defval,
    C.IS_NULLABLE AS nullable,
    T.CONSTRAINT_TYPE AS pkey 
FROM INFORMATION_SCHEMA.COLUMNS AS C 
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K 
    ON C.TABLE_NAME=K.TABLE_NAME 
    AND C.COLUMN_NAME=K.COLUMN_NAME 
    AND C.TABLE_SCHEMA=K.TABLE_SCHEMA 
    AND C.TABLE_CATALOG=K.TABLE_CATALOG 
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS T 
    ON K.TABLE_NAME=T.TABLE_NAME 
    AND K.CONSTRAINT_NAME=T.CONSTRAINT_NAME 
    AND K.TABLE_SCHEMA=T.TABLE_SCHEMA 
    AND K.TABLE_CATALOG=T.TABLE_CATALOG WHERE C.TABLE_NAME='my_table' 
    AND C.TABLE_CATALOG='my_db'

    field    |       type        | defval | nullable |    pkey     
-------------+-------------------+--------+----------+-------------
 id          | integer           |        | NO       | PRIMARY KEY
 id          | integer           |        | NO       | FOREIGN KEY   <------- duplicate row
 company_id  | integer           |        | NO       | FOREIGN KEY
 unique_name | character varying |        | NO       | 

The corresponding query on 'mysql':

SHOW columns FROM my_table;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| company_id  | int(11)      | NO   | MUL | NULL    |                |
| unique_name | varchar(128) | NO   | UNI | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Additionally, the 'pgsql' column 'id' has no default value, which is intended in my case, and probably has no impact here.

Thanks for your great work on 'f3'.
Kind regards
Jean-Michel

[xfra35]

Thanks for reporting. This should be fixed in f3-factory/fatfree-core@28e7e75.
I assumed that the PK always appears first in the list.

If that was not the case, we should add an ORDER BY condition in the SQL statement. Something like ORDER BY C.ordinal_position,CASE WHEN T.CONSTRAINT_TYPE='PRIMARY KEY' THEN 1 END. But this would have to be tested against the other engines mssql|sqlsrv|sybase|dblib|pgsql|odbc.

[jmichelf]

Hi. Thanks, this fix solved my problem.
Jean-Michel

[ivanpenchev]

@xfra35 this does not work with MSSQL using the dblib driver. The primary column does not appear first in the list thus the sql.php is unable to determine which is the primary column.