Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

XSD DataSets and ignoring foreign keys

Tags:

.net

database

xsd

I have a pretty standard table set-up in a current application using the .NET XSD DataSet and TableAdapter features. My contracts table consists of some standard contract information, with a column for the primary department. This column is a foreign key to my Departments table, where I store the basic department name, id, notes. This is all setup and functioning in my SQL Server.

When I use the XSD tool, I can drag both tables in at once and it auto detects/creates the foreign key I have between these two tables. This works great when I'm on my main page and am viewing contract data.

However, when I go to my administrative page to modify the department data, I typically do something like this:

Dim dtDepartment As New DepartmentDataTable() Dim taDepartment As New DepartmentTableAdapter()  taDepartment.Fill(dtDepartment) 

However, at this point an exception is thrown saying to the effect that there is a foreign key reference broken here, I'm guessing since I don't have the Contract DataTable filled.

How can I fix this problem? I know I can simply remove the foreign key from the XSD to make things work fine, but having the additional integrity check there and having the XSD schema match the SQL schema in the database is nice.

like image 644
Dillie-O Avatar asked Aug 01 '08 16:08

Dillie-O


People also ask

Do foreign keys have to be the same data type?

According to WL#148, a foreign key column must have the same data type + the same length + the same scale as the corresponding referenced column.

Can a table have no foreign key?

You don't have to configure a foreign key constraint on a column just because it refers to another column. You could instead configure two tables such that one refers to the other, but without any defined foreign key.

Are foreign keys always necessary?

At that point, foreign keys really are neccessary, as they allow you to move the responsibility to a single point again. It's like an onion. FKs are the last layer of defense. Unless it's an embedded local database, apps trying to do referential integrity is always an bad idea.

Is foreign key mandatory for a table?

Note that foreign keys are not mandatory, and a table may have no foreign keys. Conversely, every column in a table may have a foreign key constraint.


1 Answers

You can try turning Check-constraints off on the DataSet (it's in its properties), or altering the properties of that relationship, and change the key to a simple reference - up to you.

like image 139
Greg Hurlman Avatar answered Oct 20 '22 06:10

Greg Hurlman