Member Avatar for Emerald214

I have 2 tables: Match and Team.
Match(matchID is primary key, team1, team2, ...)
Team(TeamID is primary key, ...)

I mean how to force team1 and team2 is different from each other.
Ex: We won't want to see: Brazil vs Brazil ^^

Any help is appreciated...

  • 3 Contributors
  • 3 Replies
  • 66 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by jbennet

Dani AI

Generated

Short answer and practical options (building on , and ).

A quick, simple fix inside Access is a table-level validation rule. In Table Design open the table properties and set the Validation Rule to something like:
[Team1] <> [Team2]
and put a friendly message in Validation Text. That prevents saving a record where both team fields are the same and gives a clear error to the user.

Notes and alternatives:

  • 's composite-key idea enforces uniqueness across rows but does not stop a single record having identical values in both fields; it is not a substitute for the inequality check.
  • If users enter matches through a form, add a check in the form's BeforeUpdate event (cancel the update and show an error) so the UI never tries to save bad data.
  • If the application performs programmatic updates (update/append queries, linked back end), verify that your chosen validation is enforced in that workflow. For a backend that supports it (SQL Server, etc.) add a CHECK constraint there, or in Access 2010+ use a table Data Macro (Before Change / Before Insert) to raise an error — those are the most robust ways to stop any kind of write that would set both teams equal.
  • Also make both team fields proper foreign keys to the Team table to prevent invalid IDs, and consider a normalized “canonical pair” field plus a unique index if you also want to prevent duplicate matches in either order (A vs B and B vs A).

Recommended Answers

All 3 Replies

Member Avatar for Emerald214

any help? 1 week goes by...

Member Avatar for soporific

dude, provide much much more info than that or we can't help

Member Avatar for jbennet

Composite key?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.