The way a student is taught to model something like this is to have the 'seed' entry refer to the 'account' entry (or, better yet, the 'game' entry). This is just how one models a one:many relationship. It is then trivially true that a given seed will only be used in one game at a time. As long as one never updates the reference on an existing seed, a seed can't be re-used in this way.
Of course, relational data models only encourage this type of design: they do not require it, nor are they required for it.
Also, this is by no means the only possible bug of this nature. For example, the seed generation might be based on the current wall-clock time. I'd hope someone trying to run a casino would know better than that, but hopes of that nature are frequently unfulfilled.
Yeah, I guess it depends on how good your constraints engine is versus what your constraints actually are. If your actual constraints can't be modeled, or if you choose not to model them at the database layer (e.g. for performance reasons) then you have to implement the constraints somewhere else.
I worked on a database that allowed (almost) every table to mark a row as deleted with a boolean-type column. This caused problems when you wanted to create a new row that had the same values for the table's key-columns as a deleted row. You can't just add the deleted column to the key and have all the functionality you want (multiple deleted rows with the same values in the table's key-columns). Each table could use (or not) the flag in different ways and there was no simple way to enforce consistent behavior across all tables. The constraint has to be placed somewhere else, either in explicit code, or by creating new abstractions inside the database that allow you to represent the actual constraints of your application.
There are benefits for marking a row as deleted w/ a boolean column though. Undo-ing deletes would be easy to implement, and retaining references to deleted records would be simple as well.
The solution the problem you described would be to use a surrogate key column (typically a UUID/auto-increment), and not natural keys.
There are reasons for everything in the database. Undo and historical records were the primary use case.
All tables had a unique, integer primary key.
However, if you want to enforce a uniqueness constraint across your data [eg. UNIQUE(name, location)], the constraint breaks when you introduce the boolean deleted column [and UNIQUE(name, location, deleted) does not provide the appropriate semantics]. The application semantics must be provided at some other level than SQL column constraints.
