SQL Server constraint on referenced columns

Question

I have a table that looks something like this:

CREATE TABLE A (
  A_UNIQUE_ID int NOT NULL PRIMARY KEY,
  A_OTHER_ID int NOT NULL,
  A_CURRENT_FL bit NOT NULL )

and it has values similar to this

INSERT INTO TABLE A VALUES (1, 1, 0);
INSERT INTO TABLE A VALUES (2, 1, 0);
INSERT INTO TABLE A VALUES (3, 1, 1);

I want to create another table B like this

CREATE TABLE B (
  B_UNIQUE_ID int NOT NULL PRIMARY KEY,
  A_OTHER_ID int NOT NULL)

where B.A_OTHER_ID is constrained to a unique row in A. So B.A_OTHER_ID = A.A_OTHER_ID AND A.CURRENT_FL = 1. Is that possible with some type of check constraint setup? Examples are very much appreciated.

What do you mean by a "unique row in A"? You want `B.A_OTHER_ID` to be unique AND reference `A.A_OTHER_ID`? Or `A.A_OTHER_ID` allows duplicate values, and you don't want `B.A_OTHER_ID` to have more duplicates than are in the `A` table itself? Can you provide some good, unambiguous examples of what should be valid according to this constraint, and what should be invalid? — mellamokb, Aug 30 '12 at 22:40
A row in table A is essentially unique for any value of A.A_OTHER_ID when it has the A_CURRENT_FL set to 1, as there can only be one current record for each A.A_OTHER_ID. All other rows with the same A.A_OTHER_ID will have a value of 0 in the A_CURRENT_FL. I want TABLE B to be able to refer to the row that has A_CURRENT_FL set to 1, similar to a foreign key. The problem I have is that to be unique I need to combine A.A_OTHER_ID and an A.A_CURRENT_FL = 1, and I only want to refer to the distinct value of A.A_OTHER_ID in TABLE B. I hope that helps. — scottrudy, Aug 31 '12 at 01:28

score 0 · Answer 1 · edited May 23 '17 at 12:12

OK to start off you need a constraint on Table A to make it unique as you have described above. Using this answer you could create a function and call that in your new check constraint. eg:

CREATE FUNCTION Check_A_CURRENT_FL_Count(@Id INT) RETURNS INT AS 
BEGIN
  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM A WHERE A_OTHER_ID = @Id AND A_CURRENT_FL = 1;
  RETURN @ret;
END
GO

alter table A add constraint [CK_A] CHECK (NOT (dbo.Check_A_CURRENT_FL_Count(A_OTHER_ID) > 1 AND A_CURRENT_FL = 1))
GO

Then if I'm understanding what you want correctly - you can use the same function in a check constraint on Table B. Not entirely sure what the purpose of this would be, although obviously you are using a contrived example. If you wanted a list of unique IDs from Table A - you could use A_OTHER_ID as the primary key in Table B, and get rid of B_UNIQUE_ID?

Damien_The_Unbeliever · Accepted Answer · 2012-09-07T07:54:47.567

You can do it with some computed columns, but it doesn't look very pretty:

CREATE TABLE A (
  A_UNIQUE_ID int NOT NULL PRIMARY KEY,
  A_OTHER_ID int NOT NULL,
  A_CURRENT_FL bit NOT NULL,
  OTHER_XREF as CASE WHEN A_CURRENT_FL = 1 THEN A_OTHER_ID END persisted,
  UNIQ as CASE WHEN A_CURRENT_FL = 0 THEN A_UNIQUE_ID ELSE 0 END persisted 
  constraint UQ_OTHER_XREF UNIQUE (OTHER_XREF,UNIQ)
  )

INSERT INTO A VALUES (1, 1, 0);
INSERT INTO A VALUES (2, 1, 0);
INSERT INTO A VALUES (3, 1, 1);

CREATE TABLE B (
  B_UNIQUE_ID int NOT NULL PRIMARY KEY,
  A_OTHER_ID int NOT NULL,
  XREF as 0 persisted,
  constraint FK_B_A FOREIGN KEY (A_OTHER_ID,XREF) references A (OTHER_XREF,UNIQ))

This works:

  insert into B VALUES (2,1)

This breaks the FK constraint:

  insert into B VALUES (3,2)

The two computed columns in table A ensure that any row with A_CURRENT_FL = 1 has that as a distinct value in OTHER_XREF. Other rows will have NULL in that column. However, to apply a UNIQUE constraint (the target for the foreign key), we need something that will be distinctly different for every row with a NULL, whilst being a well-known value for the row with A_CURRENT_FL = 1. In this case, I made the well known value 0.

Finally, the foreign key constraint needs to match both of these columns, so we add a new computed column in table B with the well known value 0.

Column names aren't particularly good, but I'm sure you can come up with some.

Note, also, that by the above, we've also implemented another constraint that you didn't mention, but that also may be important - there can't be two rows now in table A that both have A_CURRENT_FL set to 1, for the same A_OTHER_ID value. This also generates an error:

INSERT INTO A VALUES (4,1,1);

SQL Server constraint on referenced columns

2 Answers2