locked
Foreign key with a constant.... RRS feed

  • Question

  • Chaps,

    Here is a question for you. Is it possible to setup a relationship between two tables using a consant value? Here is my problem:

    Table A

    prefix code
    AA 1
    AA 2
    AA 3
    AB 1
    AB 2
    AB 3

    Table B

    test1 test2 test3 test4
    Hello1 1 1 bob
    Hello2 1 3 bob
    Hello3 2 1 bob

    So what i need to do is setup a relationhip between column test2 from B to code in A but only to allow values from prefix AA. and then setup a relationship between column test3 from B and code from A but only for values from prefix AB.

    Now i've cludged this with a check constraint but i was hoping to use a foreign key, but since A is a two part key i would need to store the prefix in B.

    Any ideas?

    Thanks,

    Cedric

     

    Wednesday, September 22, 2010 7:54 AM

Answers

  • Hi Cedric,

     

    According to your description, we can use User-Defined Functions (UDF) and check constraint to achieve it, something like this:

     

    CREATE TABLE TableA (A1 VARCHAR(20), A2 INT)

    CREATE TABLE TableB (B1 VARCHAR(20), B2 INT, B3 INT, B4 VARCHAR(20))

    GO

    --Add two UDF

    CREATE FUNCTION dbo.f_TableB_fk1

    (@id INT)

    RETURNS INT

    AS

    BEGIN

      RETURN

        CASE

          WHEN ((SELECT COUNT(B.B2) FROM TableB B LEFT JOIN TableA A ON B.B2=A.A2 WHERE A.A1<>'AA' AND B.B2=@id)>=1

              OR (SELECT COUNT(B2) FROM TableB WHERE B2=@id AND B2 NOT IN (SELECT A2 FROM TableA))>=1) THEN 1

        ELSE 0

        END

    END

    GO

    --UDF2

    CREATE FUNCTION dbo.f_TableB_fk2

    (@id INT)

    RETURNS INT

    AS

    BEGIN

      RETURN

        CASE

          WHEN ((SELECT COUNT(B.B3) FROM TableB B LEFT JOIN TableA A ON B.B3=A.A2 WHERE A.A1<>'AB' AND B.B3=@id)>=1

              OR (SELECT COUNT(B3) FROM TableB WHERE B3=@id AND B3 NOT IN (SELECT A2 FROM TableA))>=1) THEN 1

        ELSE 0

        END

    END

    GO

    --Add two constraints

    ALTER TABLE TableB ADD CONSTRAINT CK_TableB_B2_1 CHECK(dbo.f_TableB_fk1(B2)=0)

    GO

    ALTER TABLE TableB ADD CONSTRAINT CK_TableB_B2_2 CHECK(dbo.f_TableB_fk2(B3)=0)

    GO

    INSERT INTO TableA VALUES('AA',1)

    INSERT INTO TableA VALUES('AA',2)

    INSERT INTO TableA VALUES('AB',3)

    INSERT INTO TableA VALUES('AB',4)

    GO

    INSERT INTO TableB VALUES('HELLO1',1,3,'hello1')

    INSERT INTO TableB VALUES('HELLO2',1,4,'hello2')

    INSERT INTO TableB VALUES('HELLO3',2,4,'hello3')

    INSERT INTO TableB VALUES('HELLO4',2,3,'hello4')

    GO

    --Do a test

    INSERT INTO TableB VALUES('HELLO5',3,3,'hello5')

    INSERT INTO TableB VALUES('HELLO6',5,3,'hello6')

    INSERT INTO TableB VALUES('HELLO7',1,1,'hello7')

    INSERT INTO TableB VALUES('HELLO8',1,5,'hello8')

    GO

     

    For more information, please visit the following link:

    CREATE FUNCTION (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms186755.aspx

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    Thursday, September 23, 2010 12:25 PM

All replies

  • Try this:


    CREATE TABLE B
     (test1 VARCHAR(10) NOT NULL,
      prefix2 CHAR(2) NOT NULL DEFAULT ('AA') CHECK (prefix2='AA'),
      test2 TINYINT NOT NULL,
      prefix3 CHAR(2) NOT NULL DEFAULT ('AB') CHECK (prefix3='AB'),
      test3 TINYINT NOT NULL,
      test4 VARCHAR(10) NOT NULL,
      FOREIGN KEY (prefix2, test2) REFERENCES A (prefix, code),
      FOREIGN KEY (prefix3, test3) REFERENCES A (prefix, code),
      /* PRIMARY KEY ?? */); 

    Wednesday, September 22, 2010 9:01 AM
  • David,

    Thanks for this. what i was hoping for is to not have to have a column for the prefix. It's a shame that i can't specify a fixed value in the relationship condition.

     

    Cedric

    Wednesday, September 22, 2010 9:31 AM
  • Hi Cedric,

     

    According to your description, we can use User-Defined Functions (UDF) and check constraint to achieve it, something like this:

     

    CREATE TABLE TableA (A1 VARCHAR(20), A2 INT)

    CREATE TABLE TableB (B1 VARCHAR(20), B2 INT, B3 INT, B4 VARCHAR(20))

    GO

    --Add two UDF

    CREATE FUNCTION dbo.f_TableB_fk1

    (@id INT)

    RETURNS INT

    AS

    BEGIN

      RETURN

        CASE

          WHEN ((SELECT COUNT(B.B2) FROM TableB B LEFT JOIN TableA A ON B.B2=A.A2 WHERE A.A1<>'AA' AND B.B2=@id)>=1

              OR (SELECT COUNT(B2) FROM TableB WHERE B2=@id AND B2 NOT IN (SELECT A2 FROM TableA))>=1) THEN 1

        ELSE 0

        END

    END

    GO

    --UDF2

    CREATE FUNCTION dbo.f_TableB_fk2

    (@id INT)

    RETURNS INT

    AS

    BEGIN

      RETURN

        CASE

          WHEN ((SELECT COUNT(B.B3) FROM TableB B LEFT JOIN TableA A ON B.B3=A.A2 WHERE A.A1<>'AB' AND B.B3=@id)>=1

              OR (SELECT COUNT(B3) FROM TableB WHERE B3=@id AND B3 NOT IN (SELECT A2 FROM TableA))>=1) THEN 1

        ELSE 0

        END

    END

    GO

    --Add two constraints

    ALTER TABLE TableB ADD CONSTRAINT CK_TableB_B2_1 CHECK(dbo.f_TableB_fk1(B2)=0)

    GO

    ALTER TABLE TableB ADD CONSTRAINT CK_TableB_B2_2 CHECK(dbo.f_TableB_fk2(B3)=0)

    GO

    INSERT INTO TableA VALUES('AA',1)

    INSERT INTO TableA VALUES('AA',2)

    INSERT INTO TableA VALUES('AB',3)

    INSERT INTO TableA VALUES('AB',4)

    GO

    INSERT INTO TableB VALUES('HELLO1',1,3,'hello1')

    INSERT INTO TableB VALUES('HELLO2',1,4,'hello2')

    INSERT INTO TableB VALUES('HELLO3',2,4,'hello3')

    INSERT INTO TableB VALUES('HELLO4',2,3,'hello4')

    GO

    --Do a test

    INSERT INTO TableB VALUES('HELLO5',3,3,'hello5')

    INSERT INTO TableB VALUES('HELLO6',5,3,'hello6')

    INSERT INTO TableB VALUES('HELLO7',1,1,'hello7')

    INSERT INTO TableB VALUES('HELLO8',1,5,'hello8')

    GO

     

    For more information, please visit the following link:

    CREATE FUNCTION (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms186755.aspx

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    Thursday, September 23, 2010 12:25 PM