It's a nice clear question with a nice unambiguous answer.

I'm a little bothered by the statement that on delete cascade means that if rows in several tables reference a key they will all be deleted; on delete cascade applies only to a single foreign key relationship, so can't influence the action on multiple referring tables; the foreign key relationships on other referencing tables might have SET NULL or SET DEFAULT instead of CASCADE and then the rows in those tables wouln't be deleted, or might even have NO ACTION in which case the delete wouldn't happen for any rows at all. ON CASCADE only means that referring rows in this table will be deleted if deletion of the target is successful.

It's a good idea to avoid NULL if possible, of course, but sometimes in the real world all the data isn't available when something is inserted and in that case a NULL may be needed - of course it does seem unlikely that the order of which the detail is part will be unknown, but I can easily dream up a zany ordering process in which it would almost always be unknown (and on reflection I can envisage such an ordering process as srather useful in some contexts) so I can't object to a nullable column here. Anyway, you can't insert orphan rows; you can insert rows which look like orphan rows, but they aren't orphan rows because an orphan row is one that has been orphaned - it had a parent once, but the parent has disappeared; so a reference constraint with on update cascade does prevent real orphan rows, it just doesn't prevent insertion of rows that look as if they are orphan rows.

The thing I might complain about if someone suggested actually doing it is the structured orderdetailid field; I can't tell from the table definition that the orderdetail table isn't in 1NF, but I can tell from the insertion code that it definitely is not in 1NF (and just to placate the anti-null fundamentalists, I'll point out that this remains true if NULL isn't permitted in any of the columns). And of course a consequence of that structured column being the primary key is that it can never be null, which makes it an absolute nonsense that one of the components that go to make up the structured column is allowed to be null. But none of that is what the question is about.