Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have the following three tables:

formId INTEGER       PK

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

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?

share|improve this question
Is there really a many-to-may relationship between forms and blocks? One block can appear on multiple forms? If there is a many-to-many relationship, should the orderInForm be for a particular form? Or should it apply to all forms? – Justin Cave May 8 '12 at 6:26
There is a many-to-many relationship. A block could appear on multiples forms. orderInForm is for a particular form. I will add this to my question to clarify. – VansFannel May 8 '12 at 6:30
up vote 1 down vote accepted

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.

share|improve this answer
Thanks for your answer. I don't understand one thing: you say that a unique constraint on the combination of (blockId, orderInForm), but I think that unique constraint must use three FormBlock's columns. Why do you use only two? I can use the same block in the same position with two or more forms. – VansFannel May 8 '12 at 6:53
@VansFannel: I think you are (partially) right, the unique constraint should be on table FormBlock but in the (formId, orderInForm) combination. – ypercubeᵀᴹ May 8 '12 at 7:14
@ypercube - Yes, that was my mistake. Fixed my answer. – Justin Cave May 8 '12 at 7:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.