Foreign key for Xml Datatype

  • Is foreign key constraint possible in xml data type?

    OR

    Is it possible to mention multiple Foreign keys  in a single cell? For ex: If xml has 2 rows, 2 ids will be given in main col with some separator, if xml has 3 rows then 3 keys so on.

    Can it be done?

  • What sort of foreign key do you have in mind? The XML column itself cannot be a foreign key, since you cannot have an index, and thus not a primary key on an XML column.

    Or are you thinking of references within the XML document, through the XSD?

     

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • You could do this with a trigger - the trigger gets the inserted XML, extracts the elements you want and checks they exist in the primary key table. If the elements don't exist, the trigger performs a rollback. You would have to check performance, as this could be slow depending on how complex your XML is and how many rows you are adding.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply