How can I get a listing of foreign key constraints in Babelfish?

Question

In SQL Server, you can run

```select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY'```

However, in Babelfish, the constraint name listed in the output is actually the underlying PostgreSQL constraint name (rather than the name that the constraint was created with), which Babelfish changes. Therefore, any DDL generated based on the `table_constraints` view will fail.

Answer

Please let me inform you that Foreign key constraints that reference the database name aren't supported in Babelfish[+]

[+] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish-compatibility.tsql.limitations-unsupported.html#babelfish-compatibility.tsql.limitations-unsupported-table

PostgreSQL has a maximum identifier length of 63 characters, while SQL Server supports up to 128. In addition to this, PostgreSQL has stricter uniqueness requirements for index names. Babelfish handles these restrictions by internally appending or replacing part of such identifiers with a 32-character string representing a hash of the identifier. While this is transparent from T-SQL, the identifier-with-hash is the object name when seen from PostgreSQL.

This is a expected behavior however, please let us know the SQL statements, error messages via support ticket so that we will investigate the issue further.

How can I get a listing of foreign key constraints in Babelfish?

Relevant content