CODEWITHSUNDEEP
mysqlsql
user5331623
user5331623

Reputation:

MySQL Predefined Values

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)
);

Upvotes: 1

Views: 475

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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.

Upvotes: 1

Related Questions