Table constraints problem
-
Hi everyone, I am creating a table to represent sporting results and so far have the following columns: --------------------------------------------------------------- game_ID date home_ID away_ID home_score away_score --------------------------------------------------------------- I have added an integrity check to ensure that home != away, as well as a 'unique' statement with (date, home) and (date, away), however this will still allow the same team to play two games on the same date; one home and one away. Is there a way to build a table so that no team can play two games on the same date or do I need to check this myself whenever I insert a new result?
-
Hi everyone, I am creating a table to represent sporting results and so far have the following columns: --------------------------------------------------------------- game_ID date home_ID away_ID home_score away_score --------------------------------------------------------------- I have added an integrity check to ensure that home != away, as well as a 'unique' statement with (date, home) and (date, away), however this will still allow the same team to play two games on the same date; one home and one away. Is there a way to build a table so that no team can play two games on the same date or do I need to check this myself whenever I insert a new result?
JenovaProject wrote:
Is there a way to build a table so that no team can play two games on the same date
Add either
unique constraintorunique indexon those columns (single constraint with two fields). Just make sure that the time portion in the date is the same. Something like:ALTER TABLE XYZ ADD CONSTRAINT UK_SingleGame UNIQUE (date, home_id);
Then if you want you can create a similar constraint for date and away_id.
The need to optimize rises from a bad design. My articles[^]
modified on Saturday, November 8, 2008 2:58 AM