Add composite PRIMARY KEY support

[Bardin08]

Description

Implement composite PRIMARY KEY support at the parser's side and the schema builder

PRIMARY KEY can be found in a couple of positions.

-- Valid Composite PRIMARY KEY Definitions

-- 1: Definition as a PRIMARY KEY attribute
CREATE TABLE users
(
    id         INT AUTO_INCREMENT,
    name       VARCHAR(122) NOT NULL UNIQUE,
    profile_id INT,
    PRIMARY KEY (id, name)
);

-- 2: Definition as a CONSTRAINT attribute
CREATE TABLE users
(
    id         INT AUTO_INCREMENT,
    name       VARCHAR(122) NOT NULL UNIQUE,
    profile_id INT,
    CONSTRAINT PR_User PRIMARY KEY (id, name)
);

ACC:

• Simple PRIMARY KEY definitions
  • 1: Definition as a column constraint
  • 2: Definition as a PRIMARY KEY attribute
  • 3: Definition as a CONSTRAINT attribute
• Validation rules were adjusted to bypass the composite key(one per table)

---

PS: it probably makes sense to move keys to a separated child of TableRoot to simplify tree structure & its validation. In this case, the new structure can look like:

Proposed syntax tree structure:

flowchart TD
    TableRoot(fa:fa-table TableRoot) --> Columns(fa:fa-columns Columns)
    TableRoot --> Keys(fa:fa-key Keys)
    
    Columns --> Column_1(Column_1 Name)
    Column_1 --> Column_1_DataType(Column_1 <br>Data Type)
    Column_1_DataType --> Column_1_DataType_Constraint("Column_1 <br>Data Type <br>Contstaint")
    Column_1 --> Column_1_Constraint_1(Column_1 <br>Constraint_1)
    Column_1 --> Column_1_Constraint_2(Column_1 <br>Constraint_2)
    
    Columns --> Column_2(Column_1 Name)
    Column_2 --> Column_2_DataType(Column_1 <br>Data Type)
    Column_2_DataType --> Column_2_DataType_Constraint("Column_1 <br>Data Type <br>Contstaint")
    Column_2 --> Column_2_Constraint_1(Column_1 <br>Constraint_1)
    Column_2 --> Column_2_Constraint_2(Column_1 <br>Constraint_2)

Loading

But this causes inconsistency between other keys and other constraints