Take the 2-minute tour ×
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.

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?

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.