[MDEV-15726] CREATE TABLE ... LIKE ... - drop column without droping key with it Created: 2018-03-29  Updated: 2018-03-29  Resolved: 2018-03-29

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Alexander Zimnitski Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Windows 7 x64


 Description   

Trying to run script. Got error: "Key column 'id' doesn't exist in table"

DROP TABLE IF EXISTS promotions;
 
CREATE TABLE `promotions` (
 
  id mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
 
  `d_start` date NOT NULL,
 
  `d_end` date NOT NULL,
 
  `prioritet` char(3) NOT NULL,
 
  `buh_grp` tinyint(1) unsigned NOT NULL DEFAULT 1,
 
  `title` varchar(30) NOT NULL,
 
  `opisanie` text NOT NULL,
 
  `ts_created` datetime NOT NULL,
 
  `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `d_start` (`d_start`,`d_end`,`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
ALTER TABLE promotions DROP PRIMARY KEY, DROP COLUMN `id`, DROP COLUMN ts_created, DROP COLUMN ts;



 Comments   
Comment by Sergei Golubchik [ 2018-03-29 ]

What key definition would you expect as a result — UNIQUE KEY (d_start,d_end) ?
This means a new constraint, that is, before your ALTER TABLE (d_start,d_end) pair of values did not have to be unique, and now it will be. MariaDB, as required by the SQL standard, will not automatically create a new constraint for your data, you need to do it explicitly. Like this:

ALTER TABLE promotions DROP COLUMN id, DROP COLUMN ts_created, DROP COLUMN ts, DROP KEY d_start, ADD UNIQUE (d_start,d_end);

Note that there's no need to drop the primary key, it'll be dropped automatically, because you've dropped all columns in it.

Generated at Thu Feb 08 08:23:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.