0

I'm having a defined relation to my user table as foreign key, but they don't necessarily need to be set. I.e. there must not be a related record.

In my MySQL editor (Sequel Pro) I cannot make the addressId "allow null". It ignores the input. Im using newest version.

When I create a new entry in the User table without an addressId, it says "Cannot add or update a child row: a foreign key constraint fails...". But I want to be able to add a User without an Address! Is that possible?

Or should I simply remove the foreign keys? What is the drawback of that?

CREATE TABLE `User1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `addressId` int(11) NULL DEFAULT '0',
  `created` datetime DEFAULT NULL,
  `activated` datetime DEFAULT NULL,
  `lastLogin` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`addressId`),
  KEY `FK_User_Locale` (`localeId`),
  KEY `FK_User_Address` (`addressId`),
  CONSTRAINT `FK_User_Address` FOREIGN KEY (`addressId`) REFERENCES `Address` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
preyz
  • 3,029
  • 5
  • 29
  • 36
  • I would remove the foreign key contraint.. IMO using a database to enforce referential integrity is a waste of resources. Plus when you rename columns and tables its a nightmare. Writing strong server scripts is a much better approach. – dano Jul 24 '12 at 22:51
  • @dano Columns and tables should never be renamed. A far worse nightmare is learning the script you thought was strong is actually weak and your data has lost integrity. – None May 12 '15 at 00:09

1 Answers1

1

Why don't you disable foreign_key_checks by setting to foreign_key_checks=0?

Else you may DROP the constraint if it does't suit your requirements!

If set to 1 (the default), foreign key constraints for InnoDB tables are checked. If set to 0, they are ignored. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships.

Jacob
  • 2,041
  • 14
  • 16
  • Wouldn't it make more sense then, to just remove the foreign key declarations alltogether? – preyz Jul 25 '12 at 13:26
  • Yeah its your wish!. If you don't need any referential integrity you may DROP it. Anyway, I've edited my answer accordingly. – Jacob Jul 25 '12 at 15:47