SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

balakadaka: Jun 30, 2005; robot terrorists WILL kill you

NihilCredo posted:

Couple of SQL Server questions:

1) Let's say we have these two tables:
code:
Users
 [UserID]
 (other columns...)

Widgets
 (other columns...)
 [CreatedBy]
 [LastEditedBy]
In this case I would want both [CreatedBy] and [LastEditedBy] to each have a foreign key relationship with Users.[UserID], with the options ON UPDATE CASCADE and either ON DELETE SET NULL or ON DELETE CASCADE, depending on the exact nature of the Widgets.

Now, SQL Server refuses to allow a second foreign key, unless both are set to ON UPDATE/DELETE NO ACTION, claiming that it might cause cycles and/or multiple cascade paths. I've read the standard Google results for this problem, but I still can't figure out what sort of cascade paths SQL Server is worried about in my scenario. Every other problematic example I've seen has either a third table involved or a different relationship diagram.

2) You're designing a new schema, and you have a whole bunch of tables that you want to set up. There's a lot of things you want to have on 95% of your tables, such as:

- a [<Table name>ID] primary key and index
- [Creation Date] and [Last Updated] columns
- triggers that autofill those two columns
- a [Status] column (e.g. 0 = new, 1 = active, 2 = deleted, or whatever)
- potentially multiple [OwnerID], [SourceID], etc. FK columns
- either a View or (in 2016) Row Level Security that only shows a user his own rows
- etc.

Now, this is a very infrequent task so copy/paste works perfectly. However, is there any feature in (T-)SQL that you're supposed to use in such a scenario?

3) Are there any guidelines for when a nchar property becomes "small enough" that you can use it as its own ID column all over your database? For an extreme example, currency is likely represented by just three ASCII characters, which aren't going to change. If you're not going to store exchange rates or other information, is it going to be faster to read/write EUR everywhere than to use a numeric CurrencyID foreign key? Or is relational storage so optimised at this point that you'd still rather join on a tinyint?

1) Think of it as SQL Server just trying to minimize the amount of deadlocks on a possible resource. Say you had both foreign keys on Widgets tying back to Users - if a Widget was created and updated by the same user, then each foreign key fires when that user is deleted. You'd have plenty of deadlocks if that user created & updated more than 1 widget. Even if cascade was just on 1 foreign key, the constraint will still check the Widgets table for a reference, but delete and update will request an exclusive lock, and most likely horrid performance. Not sure of your exact structure, but would a on update/ on delete trigger referencing a stored proc in Users work for you? (I'd say have the proc update/delete from Widgets where UserID = CreatedBy or UserID = UpdatedBy)

2) Outside of saving a table called "base_template" in model, but otherwise no magic bullet I've encountered. I've seen a truckload of dynamic SQL procs build a create table statement out of a dictionary, but it's a mess to read and debug.

3) I've seen plenty of cases where a currency was normalized into it's own table - it's just good design and saves a lot in storage. I'd say the best guidelines I see my colleagues following is this: is the character data static/controlled change or highly dynamic? If static, go for normalizing into a table. For example, if you have a column for a custom MSMQ message type in a .Net app and you have 10,000 different types, it'd still be worth it. Just about any flavor of SQL will chew through 100k rows with ease outside of BLOBs. But if you're tracking a few million different varchar values and your table isn't too big (under 100 GB), I wouldn't sweat it too much.

# ¿ Jun 4, 2015 03:19

Adbot: ADBOT LOVES YOU

# ¿ Apr 29, 2024 02:37

balakadaka: Jun 30, 2005; robot terrorists WILL kill you

NihilCredo posted:

Thanks a lot for the answers!

Regarding (1): I think I get it, mostly. However, updating / deleting a UserId is going to be an extremely infrequent operation; shouldn't I be able to tell SQL Server "yeah I don't care if this is going to be hilariously expensive, go ahead anyway" somehow?

I'm not sure if a trigger on Users works here. Either I don't set a FK constraint - in which case you could write a Widget with invalid user-related columns - or I set it to ON CASCADE NO ACTION, in which case, correct me if I'm wrong, I won't be able to update / delete Users at all and the trigger won't fire. I could work with the latter but it seems unnecessary busywork.

Sorry, I misunderstood what you were trying to do when I mentioned the trigger on Users. After I thought about it more, it seems like you're trying to update/delete the user from Users when a widget is updated or deleted? At least, that's what CASCADE would do on a foreign key on a Widgets column referencing Users. Outside of that, I'd suggest maybe doing a stored proc to control the delete when you want to delete the user ID - set all of the references to the User ID to null, then toss the row from Users.

Regarding telling SQL to ignore the cascade problem, I tried it out and had the same error. When I read that more, it seems like that protection is built in to prevent a big screw up by the relational engine and the algebrizer? I'm guessing that when it translates the commands into code to execute on the tables, something must be very weird when you get multiple cascade patgs

# ¿ Jun 5, 2015 14:35

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'