# check constraint

Go to solution Solved by NigelRel3,

## Recommended Posts

```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.

##### Share on other sites

• Solution

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.

##### Share on other sites

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

##### Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.