Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Enforce name to be UNIQUE in Schedule Table schema #98

Open
jiangyilin123 opened this issue Sep 26, 2024 · 0 comments
Open

Enforce name to be UNIQUE in Schedule Table schema #98

jiangyilin123 opened this issue Sep 26, 2024 · 0 comments
Labels
enhancement New feature or request

Comments

@jiangyilin123
Copy link
Collaborator

jiangyilin123 commented Sep 26, 2024

The current schedule table allows duplicates in the name field. However, if a new table wants to reference the name of the schedule table as a foreign key, it will fail to do so because the "name" is not unique or a primary key.

The following is one example: in this PR, the author want to reference name in support_schedules as the foreign key in support_occupant_physical_characteristics table.

       Thank you for the comment. I will follow Weili's suggestion at this stage. Originally, the `support_occupant_physical_characteristics` has the following code, which sets `name` in `support_schedules` as the foreign keys. However, this setting cannot pass the test because `name` is not a unique value. Do you have any suggestions to do this right?  
CREATE_PHYSICAL_CHAR_TABLE = """
CREATE TABLE IF NOT EXISTS support_occupant_physical_characteristics (
    id INTEGER PRIMARY KEY,
    physical_characteristic_name TEXT NOT NULL,
    schedule_activity_level TEXT NOT NULL,
    schedule_clothing_insulation TEXT NOT NULL,
    schedule_air_velocity TEXT NOT NULL,
    work_efficiency NUMERIC,
    co2_generation NUMERIC,
    co2_generation_units TEXT,
    annotation TEXT,
    FOREIGN KEY(schedule_activity_level) REFERENCES support_schedules(name),
    FOREIGN KEY(schedule_clothing_insulation) REFERENCES support_schedules(name),
    FOREIGN KEY(schedule_air_velocity) REFERENCES support_schedules(name)
);

Originally posted by @jiangyilin123 in #97 (comment)

@jiangyilin123 jiangyilin123 added the enhancement New feature or request label Sep 26, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

1 participant