Error trying to create relationship/diagram 1

[tubbers]

I'm still learning SQL Server and am having problems creating relationships between the tables in my database. I'm more familiar with MS Access.

I have about 5 tables in my database and each table has a primary key, which I set in the Enterprise Manager by clicking on the "key" icon. The keys on each table are varchar data types and vary from 4 to 7 characters in length.

Table: Character
Field Datatype
Code varchar(7) - Primary Key
Rel varchar(4)

Table: Release
Field Datatype
Rel varchar(4) - Primary Key

Table: Detection
Field Datatype
Code varchar(7) - Primary Key
Source varchar(13) - Primary Key

When I try to create the relationships in a diagram I get the following error:

'Release' table saved successfully
'Characteristics' table saved successfully
'Detection' table
- Unable to create relationship 'FK_Detection_Characteristics'.
  
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Detection_Characteristics'. The conflict occurred in database 'Project', table 'Characteristics', column 'char_Tag'.

In the wizard, I've got all the boxes checked such as: Check existing data on creation, enforce relationship for replication, etc. The keys were created as Clustered.

I've checked to make sure there is no duplicate data and have also tried to create the relationship without data in the tables and I still get the same error.

I would appreciate a little help in figuring out the problem. My knowledge of SQL Server is very limited.

I'm using SQL Server 2000 Developer Edition with Access Project as a front-end

 

[vongrunt]

How is column 'char_Tag' with another tables? Error says pretty much all necessary info...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[tubbers]

Sorry, that's an error on my part. Column 'char_tag' should have been 'code':

'Release' table saved successfully
'Character' table saved successfully
'Detection' table
- Unable to create relationship 'FK_Detection_Character'.
  
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Detection_Character'. The conflict occurred in database 'Project', table 'Character', column 'code'.

I had originally been trying to create the diagram in Enterprise Manager. A few minutes ago I tried it in the Project front-end and was able to successfully create the relationships there. So problem solved...maybe?

 

[tubbers]

Or not...
Just tried adding another table to the relationship (through Project front-end) and received this error:

'Character' table saved successfully
'Detection' table
- Unable to create relationship 'FK_Detection_Character'.  
ADO error: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Detection_Character'. The conflict occurred in database 'Project', table 'Character', column 'code'.

 

[vongrunt]

Errors like that mean value in foreign table does not exist in primary table and foreign key cannot be created. Usual reasons: bad data in tables or relationship dragged onto wrong column .

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[tubbers]

I thought for sure my data was accurate and couldn't possibly be bad so I was struggling to figure out what was wrong.

I've sorted the problem out now and it was indeed because there were records in the foreign table that didn't match the primary table.

Thanks for the help.