Graphql-engine: support composite primary, and foreign keys and unique constraints through the console

[wawhal]

This issue comes from @lukebyrne and was originally opened here.

As of now:

1. You can set composite primary keys from the console while creating the table
2. You cannot set composite primary keys from the console once the table is already created (can be done via SQL)
3. You cannot set composite foreign keys from the console (can be done via SQL)
4. Relationships are not suggested based on composite primary keys (can be done by adding manual relationships) (Related issue)
5. Adding a manual relationship does not support multiple columns.

@praveenweb please point out if I have missed anything.

[dsandip]

Edit: Added modified wireframes in a later comment

Mockups for composite primary keys (during table edit/mod) and foreign keys (table creation and mod)

1) Changing the table creation process to add foreign keys without having to save the table definition first, using a separate section for foreign keys:

2) You start off by choosing the reference table and then selecting the columns from the current and reference tables:

3) As you select columns for a simple/composite Fkey, you can see the FKey definition being updated ( (a,b) -> user (id, name)):

4) Obviously, you can create multiple Fkeys:

5) We'll reuse the above pattern in the "Modify table" page (/data/schema/<schema-name>/tables/<table-name>/modify) by pulling out the primary/foreign key info into their own sections:

6) If you edit the PKey, you will not see an option to remove a pkey, but you can modify it, including converting a simple PKey into a composite one:

7) Fkey edits work pretty much the same as FKey creation, with an additional option to remove the constraint (this will be subject to existing validation for dependent relationships). Any changes to the definition will enable the Done button:

[EDIT] via @0x777
8) The UI also needs to be implemented for adding manual relationships:

[dsandip]

@rikinsk: Changes from our chat have been updated
@karthikvt26 @arvi3411301

[maxpain]

Any updates?

[dsandip]

@praveenweb Once composite FKeys are available, are console changes needed for relationship recommendations over these constraints?

[dsandip]

@Maxpain177 There should be an update on this next week.

[dsandip]

Edit: Added modified wireframes in a later comment

@arvi3411301
We can also address #285 here itself (the console part). Console suggestions for relationships based on Fkey should also pick up and show the Fkey name in the suggestions like this:

@rikinsk FYI.

[dsandip]

Edit: Added modified wireframes in a later comment

@arvi3411301 Also adding wireframes for composite unique constraints:

Create a composite unique constraint when creating a table:
1) Option to create

2) creating the constraint

Options for composite unique constraint when editing a table:

1) List comp-unique constraints with an option to create one:

@arvi3411301 I forgot to add the option to add a composite FKey in the previous wireframes. Please take note of that option.

2) Editing a comp-unique-constraint:

[dsandip]

From @rikinsk

Show a column's config (all fields in the edit section) without having to click on the `edit` button in the modify tab. E.g. `unique`, `nullable`, `default value`, etc.

[dsandip]

@samuela @mnlbox FYI

[dsandip]

@wawhal There are tiny modifications required in this, let's talk before you pick this up. cc: @rikinsk

[dsandip]

@rikinsk @wawhal

Latest wireframes

Create table options

Primary key (single-column/composite)

This is also the current behaviour, so no changes needed here

Unique constraint - single column

For the most common use-case i.e. a unique constraint over a single column, you can define the constraint at the time of creating the column itself, and it will also show up in the section for unique constraints (which will show both single-column and composite constraints):

Foreign key definition

with the option to define violation behaviour for update and delete:

Show Fkeys creating during the table creation process:

Unique constraints

Unique constraints' summary to be shown similar to Fkeys above while table is being created

Modify table

Modify Pkey:

Modify Fkey:
Existing issues with a dependent relationship will not be fixed here

Modify unique constraint:

Manual Relationship

[wawhal]

Work is being done on console to support:

• PKs
  
  
  
  • You can drop and modify PK constraints
  
  
  • In case of modify, existing constraint is dropped and a new constraint is created
  
  
• Composite unique constraints
  
  
  
  • You can create, drop and modify composite unique constraints
  
  
  • In case of modify, existing constraint is dropped and a new constraint is created
  
  
• Composing FK
  
  
  
  • You can create and drop composite FKs
  
  
  • In case of modify, existing constraint is dropped and a new constraint is created
  
  

We will need the corresponding server changes so that the above features work as expected. AFAIK, dropping the PK constraint could break some metadata. @0x777 @rakeshkky @ecthiender can you share some insights?

cc: @dsandip @rikinsk

[mnlbox]

@dsandip your wire-frame is awesome. :heart:
I have one another suggestion. Maybe it's better to show dropdown as input type for Insert Row tab. For example in below image maybe it's better to show a dropdown that show title but pass UUID id for parent_subject_id field:

Maybe we can select which column is show as display column in foreign key creation page right to the to column here:

[mnlbox]

@dsandip Also I think some ordering for column also can useful and have a good UX to able to re order database table column. something like this:

[rakeshkky]

@wawhal Currently this is server behaviour

• Foreign keys

  • Dependencies (relationships) are being calculated on dropped constraints. An exception will be raised in case of any dependencies found for dropped constraints.
  • If cascade is true then those dependencies are dropped.

• Primary keys

  • There are no dependencies.
  • Primary key constraint names are updated in cache to generate table_constraint enum type in on_conflict clause in insert mutations.
  • A patch is needed to server to update primary columns in cache to generate input arguments for <table-name>_by_pk fields**.

• Unique constraints

  • There are no dependencies.
  • Similar to primary keys, these constraint names are being updated in cache.

** I'll push server patch to your branch. Update me with PR number.

[wawhal]

@rakeshkky the PR is not ready yet. Didn't open it to avoid unnecessary builds. You can push to issue-506 in my fork

[mnlbox]

Maybe related for table creation page: https://github.com/hasura/graphql-engine/issues/1462

[rikinsk-zz]

Reopening to track unique constraints