6

Well, after continue searching I found this article Unlike a normal query, it won't pick up a new index due to statistics being updated, a new index being created, or even a server being rebooted. The only way I'm aware of to have a FK bind to a different index is to drop and recreate the FK, letting it automatically select the index with no options to ...


3

It's possible to change the data type without data loss or breaking FK dependencies. It depends on whether you have some downtime where you can execute the required change though. Changing from datetime to datetime2(7) will require every row in the table / indexes to be updated, since these two types are stored differently on disk. This can take a little ...


2

After reading MS DOCS here. To modify a foreign key To modify a FOREIGN KEY constraint by using Transact-SQL, you must first delete the existing FOREIGN KEY constraint and then re-create it with the new definition. For more information, see Delete Foreign Key Relationships and Create Foreign Key Relationships. In you case I believe add a new ...


2

You cannot have that directly, but you can do the following: Have two foreign key columns driverid and passengerid in wallet that reference the respective tables. Add a check constraint that force one of the foreign key columns NULL and the other NOT NULL based on usertype. CHECK (usertype = 'driver' AND driverid IS NOT NULL AND passengerid IS NULL ...


2

The information required to generate the queries and the counts is all available in the catalog views, like sys.columns and sys.foreign_key_columns. We need to find all of the child tables, and then count how many rows in each child table meet the same criteria as the parent ID. CREATE PROCEDURE dbo.GeneratedPathedDeletes @ParentTable nvarchar(512), ...


2

I'm not aware of such tool, but you can easily create one your self (foreign-keys-in-a-sql-server-database): SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], C.TABLE_SCHEMA [PKTABLE_OWNER], C.TABLE_NAME [PKTABLE_NAME], KCU.COLUMN_NAME [PKCOLUMN_NAME], C2.TABLE_CATALOG [FKTABLE_QUALIFIER], C2.TABLE_SCHEMA [FKTABLE_OWNER], ...


1

You add a (redundant) unique constraint on (study_id, id) on both the treatment and visit tables. Then let the foreign keys from session refer to these unique constraints by including study_id into the respective foreign keys. Then your condition will automatically be satisfied. The unique constraints are not quite as superfluous as they seem, because the ...


1

An alternative is to add a "supertype" of Driver and Passenger. In lack of a better name, I'll call it car_resident: CREATE TABLE car_residents ( resident_id ... not null primary key , resident_type char(1) not null , check (resident_type in ('P','D')) , unique (resident_type, resident_id) , ... ); CREATE TABLE Passenger ( PassengerID ... NOT NULL ...


1

I don't have enough points to comment, so an answer. OMG. I feel your pain ;-) Yeah, people find foreign key constraints a real pain. But without them, you have to then audit the data for widows and orphans, otherwise you're entirely likely to have bad summary results. At the very least, you don't really know the validity of your results. Are they off by 0....


Only top voted, non community-wiki answers of a minimum length are eligible