tennis (again) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

tennis (again)

Author

Topic

Tim
Starting Member

392 Posts

Posted - 2003-04-27 : 02:34:07

In tennis, a singles match;

(A) can have zero or two players but not one player -- zero infers future match where players are unknown as yet

(B) a player cannot be his/her own opponent.

I get this far,

M = primary key of the match entity.
P = primary key of the player entity.

now the match-player relationship...

M,P doesn't enforce (A) but would enforce (B) if M,P was the composite primary key

M,P1,P2 doesn't enforce (B) but would enforce (A) if P1 and P2 were both mandatory.

How to implement this with just the relational model? That is I can't have any triggers or stored procedures.

It is driving me nuts.

Edited by - tim on 04/27/2003 02:46:08

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-27 : 10:00:23

Use B but set up a CHECK constraint saying P1 must not equal P2.

EDIT: changed to allow for ZERO players:

create table matches
(M int primary key,
P1 int,
P2 int,
CHECK ((P1 is Null AND p2 is Null) OR (P1 <> p2)))
GO

insert into matches -- succeeds
values (1,2,3)

insert into matches -- fails
values (2,2,2)

insert into matches -- succeeds
values (3, null, null)

GO
DROP TABLE Matches

- Jeff

Edited by - jsmith8858 on 04/27/2003 10:03:58

Tim
Starting Member

392 Posts

Posted - 2003-04-27 : 10:10:28

Thanks Jeff, but is CHECK not a SQL Servver specific feature? (I don't know)

I don't see how to do this in any datbase agnostic books on data modelling. That is, how is it possible at the logical level when DBMS implementation specifics cannot be assumed?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-27 : 10:29:43

The syntax might be specific to T-SQL (not sure either!) but I'm sure there must be an ANSI SQL standard for table row validation ...

I did a quick search but didn't find anything right away ... I'm sure someone will help out.

Or, I'm sure there's another way to do this ...

- Jeff

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2003-04-27 : 12:12:17

Check constraints is part of the ANSI sql specification and the syntax used by jsmith8858 is not specific for T-sql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources