Database design problem: where to put UNIQUE constraint

Question

I have the following three tables:

Form
-----------
formId INTEGER       PK
name   NVARCHAR(50)  NOT NULL

Block
-----------
blockId     INTEGER        PK
name        NVARCHAR(30)   NOT NULL
orderInForm INTEGER        NOT NULL

FormBlock
-----------
blockId  INTEGER  PK
formId   INTEGER  PK

There is a many-to-many relationship. A block could appear on multiples forms. orderInForm is for a particular form.

On Block table I want to avoid that two blocks have the same orderInForm in a Form.

I think I have to add an UNIQUE constraint, but if orderInForm is on Block table, I don't know how to do it.

May I need to move orderInForm to FormBlock table?

Answer 1

Since orderInForm is the position of a particular block on a particular form, it should be a column in the FormBlock table, not a column in the Block table. If orderInForm is in the Block table, a block would need to have the same position in every form it was on.

Once you move the column to FormBlock, you can then create a unique constraint on the combination of (form_id,orderInForm) on the FormBlock table.