Is it possible to set a foreign key constraint on data in XML?

Question

Some node values ​​inside an XML column column contain integer primary keys of known tables. Can I force SQL Server to check the foreign key relationship?

Answer 1

Answer: yes you can!

With the limitation that your document contains a fixed number of such links, you can create persistent computed columns for your foreign key as such.

CREATE FUNCTION dbo.GetFooRef(@doc XML) RETURNS INT WITH SCHEMABINDING AS BEGIN RETURN @doc.value(N'(/doc/foo/@id)[1]','int'); END; CREATE TABLE Foo(id INT PRIMARY KEY); CREATE TABLE Bar( doc XML, ref AS (dbo.GetFooRef(doc)) PERSISTED FOREIGN KEY REFERENCES dbo.Foo(id) ); INSERT INTO dbo.Bar(doc) VALUES ('<doc><foo id="1"/></doc>'); --The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Bar__ref__ ..." INSERT INTO dbo.Foo(id) VALUES (1); INSERT INTO dbo.Bar(doc) VALUES ('<doc><foo id="1"/></doc>'); 

Answer 2

The answer is no, you cannot.

You cannot directly use XML value() in FOREIGN KEY . For fun, I tried to create a computed column that has the result of calling .value('<some xpath>', 'int') wrapped in a user-defined function. The problem is that you also cannot use the computed column in relation to the FOREIGN KEY .

Outside of the actual limit, you can try to test incoming INSERT and UPDATE in a trigger ... but this is already becoming a solution problem. As Damien says, this doesn’t really apply to an XML document if you want to apply a foreign key constraint.

Answer 3

I would post this as a comment, but I don't have enough reputation: D. I think you can add a constraint that returns the value of the function that will be valid what you want in your xml. It would not be a foreign key, but it would at least verify your integrity.