check constraint

[shaddf]

select player_id,Gender,Shirt_no FROM player_details;
+-----------+--------+----------+
| player_id | Gender | Shirt_no |
+-----------+--------+----------+
|        10 | M      |       34 |
|        11 | M      |       12 |
|        12 | M      |       13 |
|        13 | M      |       34 |
+-----------+--------+----------+
13 rows in set (0.00 sec)

select player_id,Team_catId from players_team;
+-----------+------------+
| player_id | Team_catId |
+-----------+------------+
|        10 |          1 |
|        12 |          2 |
|        11 |          3 |
|        13 |          1 |
+-----------+------------+

i would like to put a check constraint on the shirt_no field,such that

 

no player in same team category has same shirt number

 

i have tried this:

 

CREATE TABLE player_details (
player_id          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
Fname VARCHAR(60) NOT NULL,
Gender ENUM('M','F'),

Shirt_no tinyint(2),

PRIMARY KEY (player_id ),
CONSTRAINT Shirt_number_taken
CHECK (NOT EXISTS
-- reference to second table
(SELECT *
FROM Soka_players_team_tbl AS M1
WHERE M1.player_id = Soka_player_details_tbl.player_id
))

);

 

but it is not working.

 

[NigelRel3]

Your check if the shirt number already exists is checking that a player_id already exists - nothing to do with the shirt number (or team)!

I'm not sure if your database structure is correct though - if a player is just part of one team - then put the team number on the player record .  If you want players to be in multiple teams - then put shirt number of the other record.

Then create a unique key on team, shirt number.

[Jacques1]

MySQL doesn't have CHECK constraints. You can create them, but they're completely ignored.