Geek asks and answers

Complex external key constraint in SQL

Is there a way to define a constraint using SQL Server 2005 to not only ensure that a foreign key exists in another table, but also meets certain criteria?

For example, let's say I have two tables:

Table A
--------
Id - int
FK_BId - int

Table B
--------
Id - int
Name - string
SomeBoolean - bit

Can I define a constraint that sayd FK_BId should point to an entry in table B and that the entry in table B should have the value SomeBoolean = true? Thanks in advance for any help you can provide.

+3
source share
3 answers

-, (Id, SomeBoolean), A CHECK FK_BSomeBoolean, , . BTW BIT CHAR(1) , .

CHECK (SomeBoolean IN ('F', 'T'))

:

CREATE TABLE B
(
 Id INTEGER NOT NULL UNIQUE, -- candidate key 1
 Name VARCHAR(20) NOT NULL UNIQUE,  -- candidate key 2
 SomeBoolean CHAR(1) DEFAULT 'F' NOT NULL
    CHECK (SomeBoolean IN ('F', 'T')), 
 UNIQUE (Id, SomeBoolean) -- superkey
); 

CREATE TABLE A 
(
 Ib INTEGER NOT NULL UNIQUE, 
 FK_BId CHAR(1) NOT NULL, 
 FK_BSomeBoolean CHAR(1) DEFAULT 'T' NOT NULL
    CHECK (FK_BSomeBoolean = 'T')
 FOREIGN KEY (FK_BId, FK_BSomeBoolean)
    REFERENCES B (Id, SomeBoolean)
);
+4

, , , , , .

, , , ID SomeBoolean, , , .

+2

( SQL Server) , .

ALTER TABLE a ADD CONSTRAINT fancy_fk
CHECK (FK_BId IN (SELECT Id FROM b WHERE SomeBoolean));

, .

-1

More articles:


All Articles