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: