Lookup tables

For a "tblFormObject" table whose every single record links to many differen
t
records in "tblFormInstances" table, I think I want
tblFormObject.frmId as Primary Key
tblFormInstances.frmId as Foreign Key
tblFormInstances.InstId as Primary Key
However, in the tblFormInstances table I also have a lookup column called
tblFormInstances.statId which should link to
tblFormStatuses:
tblStatId PrimaryKey
tblStatDescription
Obviously, tblFormInstances.statId is not a primary key in tblFormInstances
as different form instances can have the same form status! But How could I
set the relationship' I want tblFormInstances.StatId as primary and
tblFormStatuses.tblStatId as foreign' However, when I try to create such an
relationship with Enterprise Manager, ticking all options except cascade
update/delete, I get errror:
The columns in table 'tblFormInstances' do not match an existing
primary key or UNIQUE constraint
How could I resolve this? What keys do I need/want?Hi
I think that:
tblFormInstances.statId has a foreign key to tblFormStatuses:tblStatId
In the same way that
tblFormInstances.frmId has a Foreign Key to tblFormObject.frmId
It seems you are trying to create the foreign key for statId the wrong way
around!
John
"Patrick" wrote:

> For a "tblFormObject" table whose every single record links to many differ
ent
> records in "tblFormInstances" table, I think I want
> tblFormObject.frmId as Primary Key
> tblFormInstances.frmId as Foreign Key
> tblFormInstances.InstId as Primary Key
> However, in the tblFormInstances table I also have a lookup column called
> tblFormInstances.statId which should link to
> tblFormStatuses:
> tblStatId PrimaryKey
> tblStatDescription
> Obviously, tblFormInstances.statId is not a primary key in tblFormInstance
s
> as different form instances can have the same form status! But How could
I
> set the relationship' I want tblFormInstances.StatId as primary and
> tblFormStatuses.tblStatId as foreign' However, when I try to create such
an
> relationship with Enterprise Manager, ticking all options except cascade
> update/delete, I get errror:
> The columns in table 'tblFormInstances' do not match an existing
> primary key or UNIQUE constraint
> How could I resolve this? What keys do I need/want?|||Patrick,
tblFormInstances needs foreign key linking to both tblFormObject via frmId
and to tblFormStatuses via StatId.
hth
Quentin
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:E155F114-94C9-42B8-9108-F86C3B824BA0@.microsoft.com...
> For a "tblFormObject" table whose every single record links to many
> different
> records in "tblFormInstances" table, I think I want
> tblFormObject.frmId as Primary Key
> tblFormInstances.frmId as Foreign Key
> tblFormInstances.InstId as Primary Key
> However, in the tblFormInstances table I also have a lookup column called
> tblFormInstances.statId which should link to
> tblFormStatuses:
> tblStatId PrimaryKey
> tblStatDescription
> Obviously, tblFormInstances.statId is not a primary key in
> tblFormInstances
> as different form instances can have the same form status! But How could
> I
> set the relationship' I want tblFormInstances.StatId as primary and
> tblFormStatuses.tblStatId as foreign' However, when I try to create such
> an
> relationship with Enterprise Manager, ticking all options except cascade
> update/delete, I get errror:
> The columns in table 'tblFormInstances' do not match an existing
> primary key or UNIQUE constraint
> How could I resolve this? What keys do I need/want?|||I thought so too!
tblFormInstances.statId as primary key table
tblFormStatuses.statID as foreign key table
I have under Enterprise Manager for tblFormInstances:
Relationship name: FK_tblFormStatuses_tblFormInstances:
Primary Key Table: tblFormInstances statId
Foreign Key Table: tblFormStatuses statId
Check existing data on creation: ticked
Enforce relationship for replication: ticked
Enforce relationship for INSERTs and UPDATEs: ticked
Cascades: NOt ticked
but I get error saying "The columns in table 'tblFormInstances' do not match
an existing primary key or UNIQUE constraing"
Try swapping the table the other way round does not help either.
"Quentin Ran" wrote:

> Patrick,
> tblFormInstances needs foreign key linking to both tblFormObject via frmId
> and to tblFormStatuses via StatId.
> hth
> Quentin
>
> "Patrick" <questions@.newsgroup.nospam> wrote in message
> news:E155F114-94C9-42B8-9108-F86C3B824BA0@.microsoft.com...
>
>|||Hi
It would be easier to understand if you posted the DDL
http://www.aspfaq.com/etiquett_e.asp?id=5006
once you are used to the T-SQL syntaxes it is often easier to write the code
rather than mess around with the EM gui!
This does not sound correct:
Primary Key Table: tblFormInstances statId
Foreign Key Table: tblFormStatuses statId
Try making it
Primary Key Table: tblFormStatuses statId
Foreign Key Table: tblFormInstances statId
John
"Patrick" wrote:
[vbcol=seagreen]
> I thought so too!
> tblFormInstances.statId as primary key table
> tblFormStatuses.statID as foreign key table
> I have under Enterprise Manager for tblFormInstances:
> Relationship name: FK_tblFormStatuses_tblFormInstances:
> Primary Key Table: tblFormInstances statId
> Foreign Key Table: tblFormStatuses statId
> Check existing data on creation: ticked
> Enforce relationship for replication: ticked
> Enforce relationship for INSERTs and UPDATEs: ticked
> Cascades: NOt ticked
> but I get error saying "The columns in table 'tblFormInstances' do not mat
ch
> an existing primary key or UNIQUE constraing"
> Try swapping the table the other way round does not help either.
> "Quentin Ran" wrote:
>