THE CURIOUS CASE OF THE FOREIGN KEY About

THE CURIOUS CASE OF THE FOREIGN KEY

About me Lisa Bohm The Twitter: @Lisa. GB_SQL The email: lgbohm@gmail. com Started: Coldfusion Developer Now: DB architect/t. SQL dev Ed

These keys that you speak of… • Technically a data constraint • Two types • Primary Key • Foreign Key • Why have them? • Help define relationships! • Replication…

Primary Keys ØUnique ØCan be multi-column ØAll columns specified not nullable ØDatatype limits (must support binary ordering) ØIndex…. ØClustered? (beyond scope of this presentation)

The great debate… Natural or Surrogate? • Natural key • Ever change? • Surrogate key • Translation? Ordering of data on pages…. • (IF this is also the clustered index. Indexing is beyond the scope of this presentation. This will have some effect on the NCL if your PK is NOT the clustered index. )

Non-incrementing Primary Key (like GUID) If not, page splits can be a problem: Record ACK Record AAX Record AAZ Record ABC Record ABM Record ABY Record ACE Record ACO Record ACW Record ADA Record ADJ Record AAX Record AAZ Record ABC Record ABM Record ABY Record ACE Record ACK Record ACO Record ACW Record ADA Record ADJ

Incrementing Primary Key (like IDENTITY) • Contention on the end? Everyone writing to same data page… Record 128 Record 129 Record 130 Record 131 Record 123 Record 124 Record 125 Record 126 Record 127

Foreign Keys • Data “reference” • Primary Key • - OR – unique index • Data integrity (Sales order header, sales order details) • Easily find column references PKID VALUE 1 2 3 4 Some Data For Thing tbl. ID PKID VALUE 1 2 3 4 1 1 1 2 Addtl Info Detail Other

Yo – Data Type Matters • Set up a table with the following columns: pfkey. for. Primary. Key intcol int identity , dt 2 col datetime 2 , varchar 20 Col varchar(20) , varchar 2000 Col varchar(2000) , someint. ID int , guid. Col uniqueidentifier “Primary Key” table – inserted 1 million rows “Foreign Key” table – copied the above table structure, added 10 million rows copied from “Primary Key” table Not the same number of rows per primary key

Four different queries • Query Options: • Select all • Select count • Select a date range (3 weeks) • Select data by 6 different someint. ID values (think orders by customer. ID)

Primary/Foreign Key types tested • No primary/foreign key • int • datetime 2 • GUID • varchar(20) • varchar(2000) (note – no values more than 1700 chars) • int + datetime 2 • int + GUID • int + varchar(20) • int + varchar(2000) • int + datetime 2 + GUID • int + datetime 2 + varchar(20) • int + datetime 2 + varchar(2000) (Please note: clustered index always on int column. PK/FK were nonclustered indexes)

PK/FK Data Types t+ in + dt 2 t+ in ) 00 0 0) id 0) gu r(2 ha + ha va rc dt 2 rc va + t+ (2 00 ha r (2 0) gu id da te ha r t+ rc va in t+ in 0) 00 (2 0) gu id ha r r(2 ha rc va t in pk dt 2 no Query time in ms So…. What did we find? 250000 200000 150000 100000 all count 50000 date range id values 0

PK/FK Data Types t+ in dt 2 + 0) id gu r(2 ha rc va + (2 0) gu id ha r dt 2 rc va t+ in da te (2 0) gu id ha r t+ in rc va t in pk dt 2 no Query time in ms Closer look at some details 25000 20000 15000 10000 count date range 5000 id values 0

Interesting findings • Expected: • Keys including varchars (the bigger, the worse-er) performed the most poorly • Keys including the datetime 2 column performed quite well on the date range query • “Overall”, the int key performed “the best” • Not as expected: • Keys including the GUID performed well on the “in 6 some. Int. ID” queries • Suspect: GUIDs related to these IDs were “near” each other in the nonclustered index • Me <> Bob Ward. No hex editor magic explanation here.

Please note: • Cleared plan cache between each set • Did NOT run multiple iterations of each query • Static data set – all rows inserted at once • Not like a “normal” database • No page splits/index fragmentation

Which to use? Well, “it depends”… • Remember: • primary key <> (maybe) clustered index • Primary Key = index • If multiple column key: • any table referring back NEEDS ALL COLUMNS • Also use the column in same way… • Yeah, that varchar thing. Please don’t. • Don’t use GUIDs, in spite of these results

Foreign Key Settings • Restraints on Updates/Deletes • Cascading referential integrity • No action = error • Cascade = corresponding rows updated/deleted • Set Null = values in foreign key set to null (yes, even with update) • Set Default = values in foreign key set to default value (yes…) • Note: default has to be: • a valid primary key value • That exists in pk table

DEMO TIME!

Some Concerns • Triggers… • Cascading • Caused by original update/delete • After triggers – after all cascading actions • If multiple triggers on single table, fire in random order • Multiple cascading chains: order in which chains fire triggers unspecified • But – an entire chain fires before the next starts • … and so forth

Any other gotchas? So, one day I was walking along and…. . Customer-panic-performance-tanking-hands-waving Wide. World. Importers: DELETE FROM Sales. Order. Lines WHERE Order. ID = 59575;

DEMO TIME!

What’s going on? Sales. Orderlines Foreign key constraint with on Delete Cascade (No rows) Sales. Promo. Discount Sales. Invoice. Lines Foreign key constraint with on Delete Cascade (All foreign key field values NULL)

What’s going on, work-around flavor Sales. Orderlines Foreign key constraint NO Delete Cascade (No rows) Sales. Promo. Discount Sales. Invoice. Lines Foreign key constraint with on Delete Cascade (All foreign key field values NULL)

Take-home Message(s) • Use foreign keys (correctly). Be aware of: • data types • Referential integrity options • If unexpected tables in stats IO or execution plan • Look to foreign keys as a possibility

Further Experimentation • What happens if there’s records in the “closet” table? • What happens if there’s a default value in the Invoice. Lines table linked field instead of NULL?

Additional Resources • Primary Key Primer for SQL Server • • https: //www. red-gate. com/simple-talk/sql/learn-sql-server/primary-keyprimer-for-sql-server/ Primary and Foreign Key Constraints https: //docs. microsoft. com/en-us/sql/relationaldatabases/tables/primary-and-foreign-key-constraints? view=sqlserver-2017 Questions about Primary and Foreign Keys You Were Too Shy to Ask https: //www. red-gate. com/simple-talk/sql/t-sql-programming/questions -about-primary-and-foreign-keys-you-were-too-shy-to-ask/ Identity Column in Concurrency, Multithreading, Parallel Processing https: //social. msdn. microsoft. com/Forums/sqlserver/en-US/18 bc 8 fa 74 d 36 -4 a 68 -a 850 -33217 d 08 aad 4/identity-column-in-concurrencymultithreading-parallel-processing? forum=sqldatabaseengine SQL Server Foreign Key Update and Delete Rules https: //koukia. ca/sql-server-foreign-key-update-and-delete-rules 556 cf 09117 fe