Add Foreign Key to the Table as a column



  • I have two tables as below:

    Table name: table_1

    ID studentId Date enrolled parent name parents annual income
    1 12345 2022/01/13 14:39:15 enrolled ABC 12345
    2 12345 2022/01/13 14:39:06 not-enrolled ABC 10345
    3 12345 2022/01/13 8:03:44 not-enrolled ABC 1467

    I am working on refactoring this table as it has some flaws and redundant data. Also, the date is mentioned in this table as VARCAR(20).

    I want to refactor it into 2 tables as below:

    Table A --Only 1 entry for each student ID, the first date when entry was made in the table, the latest enrolled status of the student, and parent name (made the entries in the table_1 bold)

    Table name: table_2

    ID studentId strat_date enrolled parent name
    1 12345 2022/01/13 8:03:44 enrolled ABC

    Table B -- The rest of the history data

    Table name: table_3

    ID studentId_1 Date parents annual income
    1 1 2022/01/13 14:39:15 12345
    2 1 2022/01/13 14:39:06 10345
    3 1 2022/01/13 8:03:44 1467

    The "ID" column in the "table_2" table is the PRIMARY KEY. The "studentId_1 " column in the "table_3" table is a FOREIGN KEY.

    I know how to create table_2.

    INSERT IGNORE INTO table_2 (studentId, `start_date`, `status`, `parent name`)
    SELECT studentId, MIN(`Date`), MIN(enrolled), `parent name`
    FROM table_1
    GROUP BY studentId, `parent name`;
    SELECT * FROM table_2;
    

    Which gives an output:

    ID studentId strat_date enrolled parent name
    1 12345 2022/01/13 8:03:44 enrolled ABC

    How do I create table_3 as it has "FOREIGN KEY" in the table as below:

    ID studentId_1 Date parents annual income
    1 1 2022/01/13 14:39:15 12345
    2 1 2022/01/13 14:39:06 10345
    3 1 2022/01/13 8:03:44 1467


    • MIN(enrolled) is kludgy. It works only because of string choices and comparison.

    • BUG! MIN(Date), MIN(enrolled) cannot be trusted to get data from the same row. You have a "groupwise-max" problem; see the tag.

    • You need INDEXes; FKs are optional.

    • ID on table_3 is unnecessary; the PK can be PRIMARY KEY(student_id, date)

    • Do use DATETIME, not VARCHAR.

    • Table_2 does not need ID; PRIMARY KEY(student_id) is really what you need.

    Since you seem to be building students (table_2) and income_history (table_3) from table_1, these two steps might work:

    INSERT INTO students (...)
        SELECT ... FROM table_1
            WHERE enrolled = 'enrolled';
    INSERT INTO income_history (...)
        SELECT ... FROM table_1;
    

    (If there are more options for enrolled or a student can be "enrolled" twice or ..., then the above won't work.)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2