StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I think that you'll have to loosen up your constraints to get this to work. Like you said, even if NHibernate updated the index instead of the categoryId, "there could be issue if, say, there were also a UNIQUE constraint on EntryID and Index". This is what I would suggest:</p> <pre><code>-- Looser constraints. This should work. CREATE TABLE EntryCategory ( id int IDENTITY PRIMARY KEY, categoryId int REFERENCES Category (id), entryId int REFERENCES Entry (id), index int ); CREATE INDEX IX_EntryCategory_category ON EntryCategory (entryId, categoryId); CREATE INDEX IX_EntryCategory_index ON EntryCategory (entryId, index); </code></pre> <p>I mean, ideally you would have something like the following - but it would be nearly <em>impossible</em> to run any UPDATE queries on it:</p> <pre><code>-- Nice tight constraints - but they are TOO tight. CREATE TABLE EntryCategory ( categoryId int REFERENCES Category (id), entryId int REFERENCES Entry (id), index int, PRIMARY KEY (entryId, categoryId), UNIQUE KEY (entryId, index) ); </code></pre> <p>Whenever I'm stuck in a situation like this where what I <em>really</em> want is a unique key but for some reason I'm forced to do without it, I settle for an index instead.</p> <hr /> <p>In case it's helpful, Ayende has <a href="http://ayende.com/Blog/archive/2009/06/02/nhibernate-mapping-ltlistgt.aspx" rel="nofollow noreferrer">an example with a very similar mapping</a> - a many-to-many list. He doesn't talk about the database schema, though.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload