# Foreign key with a constant....

• ### 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

• 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

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

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

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

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

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

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