CODEWITHSUNDEEP
sqlsql-server-2008
Muhammad Umer Asif
Muhammad Umer Asif

Reputation: 255

SQL Relationship Error

I have a database and for the sake of simplicity, lets say it has two tables. One is Employee and the other is EmployeeType. In the Database diagram i am trying to build a relationship of the two tables but it gives me the following error....

'EmployeeType (HumanResources)' table saved successfully
'Employee (HumanResources)' table
- Unable to create relationship 'FK_Employee_EmployeeType'.  
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Employee_EmployeeType". The conflict occurred in database "TheDB", table "HumanResources.EmployeeType", column 'iID'.

I don't know what is wrong with it. please help...

Upvotes: 0

Views: 2309

Answers (2)

voidstate
voidstate

Reputation: 7990

A simple answer: you have data in your tables which would break this relationship, so you cannot create the relationship.

In this case, check the Employee table and look for rows where the id for EmployeeType (EmployeeTypeId?) is either NULL or illegal (not to be found in the EmployeeType table). Fix these rows and the relationship will save OK.

Upvotes: 1

CptSupermrkt
CptSupermrkt

Reputation: 7134

Ouch, so many downvotes. At least he bothered to post the error message. As Daniel A. White said above, you likely already have the foreign key you're trying to make. Your tag says you're using SQL Server 2008, so I'm going to assume you Microsoft SQL Server Management Studio. If that is the case, one mistake I used to make (and sometimes still do!), is I go to create a new relationship by right-clicking any of the columns in the Design view of the table I want to make a foreign key, and hit Relationships. In the Foreign Key Relationships menu that appears, I hit "Add" at the bottom, and then expand the "Tables and Columns Specification" section. Then I hit the "..." next to "Tables and Columns Specification," which lets you choose what columns you want to relate.

Now the problem here that I sometimes run into is, if you don't finalize this relationship right here, you're setting yourself up for situations like this. The reason is, is that when you hit "Add" a few steps back, you already created a foreign key on this table. Even if you cancel out and close everything, the table retains that foreign key. If you experimented around with this, you may have already accidentally created the relationship, you just didn't know it then.

I realized how often I made this mistake when I first started, because I looked back at my very first project a year after I began working with SQL Server 2008, and the Foreign Key Relationships are funny, because I have a ton of foreign key entries pointing to no where in every table, where I obviously was just experimenting as I was learning.

Another thing, just to cover some more bases here, is that you mentioned you were doing this in the Database Diagram view. Don't think there's anything wrong with that, but I've found that sometimes doing things via the GUI, it can be kind of flakey. By that, what I mean is, sometimes I will want to make a really small and minor change, but the GUI will tell me that I can't do that kind of change after the table has been made. But then if I just do exactly what I want to do via a typed out query, it runs just fine.

The ultimate test to try to see if you have your desired functionality is to simply try putting in a record that would violate the foreign key restraint. If it goes through, then the relationship isn't there or it's not set up properly. If it fails, then you know you've already got what you want.

I think you got some downvotes because this isn't a very complicated problem/should usually be easily solvable on your own, especially with Google. Since it's not, but you bothered to post anyway, I'm assuming you're somewhat new to this, so I just wanted to throw out my advice as a fellow newbie. Don't let the downvotes get you down, just maybe next time ask a little more concrete question other than "what's wrong?" For instance, "This error seems to be telling me the key already exists. Can anyone tell me how to check and see what relationships already exist? I don't remember making this relationship, how could this have happened?" etc.

Upvotes: 4

Related Questions