0

I want establish a one-to-one relationship between two columns (a program code and a test code) in the same table. I want all tests with the same test code to have the same program code.

  1. My first thought was to use a UDF to find cases where the same test code corresponds to two different programs. I learned that this won't work because t-sql only checks UDFs in check constraints after INSERTS -- not after UPDATES why is t-sql allowing me to violate a check constraint that uses a UDP?

  2. My next thought was to move the logic from the UDF into the check constraint itself. But t-sql is saying that sub-queries are not allowed in the check constraint. This also means I can't use EXISTS syntax (which I think also uses a sub-query).

    ALTER TABLE [dbo].[mytable] WITH CHECK ADD CONSTRAINT [oneProgramPerTest] CHECK
    ( (select COUNT(*) from mydb.dbo.mytable u1 inner join mydb.dbo.mytable u2 on u1.testcode=u2.testcode and u1.progcode <> u2.progcode )=0 )

Unless there is some way to enforce this logic without (1) a udf or (2) a subquery then it seems like I need to create a "dummy" table of program codes and then enforce a one-to-one relationship between test codes from myTable and the dummy table. This seems really ugly so there has got to be a better way. Right?

Community
  • 1
  • 1
bernie2436
  • 22,841
  • 49
  • 151
  • 244

1 Answers1

2

Have you read about normalization (and if you haven't why are you designing a datbase?). You should havea structure with

tableA
id (PK)
programcode
other fields

tableB
programcode (PK)
testcode

Add a formal foreign key between the two tables and define program code as the PK in tableb. Then to get the data you want:

select <Name specific fields, never use select *>
from tableA a
join tableB b on a.programcode = b.programcode
HLGEM
  • 94,695
  • 15
  • 113
  • 186