Records (Values) from One-Column Table Instead of Enum Set
I am having a difficult time writing the ALTER TABLE statement so that the following is true:
1. A column in the main main table (Table `A`) is basically an enum data type but will no longer be an enum data type because the set of values is constantly being edited. Call the column `col_2`.
2. The set of values for that column will now come from another table---a one-column table---(Table `B`) in which the records (rows) of that table represent the enum set.
3. IMPORTANT: record/row creation (insert) or (editing) updating/replacing in Table A requires a col_2 check to make sure values conform to permissible values in Table B (its single column)
4. IMPORTANT: the reverse is true: changes to Table B record/row requires an update in Table A col_2. In the case of editing (update/replace), Table A col_2 is updated/replaced with same string (varchar type). In the case of deletion, the record/row in Table A containing a no longer valid col_2 will not have NULL entered on col_2 NOR the record deleted, but rather the record will be *moved* to Table C (which is created if it does not exist), and will have identical table structure to Table A but col_2 will have a NULL value entered.
I am guessing that can be done in one long CREATE or ALTER TABLE statement.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread