July 2, 2003 at 5:09 am
I have 2 tables - 1 for People [PersonDetails] and one for Organisations [CRMTestOrg]. I also have a table for Addresses [CRMAddress] which I want to use to store address information for both People and Organisations. I have used a relationship based on both the ID and the name of the table. However I am unable to enter any details because it will always fail one relationship. Is there a way of doing this so SQL will check both relationships and if one works then it will allow entry/deletiion. I have included a simplified script for the tables and relationships if someone has the time/inclination to have a look. The problem arises as soon as any address info is entered.
SCRIPT:
CREATE TABLE [dbo].[crmTestORg] (
[orid] [int] IDENTITY (1, 1) NOT NULL ,
[orName] [char] (10) NOT NULL ,
[ownerTable] [char] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CRMAddress] (
[AdID] [int] IDENTITY (1, 1) NOT NULL ,
[adName] [char] (20) NOT NULL ,
[adOwnerTable] [char] (10) NOT NULL ,
[adOwnerID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PersonDetails] (
[prPersonID] [int] IDENTITY (1, 1) NOT NULL ,
[prName] [char] (10) NOT NULL ,
[prOwnerTable] [char] (10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[crmTestORg] WITH NOCHECK ADD
CONSTRAINT [DF_crmTestORg_owner] DEFAULT ('Org') FOR [ownerTable],
CONSTRAINT [PK_crmTestORg] PRIMARY KEY NONCLUSTERED
(
[orid],
[ownerTable]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CRMAddress] WITH NOCHECK ADD
CONSTRAINT [PK_CRMAddress] PRIMARY KEY NONCLUSTERED
(
[AdID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PersonDetails] WITH NOCHECK ADD
CONSTRAINT [DF_PersonDetails_prOwnerTable] DEFAULT ('Person') FOR [prOwnerTable],
CONSTRAINT [PK_PersonDetails] PRIMARY KEY NONCLUSTERED
(
[prPersonID],
[prOwnerTable]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_CRMAddressPerson] ON [dbo].[CRMAddress]([adOwnerTable], [adOwnerID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CRMAddress] ADD
CONSTRAINT [FK_CRMAddress_crmTestORg] FOREIGN KEY
(
[adOwnerID],
[adOwnerTable]
) REFERENCES [dbo].[crmTestORg] (
[orid],
[ownerTable]
),
CONSTRAINT [FK_CRMAddress_PersonDetails] FOREIGN KEY
(
[adOwnerID],
[adOwnerTable]
) REFERENCES [dbo].[PersonDetails] (
[prPersonID],
[prOwnerTable]
)
GO
July 2, 2003 at 6:18 am
Double post, see
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply