Cross Row Referential Integrity

Cross Row Referential Integrity

• Brent Rector

  Well, First I wanted to have a constraint that works across all the rows e.g. all books in all rows have unique book id and all authors in all rows have unique author id.

  Then, since XML type is a schema collection, it can hold both authors and books in the same column to make my table generic. So, I thought may be I can extend the concept and create a PK-FK relationship between authors/books.

  
  
  

• farah

  Why do you want to put authors and books in the same column   This violates basic relational modeling fundamentals; I'd be very interested in knowing what you feel you'd gain by doing something like that.

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <Hemant K@discussions.microsoft.com> wrote in message news:d06f449c-15af-4a81-96a9-f32a06aa5366@discussions.microsoft.com...

  In SS05 XML typed columns, is it possible to have cross row constraints and referential integrity

  Say I have a column with two schemas: Author and Book. Each has an ID. Each row in the table has only one author or book. Can I enforce that all Author ID are unique across all rows and that there is an author for every book

  Is it possible now If yes, how If no, is there a plan to add this feature in the future Is there any work around now

  I am appending the script to demonstrate what I want to achieve.

  Thanks in advance.

  -------------------------------------------------------

  USE [pubs]
  GO

  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO

  --Drop the table
  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyXMLTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  drop table [dbo].[MyXMLTable]
  GO

  if exists (select * from sys.xml_schema_collections where name = N'XmlSchemaCollection')
  DROP XML SCHEMA COLLECTION XmlSchemaCollection
  GO

  --Create the schema
  CREATE XML SCHEMA COLLECTION XmlSchemaCollection AS
  N'< xml version="1.0" encoding="UTF-16" >
  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
  targetNamespace="http://www.test.com/XmlTest"
  xmlns ="http://www.test.com/XmlTest"
  elementFormDefault="qualified"
  attributeFormDefault="unqualified" >

  <xs:element name="Author">
  <xs:complexType>
  <xs:sequence>
  <xs:element name="auId" type="xs:long" />
  <xs:element name="auName" type="xs:string" minOccurs="0" />
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:schema>

  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
  targetNamespace="http://www.test.com/XmlTest"
  xmlns ="http://www.test.com/XmlTest"
  elementFormDefault="qualified"
  attributeFormDefault="unqualified" >

  <xs:element name="Book">
  <xs:complexType>
  <xs:sequence>
  <xs:element name="bookId" type="xs:long" />
  <xs:element name="title" type="xs:string" minOccurs="0" />
  <xs:element name="bookAuID" type="xs:long"/>
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:schema>' ;
  GO

  --Create the table
  CREATE TABLE [dbo].[MyXMLTable](
  i int primary key,
  recType varchar(max),
  [XMLData] xml (XmlSchemaCollection)
  ) ON [PRIMARY]
  GO

  DECLARE @s varchar(2048)

  -- insert records into Author table
  SET @s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest">
  <xns:auId>1</xns:auId><xns:auName>Tom</xns:auName></xns:Author>'
  INSERT INTO [dbo].[MyXMLTable] VALUES (1, 'Author', @s)
  SET @s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest">
  <xns:auId>2</xns:auId><xns:auName>Dick</xns:auName></xns:Author>'
  INSERT INTO [dbo].[MyXMLTable] VALUES (2, 'Author', @s)
  SET @s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest">
  <xns:auId>3</xns:auId><xns:auName>Harry</xns:auName></xns:Author>'
  INSERT INTO [dbo].[MyXMLTable] VALUES (3, 'Author', @s)

  -- insert records into Book table
  SET @s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
  <xns:bookId>1</xns:bookId>
  <xns:title>Butterflies</xns:title>
  <xns:bookAuID>1</xns:bookAuID>
  </xns:Book>'
  INSERT INTO [dbo].[MyXMLTable] VALUES (9, 'Book', @s)
  SET @s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
  <xns:bookId>2</xns:bookId>
  <xns:title>Tigers</xns:title>
  <xns:bookAuID>3</xns:bookAuID>
  </xns:Book>'
  INSERT INTO [dbo].[MyXMLTable] VALUES (10, 'Book', @s)
  SET @s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
  <xns:bookId>3</xns:bookId>
  <xns:title>Elephants</xns:title>
  <xns:bookAuID>2</xns:bookAuID>
  </xns:Book>'
  INSERT INTO [dbo].[MyXMLTable] VALUES (11, 'Book', @s)
  SET @s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
  <xns:bookId>4</xns:bookId>
  <xns:title>Eagles</xns:title>
  <xns:bookAuID>3</xns:bookAuID>
  </xns:Book>'
  INSERT INTO [dbo].[MyXMLTable] VALUES (12, 'Book', @s);

  -- Table View with a join
  WITH XMLNAMESPACES ('http://www.test.com/XmlTest' AS xns)
  SELECT A.i, A.[XMLData].value('
  (/xns:Book/xns:title)[1]', 'nvarchar(max)') as Title,
  B.[XMLData].value('
  (/xns:Author/xns:auName)[1]', 'nvarchar(max)') as [Author Name]
  FROM dbo.MyXMLTable A inner join dbo.MyXMLTable B on
  A.[XMLData].value('(/xns:Book/xns:bookAuID)[1]', 'int') =
  B.[XMLData].value('(/xns:Author/xns:auId)[1]', 'int')
  WHERE (B.[XMLData].exist('/xns:Author[xns:auId=3]')=1)

  
  

• StevenBerr

  Thank you John.

  I was hoping that SQL Server 2005 and XML provide some support for cross-row constrains.

  
  
  

• C.Z.

  This may work, I haven't tried it:

  Create a persisted computed column which promotes out the author id as the primary key for the table, and another persisted computed column which promotes out the bookId. Then create a 1:n PK-FK relationship between those two computed columns.

  To create the computed columns you will have to create a UDF which wraps the XQuery invocation. SQL Server 2005 does not support XQuery directly in comptued column definitions or check constraints.

  
  
• Cross Row Referential Integrity