SQL Server constraint Help

Question

Hello there,

I'm having problems coming up with an adequate restraint in SQL Server 2005. My problem involves the following tables:

Table PKTable
{
  pk integer primary key,
  property integer,
}

Table FKTable
{
  pk integer primary key,
  fk integer references PKTable(pk), 
}

I really want to make it impossible for a record, fk_rec, to exist when the PKTable record it references has property = 5. The first thing I tried to do was create a schema bound view with the following query and create an unique index on the UniqueCol field.

SELECT     'True' AS UniqueCol, 'uh oh' AS DiffCol
FROM         FKTable INNER JOIN
                      PKTable ON FKTable.fk = PKTable.pk
WHERE      PKTable.property = 5
UNION
SELECT     'True' AS UniqueCol, 'default' AS DiffCol

So basically, the ('True', 'default') record will always exist in the view, and when someone attempts to insert a row into FKTable and the PKTable record it references has property = 5, I get a constraint violation because the first part of the union query above will return something. That was the idea anyway. But, SQL Server 2005 doesn't allow indices on views that involve a union. I then tried to create another view:

SELECT UniqueCol, DiffCol FROM TheViewAbove

Putting a unique index on UniqueCol fails in this case because you can't have indices on a view that reference another view. I knew it was a long shot. Any way to get around this?

Thanks!

Answer 1

Two ways.

1. Create a subtype table (say it's called NonProp5s that has only the non property = 5 rows in it, then reference the pk in this subtype table from the FKTable instead of the main PKTable. This doesn't need to have anything more than the pk column in it, (Create Table NonProp5s (pk Integer Primary Key Not Null) the key is that the insert into the parent pkTable would always insert a row into NonProp5s, with the same pk value, except where the property value was equal to 5. (You will also need an Update trigger to insert/remove the row whenever the property value in PKTable changes to/from 5.)
2. Create 2 triggers. One on inserts/updates to the FK Table that rolls back the transaction if you are trying to insert a record that references a PK row with property = 5, and Second trigger on updates to PK table that rolls back transaction if trying to change property to value = 5 when there are any child records in FKTable.

Answer 2

1. Create a unique constraint on PKTable(pk,property)
2. Add column PK_property to your child table. Have a check constraint ensure that (property<>5)
3. Have a foreign key refer (pk,pk_property) to PKTable(pk,property)

Answer 3

I really didn't want to change the tables or the relationships between tables so here is what I did:

I created a table called DummyTable with more than 1 record in it. I put 2 in it. Then I built a view:

SELECT pk FROM FKTable 
    INNER JOIN PKTable ON FKTable.fk = PKTable.pk
    INNER JOIN DummyTable ON 1 = 1
WHERE PKTable.property = 5

Then I put a unique index on the pk field in the view. This way I'll get duplicates when the a row exists that violates my constraint.

Answer 4

You could also use a triggers to implement constraints, like this:

First create tables:

CREATE TABLE tPK(
ID int NOT NULL PRIMARY KEY
,[Property] int NULL
)
go

CREATE TABLE tFK(
ID int NOT NULL PRIMARY KEY
,fk int NOT NULL
)
go

ALTER TABLE tFK ADD
CONSTRAINT FK1_tFK FOREIGN KEY(fk) REFERENCES tPK(ID)
    ON UPDATE CASCADE
    ON DELETE CASCADE
go

Then add the trigger to the parent table

CREATE TRIGGER trgPK ON tPK
    AFTER INSERT, UPDATE
AS
IF UPDATE(Property) 
    BEGIN
        DELETE  FROM tFK 
        WHERE   fk IN ( SELECT  T.ID
                        FROM    tPK AS T
                        WHERE   T.[Property] = 5 )

    END
go

And on the child table:

CREATE TRIGGER trgFK ON dbo.tFK
    AFTER INSERT, UPDATE
AS
IF UPDATE(fk) 
    BEGIN
        DELETE  FROM tFK 
        WHERE   fk IN ( SELECT  T.ID
                        FROM    tPK AS T
                        WHERE   T.[Property] = 5 )

    END
GO

If you have lots of loading to do, then:

1. disable both triggers,
2. load data,
3. enable both triggers,
4. update a row in either table.