October 5, 2005 at 10:36 am
Before inserting a new record into TBL_X I want to see if the two key columns for a row in TBL_X are contained within TBL_Y (a validation table that specifies allowable.
I could see how I could do this by a SPROC later but want to trap it before the table is updated.
e.g. (comma separates columns)
TBL_Y
Field1,Field2
--
A,A
A,B
C,A
TBL_X
Field1,Field2,Name1,Name2
---
A,A,Geoff,Whinston
A,B,Geoff,Charlie
D,A,Rob,Bobby <-- not allowed as D,A not contained within TBL_Y
October 5, 2005 at 10:49 am
If your schema is:
create table TBL_Y
(field1 char(1) not null
,field2 char(1) not null
, constraint TBL_Y_P primary key (field1, field2)
)
go
create table TBL_X
(field1 char(1) not null
,field2 char(1) not null
, Name1 varchar(255) not null
, Name2 varchar(255) not null
, constraint TBL_Y_F_TBL_X foreign key (field1, field2)
references TBL_Y
)
go
For your pre-check:
IF NOT EXISTS
(select 1 from TBL_Y
where field1 = @field1
and field2 = @field2
)
BEGIN
RAISERROR ('Values for field1 and field2 are not a defined valid combination',10,1)
return +1
end
SQL = Scarcely Qualifies as a Language
October 5, 2005 at 11:00 am
i presume your pre-check would be contained within a SPROC that is updating TBL_X
October 5, 2005 at 11:35 am
Why not add a composite foreign key on those columns?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply