Jump to content
Sign in to follow this  
shaddf

check constraint

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 this post


Link to post
Share on other sites

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 this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

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

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.