Filter a unique constraint or similar tool

Question

I need to figure out a workaround for a design error that is too late to fix. Basically, I expect duplicate data to fall into a table that is specially structured to avoid this:

CREATE TABLE building (
    building_id INT IDENTITY(1, 1) NOT NULL,
    address_id INT NOT NULL,
    company_id INT NOT NULL,
    CONSTRAINT building_pk PRIMARY KEY (building_id),
    CONSTRAINT building_fk1 FOREIGN KEY (address_id) REFERENCES address (address_id),
    CONSTRAINT building_fk2 FOREIGN KEY (company_id) REFERENCES company (company_id)
);
CREATE INDEX building_idx1 ON building (address_id);
CREATE INDEX building_idx2 ON building (company_id);

/* Prevent dupes */
ALTER TABLE building ADD CONSTRAINT building_uk1 UNIQUE (address_id);

^{(The original localized names have been changed to make them more understandable and relevant to the question.)}

Export Buildings API at Acme Inc. will send different buildings that have the same address. Deletion building_uk1will break certain features that are uniqueness, and we cannot afford to reissue it at this time.

building_uk1 (company_id=314), , . building_uk1 , company_id 314?

Answer 1

:

CREATE UNIQUE NONCLUSTERED INDEX [IX_building_uk1] ON [dbo].[building]
(
    [address_id] ASC
)
WHERE (company_id <> 314)

Answer 2

, constriant, , .

, , build_id 314

CREATE FUNCTION CheckDistinctConstraint ()
RETURNS int
AS
BEGIN
  DECLARE @retValue int = 0
  DECLARE @cnt int = 0
  DECLARE @distcnt int = 0
  SELECT
    @cnt = COUNT(*),
    @distcnt = COUNT(DISTINCT address_id)
  FROM building
  WHERE company_id <> 314
  IF @cnt <> @distcnt
  BEGIN
    SET @retValue = 1
  END
  RETURN @retValue
END

,

ALTER TABLE dbo.Building
  ADD CONSTRAINT CheckDistConstraint CHECK (dbo.CheckDistinctConstraint() =0);

CREATE TABLE building (
    building_id INT  NOT NULL,
    address_id INT NOT NULL,
    company_id INT NOT NULL--,
    CONSTRAINT building_pk PRIMARY KEY (building_id)--,
    --CONSTRAINT building_fk1 FOREIGN KEY (address_id) REFERENCES address (address_id),
    --CONSTRAINT building_fk2 FOREIGN KEY (company_id) REFERENCES company (company_id)
);

--inserts successfully
insert into building ( building_id, address_id, company_id) values
(1,11,22)

--insert failed bcos check constraint
insert into building ( building_id, address_id, company_id) values
(5,11,315)

-- insert successfull eventhough duplicate in address_id but building_id is 314
insert into building ( building_id, address_id, company_id) values
(311,11,314)

, - ?