Answers.Body: <p>The table you are looking for is <code>civicrm_uf_match</code> with the following structure</p>
  
  <pre><code>CREATE TABLE `civicrm_uf_match` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'System generated ID.',
    `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this match entry for',
    `uf_id` int(10) unsigned NOT NULL COMMENT 'UF ID',
    `uf_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'UF Name',
    `contact_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Contact ID',
    `language` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'UI language preferred by the given user/contact',
    PRIMARY KEY (`id`),
    UNIQUE KEY `UI_uf_name_domain_id` (`uf_name`,`domain_id`),
    UNIQUE KEY `UI_contact_domain_id` (`contact_id`,`domain_id`),
    KEY `I_civicrm_uf_match_uf_id` (`uf_id`),
    KEY `FK_civicrm_uf_match_domain_id` (`domain_id`),
    CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
    CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`)  REFERENCES `civicrm_domain` (`id`)
   )
  </code></pre>
  
  <p>So the SQL statement to delete every link except the admin user is:</p>
  
  <pre><code>delete from civicrm_uf_match
  where uf_id !=1
  </code></pre>
  
  <p>The batch found at <em>Administer -> Users and Permissions -> Synchronize Users to Contacts</em> brings them back. But better try this first on a test instance.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-25T19:37:47.543Z
Answers.LastActivityDate: 2017-01-25T19:37:47.543Z
Answers.UserDisplayName: Kainuk - CiviCooP
Body: <p>1) In what table/field of the CiviCRM database is the Drupal User ID stored?</p>
  
  <p>2) What is the SQL statement to delete all rows in that table except User1?</p>
  
  <p>Due to the way we imported contacts and generated Drupal users, all our contact/user links are mismatched (on a development site). And I just can't get phpMyAdmin working on this server, so need to do it via mysql CLI.</p>
  
Tags: <drupal><mysql>
Title: SQL to unlink contacts and drupal accounts
ThreadText: # SQL to unlink contacts and drupal accounts
  
  (Asked by: username_0 on 2017-01-25)
  
  1) In what table/field of the CiviCRM database is the Drupal User ID stored?
  
  2) What is the SQL statement to delete all rows in that table except User1?
  
  Due to the way we imported contacts and generated Drupal users, all our contact/user links are mismatched (on a development site). And I just can't get phpMyAdmin working on this server, so need to do it via mysql CLI.
  
  **Tags:** drupal, mysql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-01-25. Score: 3
  
  The table you are looking for is civicrm_uf_match with the following structure
  CREATE TABLE `civicrm_uf_match` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'System generated ID.',
    `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this match entry for',
    `uf_id` int(10) unsigned NOT NULL COMMENT 'UF ID',
    `uf_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'UF Name',
    `contact_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Contact ID',
    `language` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'UI language preferred by the given user/contact',
    PRIMARY KEY (`id`),
    UNIQUE KEY `UI_uf_name_domain_id` (`uf_name`,`domain_id`),
    UNIQUE KEY `UI_contact_domain_id` (`contact_id`,`domain_id`),
    KEY `I_civicrm_uf_match_uf_id` (`uf_id`),
    KEY `FK_civicrm_uf_match_domain_id` (`domain_id`),
    CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
    CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`)  REFERENCES `civicrm_domain` (`id`)
   )
  
  So the SQL statement to delete every link except the admin user is:
  delete from civicrm_uf_match
  where uf_id !=1
  
  The batch found at Administer -> Users and Permissions -> Synchronize Users to Contacts brings them back. But better try this first on a test instance.
  
  
  ---
  
  