Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL standard in SQL Server and postrgesql

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';
like image 363
user1759320 Avatar asked Aug 07 '26 10:08

user1759320


1 Answers

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.
...


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!