Tag Archives: Server

Truncating MySQL tables with foreign key constrains

One cannot TRUNCATE, MySQL table that has FK constraints applied on it. TRUNCATE is not the same as DELETE. There are 2 possible workarounds for the problem. The good (which does not risk damage to data integrity) and the bad.

Option 1:

  1. Remove constraints.
  2. Perform TRUNCATE.
  3. Delete manually the rows that now have references to nowhere.
  4. Create constraints.

Option 2: Aka the BAD variant. Very handy during development.

SET FOREIGN_KEY_CHECKS = 0;
 
TRUNCATE table1;
TRUNCATE table2;
 
SET FOREIGN_KEY_CHECKS = 1;

Disabling foreign key checks may lead to a damage to the data integrity, for ex. leaving your tables with rows that do not adhere to the FOREIGN KEY constraints.

CLRF in SQL Server

An interesting problem I was facing was adding CLRF on a string in SQL Server. The solution was to add a combination of chars ( CHAR(13) + CHAR(10) ) where the string should break the line. The follwing example will add CLRF in the string being updated.

UPDATE NM196 SET 
        OSNOVANIE = 
	'Вал. превод Реф.@@500' + CHAR(13) + CHAR(10) + 
	'сума @@547 @@502 @@542' + CHAR(13) + CHAR(10) + 
	'ориг.сума @@501' + CHAR(13) + CHAR(10) + 
	'вальор @@505 ' + CHAR(13) + CHAR(10) + 
	'Наредител: @@522 ' + CHAR(13) + CHAR(10) + 
	'Бенефициент: @@526 ' + CHAR(13) + CHAR(10) + 
	'Основание: @@535 @@546'
WHERE CODE = 295 AND NOMID = 196