does anybody can explain to me why this statement returns columns which are FK in SQL Server but in postgresql returns nothing?
Table is identical on both RDBMS and do have FKs
select *
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
where ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
and ccu.TABLE_NAME = 'table_name'
and tc.CONSTRAINT_TYPE = 'FOREIGN KEY';
It seems that SQL Server stores "outgoing" foreign keys whereas PostgreSQL stores "incoming" foreign keys.
SQL Server example: http://sqlfiddle.com/#!6/57493/3
PostgreSQL example: http://sqlfiddle.com/#!12/57493/5
Unfortunately the ANSI standard doesn't say which one is correct. It merely says:
TABLE_CONSTRAINTS base table
The values of CONSTRAINT_TYPE have the following meanings:
FOREIGN KEY: The constraint being described is a foreign key constraint.
...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With