I'm using (evaluating) a data modelling tool and have situations where I
have fields that need to be part of the primary key for a table, and also be
a foreign key to another table. This particular product will not let me do
this, a field has to be one or the other. Since I haven't previously tried
to enforce actual server level foreign key contraints, I wasn't sure if this
was a limitation of this product, or whether there is some valid reason why
this isn't a good design and the product is trying to "protect" me.

A simple example, basically a table that acts as an intersection for two
other tables:

Class Table
  ClassCode (Primary)
  Data

Type Table
  TypeCode (Primary)
  Data

ClassType Table
  ClassCode (Primary & FK)
  TypeCode (Primary & FK)
  Data
  Data

I've done this type of design for years in many different systems and, IMO,
is normal and proper. Do I need a different data modeller, or a lesson in
RDBMS design? If the former (hopefully), than I'd welcome some endor{*word*224}ts
of your favorite SQL data-modelling tool.

Thanks

* Wayne Niddery - WinWright Consulting
* Host of RADBooks at http://home.ican.net/~wniddery/RADBooks.html
* -- Amazon.com Associate -- Delphi, C++Builder, JBuilder, InterDev
* ...remove X when replying...

This perfectly legal design.
If the tool doesn't support it, get another modeller!

I use data-architect from Sybase. This tool supports this constructs.
(It is a pricey tool though).

Edwin van der Elst.
Finalist software

Wayne Niddery <winwri...@Xshaw.wave.ca> wrote in article
<6cg97e$29...@forums.borland.com>...

Hmm, it really does seem odd, doesn't it ... I can't see any compelling
reason why the design tool should force you to avoid this kind of
construction at a physical table level. What you are trying to do seems to
be perfectly reasonable.

In your tool, as well as specifying that a given column is part of either a
primary key or foreign key, is there a facility for specifying row-level
contraints? If so, would it be possible to do it that way? I.e., in much
the same way that, in an SQL 'create table', instead of writing:
        ClassCode integer not null references Class (ClassCode),
I could write:
        ClassCode integer not null,
        ..... // some more column definitions
        foreign key FKClass (ClassCode) references Class (ClassCode),

Edwin van der Elst wrote in message
<01bd3d26$1c1dab80$9f00a...@ed2.finalist.nl>...

* Wayne Niddery - WinWright Consulting
* Host of RADBooks at http://home.ican.net/~wniddery/RADBooks.html
* -- Amazon.com Associate -- Delphi, C++Builder, JBuilder, InterDev
* ...remove X when replying...

* Wayne Niddery - WinWright Consulting
* Host of RADBooks at http://home.ican.net/~wniddery/RADBooks.html
* -- Amazon.com Associate -- Delphi, C++Builder, JBuilder, InterDev
* ...remove X when replying...