API for working with foreign keys

[kgodey]

Problem

Users may want to add a column that references a column from another table (i.e. a foreign key). To support this functionality, we need to enable working with foreign keys in our API.

Proposed solution

We need to make the following changes to our API:

• High priority for front end:

  • API support for searching from the record selector #945
  • API support for "Link Table" dialog #972

• Medium priority for front end:

  • Modify records API to return preview data for foreign key columns #946
  • API support for storing "record preview" and "record selector" settings #944

• Low priority for front end:

  • Modify constraints API to support creating foreign key constraints #942
  • Modify constraints API to list foreign key constraints #943

• Not needed for front end

  • Ensure integrity of columns with foreign key constraints applied #973
  • API support for deleting FK constraints -- (this already works)

Additional context

• front-end meta ticket: Frontend implementation of foreign key constraints #263
• CRUD API for table constraints #326
• Design for usage of Foreign Key constraints #243
• Please ensure that creation parameters do not clash with Extend column API to support duplicating columns #452
• Mathesar's API Standards