database errors: integrity

Showing posts with label integrity. Show all posts

Sunday, February 19, 2012

cross-database referential integrity?

Is cross-database referential integrity possible? Doesn't seem to be with a
standard foreign key constraint but are there "alternative" methods of
maintaining referential integrity across databases? Thanks.
LC
Use Triggers instead.
HTH, Jens Smeyer
http://www.sqlserver2005.de
"LC" <LC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:73B1B5DB-9477-41EE-9A4A-EEA528871BD3@.microsoft.com...
> Is cross-database referential integrity possible? Doesn't seem to be with
> a
> standard foreign key constraint but are there "alternative" methods of
> maintaining referential integrity across databases? Thanks.
> LC
|||Thanks Jens, that should work.
"Jens Sü?meyer" wrote:

> Use Triggers instead.
> HTH, Jens Sü?meyer
> --
> http://www.sqlserver2005.de
> --
> "LC" <LC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:73B1B5DB-9477-41EE-9A4A-EEA528871BD3@.microsoft.com...
>
>
|||yuck.
I'm no fan of triggers (Although IN the db that is your only option)
You could "consider" doing this in an DB_Accessor layer. (Enforce via Code)
Greg Jackson
PDX, Oregon

cross-database referential integrity?

Is cross-database referential integrity possible? Doesn't seem to be with a
standard foreign key constraint but are there "alternative" methods of
maintaining referential integrity across databases? Thanks.
LCUse Triggers instead.
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"LC" <LC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:73B1B5DB-9477-41EE-9A4A-EEA528871BD3@.microsoft.com...
> Is cross-database referential integrity possible? Doesn't seem to be with
> a
> standard foreign key constraint but are there "alternative" methods of
> maintaining referential integrity across databases? Thanks.
> LC|||Thanks Jens, that should work.
"Jens Sü?meyer" wrote:

> Use Triggers instead.
> HTH, Jens Sü?meyer
> --
> http://www.sqlserver2005.de
> --
> "LC" <LC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:73B1B5DB-9477-41EE-9A4A-EEA528871BD3@.microsoft.com...
>
>|||yuck.
I'm no fan of triggers (Although IN the db that is your only option)
You could "consider" doing this in an DB_Accessor layer. (Enforce via Code)
Greg Jackson
PDX, Oregon

Thursday, February 16, 2012

Cross Row Referential Integrity

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)

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.

|||? 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 MachanicPro SQL Server 2005, available nowhttp://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 ONGOSET QUOTED_IDENTIFIER ONGO --Drop the tableif 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 schemaCREATE 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 tableCREATE TABLE [dbo].[MyXMLTable](i int primary key, recType varchar(max),[XMLData] xml (XmlSchemaCollection)) ON [PRIMARY]GO DECLARE @.s varchar(2048) -- insert records into Author tableSET @.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 tableSET @.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 joinWITH 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)|||

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.

|||

Thank you John.

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

Tuesday, February 14, 2012

Cross database integrity

Is it possible to create foreign key between to distinct SQL databases ?
Database A contains table [customers] (primary key : customer_id)
Database B contains table [invoices] (each invoice should be linked to
a customer. Foreign key : customer_id)
I would like to create an integrity constraint between
[invoices].[customer_id] and [customers].[customer_id], despite the fact
that [invoices] et [customers] tables are in SEPARATE databases.
Is it possible to do that ?
Thanks in advance
Tom
The short answer is no. The more complex answer is: Yes, you would have to
enforce cross database constraint integrity using a Trigger. There are
security and other issues that will also have to be addressed.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tom McLeesh" <tom.mcleesh@.gmail.com> wrote in message
news:u7%23a7148GHA.4572@.TK2MSFTNGP02.phx.gbl...
> Is it possible to create foreign key between to distinct SQL databases ?
> Database A contains table [customers] (primary key : customer_id)
> Database B contains table [invoices] (each invoice should be linked to a
> customer. Foreign key : customer_id)
> I would like to create an integrity constraint between
> [invoices].[customer_id] and [customers].[customer_id], despite the fact
> that [invoices] et [customers] tables are in SEPARATE databases.
> Is it possible to do that ?
> Thanks in advance
> Tom
|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tom McLeesh" <tom.mcleesh@.gmail.com> wrote in message
news:u7%23a7148GHA.4572@.TK2MSFTNGP02.phx.gbl...
> Is it possible to create foreign key between to distinct SQL databases ?
> Database A contains table [customers] (primary key : customer_id)
> Database B contains table [invoices] (each invoice should be linked to a
> customer. Foreign key : customer_id)
> I would like to create an integrity constraint between
> [invoices].[customer_id] and [customers].[customer_id], despite the fact
> that [invoices] et [customers] tables are in SEPARATE databases.
> Is it possible to do that ?
> Thanks in advance
> Tom

Sunday, February 19, 2012

cross-database referential integrity?

cross-database referential integrity?

Thursday, February 16, 2012

Cross Row Referential Integrity

Tuesday, February 14, 2012

Cross database integrity

Cross database integrity

Cross database integrity

Cross database integrity

database errors

Blog Archive

About Me