MySQL Predefined Values

Question

I am currently learning to use MySQL and a bit stuck and wondering if someone can help. Below is one of my tables for CD information. It works but I need to change the Genre field so that instead of a user typing in a Genre, they can pick from a preselected list of values. I am really unsure how to do this and wondering if someone can help steer me on this.

CREATE TABLE CD (
    Unique_CD_Identification_Number INT NOT NULL,
    CD_Title VARCHAR(23) NOT NULL,
    CD_Year YEAR(4)  NOT NULL,
    CD_Price DECIMAL(2,2) NOT NULL,
    CD_Description VARCHAR(56) NOT NULL,
    CD_Genre  VARCHAR(10) NOT NULL,
    Unique_Production_Number INT,
    FOREIGN KEY (Unique_Production_Number) REFERENCES Production(Unique_Production_Number),
    PRIMARY KEY (Unique_CD_Identification_Number)
);

Gordon Linoff · Accepted Answer · 2015-09-13 19:48:19Z

1

Make CD_Genre instead CD_GenreId. Then this should be a foreign reference into a Genres table:

CREATE TABLE Genres (
    GenreId int not null auto_increment primary key,
    Name varchar(255) not null
);

CREATE TABLE CD (
    Unique_CD_Identification_Number INT NOT NULL,
    CD_Title VARCHAR(23) NOT NULL,
    CD_Year YEAR(4)  NOT NULL,
    CD_Price DECIMAL(2,2) NOT NULL,
    CD_Description VARCHAR(56) NOT NULL,
    CD_GenreId int NOT NULL,
    Unique_Production_Number INT,
    FOREIGN KEY (Unique_Production_Number) REFERENCES Production(Unique_Production_Number),
    FOREIGN KEY (CD_GenreId) REFERENCES Genres(GenreId),
    PRIMARY KEY (Unique_CD_Identification_Number)
);

You can then use the Genres table to populate your list.

answered Sep 13, 2015 at 19:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Predefined Values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related