SQL Function Constraint for Denormalized Data

Question

How do I create a function constraint for denormalized data?

-- graduation_class table
graduation_class_id | graduation_year_id
123                         1    
456                         2

-- user table
user_id |   graduation_class_id |   graduation_year_id
555             123                     1
556             123                     3  <--- bad data
557             456                     2

A graduation class can only be assigned to a sign graduation year. The combination of the two is unique in the graduation_class table.

I want to create a function constraint on the graduation_year_id column on the user table to make sure the graduation_year_id is in sync with the proper id from the graduation_class table so that the record 556 in the user table will never happen.

** Please assume normalization is NOT a solution to this, or I wouldn't be asking this question! :) **

whether `graduation_class_id + graduation_year_id` is unique in `graduation_class` table — Pரதீப், Feb 02 '15 at 17:31
Yes, it's a unique combination in the graduation_class table. — Adam Levitt, Feb 02 '15 at 17:32
What you want to achieve is best implemented through a FK constraint — Giorgos Betsos, Feb 02 '15 at 17:33
I would populate `graduation_year_id` in the `user` table through a trigger. — David Faber, Feb 02 '15 at 17:33
I already have a FK constraint, but that doesn't enforce that it's the right constraint, only that the id exists in any row in the graduation_class table. It doesn't enforce which one. — Adam Levitt, Feb 02 '15 at 17:36
I was talking about a composite FK. See answer below by @NoDisplayName. You can place a unique contraint in `graduation_class` table if you like (instead of a PK) — Giorgos Betsos, Feb 02 '15 at 17:45

score 1 · Answer 1 · answered Feb 02 '15 at 17:38

1

Instead of function create a composite primary key and a foreign key to fix your problem

Make the graduation_class_id ,graduation_year_id in graduation_class as composite primary key

ALTER TABLE graduation_class
ADD PRIMARY KEY (graduation_class_id ,graduation_year_id)

Now add foreign key in the user table

ALTER TABLE user
ADD FOREIGN KEY (graduation_class_id ,graduation_year_id)
REFERENCES graduation_class(graduation_class_id ,graduation_year_id)

This will ensure you to add only the row added to user table is already present in graduation_class table

answered Feb 02 '15 at 17:38

Pரதீப்

91,748
19
131
172

I think `graduation_class_id` is intended to be the primary key of the `graduation_class` table, so the above might break referential integrity on that table. – David Faber Feb 02 '15 at 17:42
@DavidFaber May be.. but that is the not that right column to act as primary key in that table should be altered – Pரதீப் Feb 02 '15 at 17:44
You don't need to alter `graduation_class` PK. A unique contraint on both columns (`graduation_class_id ,graduation_year_id`) would do. – Giorgos Betsos Feb 02 '15 at 17:46
@GiorgosBetsos - If you create a `unique constraint` then it cannot be referred in `user` table – Pரதீப் Feb 02 '15 at 17:53
This is what I have in mind http://stackoverflow.com/questions/4225039/add-a-unique-constraint-of-a-sql-table-as-foreign-key-reference-to-an-another-sq – Giorgos Betsos Feb 02 '15 at 17:55
While this answer enforces referential integrity, I don't see how it prevents the incorrect value from being entered. – Dan Bracuk Feb 02 '15 at 18:35

score 1 · Accepted Answer · answered Feb 02 '15 at 17:53

As mentioned by other answers, this schema is not ideal, but if you need to maintain data integrity in this schema before having the opportunity to clean things up, a function constraint will do this for you. First, we will create a function that takes in the graduation_class_id and graduation_year_id as parameters, and returns a bit value:

create function dbo.fn_check_graduation_year_id
    (
    @graduation_class_id int,
    @graduation_year_id int
    )
    returns bit
as
    begin
        declare @return bit = 1
        if @graduation_year_id != (select top 1 graduation_year_id
                                   from graduation_class
                                   where graduation_class_id = @graduation_class_id)
            set @return = 0
        return @return
    end

This function will return true if the graduation_year_id matches the value on the graduation_class table given the graduation_class_id value. Next, we will add the constraint to your user table, making sure the result of the check function returns true:

alter table [user] with nocheck add constraint ck_graduation_year_id
    check (dbo.fn_check_graduation_year_id(graduation_class_id,graduation_year_id) = 1)

have a look at here http://sqlblog.com/blogs/tibor_karaszi/archive/2009/12/17/be-careful-with-constraints-calling-udfs.aspx — Pரதீப், Feb 02 '15 at 17:55
In this case the function takes both values being checked, so an update to either that does not match the `graduation_class` table would return an error. Not an ideal solution, but an answer to the OP non the less. — Ron Smith, Feb 02 '15 at 18:01

score 0 · Answer 3 · answered Feb 02 '15 at 17:33

0

Like this:

alter table user
drop column graduation_year_id

If you need it, select it by joining to the graduation_year table

answered Feb 02 '15 at 17:33

Dan Bracuk

20,699
4
26
43

That's the ideal solution, but right now we need it denormalized... is there a good way to get this to work, assuming we need it denormalized? – Adam Levitt Feb 02 '15 at 17:35

SQL Function Constraint for Denormalized Data

3 Answers3