Selecting Data Type In MySQL

Question

CREATE TABLE Game
(
GameID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
DatePlayed DATE NOT NULL,
BoardNum TINYINT NOT NULL,
Score ENUM('1-0', '0-1', '2-2') NOT NULL,
MatchID INT NOT NULL,
WhitePlayer CHAR(20) NOT NULL,
BlackPlayer CHAR(20) NOT NULL,
CONSTRAINT fk_MatchID FOREIGN KEY (MatchID) REFERENCES MatchPlay (MatchID),
CONSTRAINT fk_WhitePlayer FOREIGN KEY (WhitePlayer) REFERENCES Player (PlayerName),
CONSTRAINT fk_BlackPlayer FOREIGN KEY (BlackPlayer) REFERENCES Player (PlayerName)
);

I'm unable to find a solution online, maybe someone can help. The Score entity above I am looking a data type that would save the data input say 4-2, 2-1, 1-0 example. Something like this format [0-9]-[0-9] . So basically, it wont allow the user to enter 10-0 only between [0-9]-[0-9] . Also would it be ENUM or different data type.

Answer 1

I think it's not possible to use SQL that way. Data manipulation should not occur in the sql, it's better in the controller if you use MVC concept. It's much safer.

in your case, you can use varchar or integer datatype as alternative.