June 4, 2007 at 3:38 pm
The subject line says it all; I want to know if I can use a column from a view in a foreign key relationship in a table? You see, I want to define a table like this:
CREATE TABLE [dbo].[ClinicalRSSvoucherMatch](
[ClinicalTreatmentType] [smallint] NOT NULL,
[RSSTreatmentType] [smallint] NOT NULL,
[OutcomesTreatmentType] [smallint] NULL,
CONSTRAINT [PK_ClinicalRSSvoucherMatch] PRIMARY KEY CLUSTERED
(
[ClinicalTreatmentType] ASC,
[RSSTreatmentType] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
and the 3 columns ClinicalTreatmentType, RSSTreatmentType and OutcomesTreatmentType, all come from the same column in another table. However, they have different meanins depending upon a second column in that other table. That second column means that if it is a 1, then any value for the first column has to be considered a clinical treatment type column, and so on. I originally thought I could set up foreign key relationships if I were to create 3 views of the original table filtering it by the second column so that this view could be used to enforce what values could go into each of the 3 columns of the ClinicalRSSVoucherMatch table. However, it appears now to me that this won't work. So, how do I do it, please?
Kindest Regards, Rod Connect with me on LinkedIn.
June 5, 2007 at 1:42 am
your problem is that you are overloading one column to mean different things depending on the value in another column. this is a bad design.
---------------------------------------
elsasoft.org
June 6, 2007 at 10:51 am
you could do an insert,update trigger that fails if your check from the other table is not valid.
A check constraint could also potentially be used.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply