I want to create a table for the following:

1. One pokemon must have and can only have one trainer.

2. One pokemon may evolve into many other pokemons.

3. The pokemon name has a maximum of 50 characters and can not be blank.

4. The dex number has 3-numeric-digit and can not be blank.

5. The pokemon height is a whole number and ranges [0, 1,000] cm inclusive.

6. The pokemon weight ranges [0.00, 1,000.00] kg inclusive.

7. The pokemon color has a maximum of 20 characters.

8. The pokemon type has a maximum of 15 characters. If an INSERT doesn't list the pokemon type value, it should default to Grass

I am stuck specifically on number 5. How do I apply that to my table? Also, another question is how do I apply numbers 5-6 and 8 also? How can I also assign the EvolvedFrom as one of the Foreign Keys? I don't know where to refrence it. I am a little bit confused, so please help. Thank you so much.

Table Reference

ERD of the table to be created

This is my sample code that I have created:

CREATE TABLE pokemon(
pokemonId int,
pokemonDexNum int(3) NOT NULL,
pokemonName varchar(50) NOT NULL,
pokemonHeightCm  int unsigned,
 CONSTRAINT CHK_pokemonHeightCm  CHECK (pokemonHeightCm  <= 1000)
pokemonWeightKg int NOT NULL,
pokemonColor varchar(50),
pokemonType varchar(50),
trainerId int NOT NULL,
evolvedFrom varchar(50),
PRIMARY KEY (pokemonId),
FOREIGN KEY (trainerId) REFERENCES trainer(trainerId),
FOREIGN KEY (evolvedFrom) REFERENCES pokemon(evolvedInto) --THIS LINE IS INCORRECT. HELP--
);

CodePudding user response：

Try to include a constrain as below.

CREATE TABLE pokemon( pokemonHeightCm int() NOT NULL, CONSTRAINT pokemonHeightCm_Ck CHECK (pokemonHeightCm BETWEEN 0 AND 1000), ... and the rest

CodePudding user response：

Try the below, I haven't tested the syntaxes:

CREATE TABLE pokemon(
pokemonId int ,
pokemonDexNum int(3) NOT NULL, #rule 4
pokemonName varchar(50) NOT NULL, #rule 3
pokemonHeightCm int NOT NULL, CONSTRAINT CHK_pokemonHeightCm  CHECK (pokemonHeightCm  <= 1000), #rule 5
pokemonWeightKg decimal NOT NULL, CONSTRAINT CHK_pokemonWeightKg  CHECK (pokemonWeightKg  <= 1000.00), #rule 6
pokemonColor varchar(20), #rule 7 
pokemonType varchar(15) NOT NULL DEFAULT 'Grass', #rule 8
trainerId int NOT NULL,
evolvedFrom int,
PRIMARY KEY (pokemonId),                            #rule 1
FOREIGN KEY (trainerId) REFERENCES trainer(trainerId), #rule 1
FOREIGN KEY (evolvedFrom) REFERENCES pokemon(pokemonId)  #rule 2
);