drop foreign key constraint not removed completely

Question

I dropped a foreign key constraint in users_id field, and wanted to set field value to allow NULL values, and DEFAULT NULL.

It seems like foreign key constraint has been removed, but somehow I cannot make the field NULL-able.

ALTER TABLE tbl_name DROP FOREIGN KEY `fk_internal_team_has_users_users1`;

Here's the table schema after dropping foreign key:

 CREATE TABLE `internal_team_head` ( `id` int(11) NOT NULL AUTO_INCREMENT, `internal_team_id` int(11) NOT NULL, `users_id` int(11) NOT NULL DEFAULT '0', `type` enum('lead','project_manager') NOT NULL, `updated_by` int(6) DEFAULT NULL, `updated_on` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`,`internal_team_id`,`users_id`,`type`), KEY `fk_internal_team_has_users_users1_idx` (`users_id`), KEY `fk_internal_team_has_users_internal_team1_idx` (`internal_team_id`), CONSTRAINT `fk_internal_team_has_users_internal_team1` FOREIGN KEY (`internal_team_id`) REFERENCES `internal_team` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=utf8

Now I want to change users_id to allow NULL values and default to NULL.

ALTER TABLE `internal_team_head` CHANGE `users_id` `users_id` INT(11) NULL DEFAULT NULL;

The Alter query executes successfully but users_id Null attribute is still set to No and default value set to 0.

How do I enforce the changes?

Community · Accepted Answer · 2020-06-20 09:12:55Z

From the manual:

PRIMARY KEY

A unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently).

Solution therefore is, to remove the column from the primary key. Your PK design makes no sense, anyway. The auto_increment column is sufficent as PK. Other columns in an index are only used if the left most column is also used. The row is identified by a unique index (auto_increment) already, so there's no need for that.
Also keep in mind, that you want your PK to be as small as possible. Secondary indexes include the PK to access rows. So try to avoid unnecessary overhead.

Also I'm puzzled, because your question title has nothing to do with your question. But to address that issue also, a KEY is not a FOREIGN KEY, it's an index. Yes, the naming is suboptimal.

Lina Silveira · Accepted Answer · 2018-06-22 09:00:39Z

0

Try this:

ALTER TABLE internal_team_head ALTER COLUMN users_id INT(11) NULL;

answered Jun 22, 2018 at 9:00

Lina Silveira

177 bronze badges

Collectives™ on Stack Overflow

drop foreign key constraint not removed completely

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related