Can't Create a Relationship, Even though My Tables are Fine! 1

[Rexolio]

Here are my tables:

tblClients
----------
ClientID (Identity Integer field, Index and Primary Key)
FName
LName
Blah blah blah

tblClient_Markets
------------------
ClientID (Int field)
MarketID (Int Field)
BOTH ARE THAT TABLES PRIMARY KEY

When I try to create a one to many relationship from tblClients to tblClient_Markets I get the following error:

'tblClients' table saved successfully
'tblClient_Markets' table
- Unable to create relationship 'FK_tblClient_Markets_tblClients'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblClient_Markets_tblClients'. The conflict occurred in database 'perfectweddingguideSQL', table 'tblClients', column 'ClientID'.

HELP!!!
rexolio@bellsouth.net
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[topher0303]

Are you appling the foreign key to tblClients table or the tblClient_Markets table? The foreign key should be applied to the tblClient_Markets table:

alter table tblClient_Markets
add constraint FK_tblClient_Markets_tblClients
foreign key (ClientID)
references tblClients (ClientID)

 

[Rexolio]

Hi topher, thanks for replying...

Are you sure? I was under the impression it should be the other way around because there will only be ONE record for each client in tblClients, but each client could be in many markets, there for I would think that the foreign key would be in tblClient_Markets.

tblClients
----------
1 D. Smith
2 G. Jones
3 T. Adams

tblClient_Markets
-----------------
1 ATL
1 JAX
2 NYC
3 ATL
3 NYC
3 JAX

Also, there is another table, tblClient_Orders, where there is also ClientID, because it holds the different orders placed by each client. I get a similar message when I try to create a relationship between tblClients and tblClient_Orders. I have another database with similar tables, similar designs and the relationships never caused me a problem. I pulling my hair out on this one!

Thanks in advance for your (or anyone's) assistance!
rexolio@bellsouth.net
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[topher0303]

Yep, I'm sure. Foreign keys are created on the child table (the one with the multiple values). The foreign key references the parent table but is applied to the child table.

Does your foreign key constraint look like the one I posted? If not, try it. Also, post the script of your foreign keys.

Chris.

 

[Rexolio]

Maybe you can take a look at the following and see more of what my confusion is.

Here is a diagram of some of the tables that I was able to get a relationship put together without any problem:

http://216.53.190.158/pub_staff.jpg

Here is a diagram of the tables I'm having problems with even though they are setup structurally very similar to many of the tables in the other diagram:

http://216.53.190.158/client_location.jpg

Thanks again for your time!
rexolio@bellsouth.net
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[Rexolio]

Here's another question... I'm using the diagram feature of SQL Server 2000 to set up my relationships. I have a TON of tables, so I'm making different diagrams for different colletions of tables (see photos via the links in previous post.) tblMarkets, which most all of the tables associate with, appears in both. Is it possible to set up relationships this way, or do I need to put everything in one diagram and set up the relationships that way? Hope not, because that seems very silly and will take forever considering I have to connect to SQL Server via the internet.

Just a thought, because I'm out of ideas.
rexolio@bellsouth.net
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[Rexolio]

Okay, finally I was able to get the relationships going. But here's the thing and if someone could tell me if this is good or bad and what it really means, I'd appreciate it...

in order to get the relationships to save, when I created them, I had to uncheck "Check existing data on creation".
rexolio@bellsouth.net
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[topher0303]

I would say it is bad. It means that you have records in your child tables that you do not have a parent record for. For example, you have a tblClient_Markets.ClientID value that does not exist in tblClients.ClientID. In otherwords, you have a client market record for a non-existent client.

You should be able to find which client markets that are missing clients with the following SQL:

SELECT cm.*
FROM tblClient_Markets cm
LEFT JOIN tblClients c ON cm.ClientID = c.ClientID
WHERE c.ClientID IS NULL


Chris.

 

[Rexolio]

Thanks Chris. That's a huge help!!!!
rexolio@bellsouth.net
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes