Re: Can not add foreign key constraints

2017-04-24 Thread Peter Brawley 

On 4/24/2017 9:18, David Mehler wrote:

Hello,

  I'm trying to add a table to an existing database. I'm wanting it to get
  one of it's fields from an already existing table. I've done this
  before in this database. This works:

  CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`user` varchar(40) NOT NULL,
`password` varchar(128) NOT NULL,
`quota` bigint(20) NOT NULL DEFAULT 256,
`quota_messages` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `user` (`user`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  This does not:

  CREATE TABLE IF NOT EXISTS `lastauth` (
  `user` varchar(40) NOT NULL,
  `remote_ip` varchar(18) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
  CURRENT_TIMESTAMP,
  PRIMARY KEY (`user`),
  FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  Can anyone spot the situation?


Let's see the CREATE TABLE statement for the referenced table, and the 
error message.


PB

-



  Thanks.
  Dave.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Can not add foreign key constraints

2017-04-24 Thread Reindl Harald 



Am 24.04.2017 um 16:18 schrieb David Mehler:

  I'm trying to add a table to an existing database. I'm wanting it to get
  one of it's fields from an already existing table. I've done this
  before in this database. This works:

  CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`user` varchar(40) NOT NULL,
`password` varchar(128) NOT NULL,
`quota` bigint(20) NOT NULL DEFAULT 256,
`quota_messages` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `user` (`user`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  This does not:

  CREATE TABLE IF NOT EXISTS `lastauth` (
  `user` varchar(40) NOT NULL,
  `remote_ip` varchar(18) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
  CURRENT_TIMESTAMP,
  PRIMARY KEY (`user`),
  FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  Can anyone spot the situation?


wouldn't it be cool if you post the errors you get from the start?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Can not add foreign key constraints

2017-04-24 Thread David Mehler 
Hello,

 I'm trying to add a table to an existing database. I'm wanting it to get
 one of it's fields from an already existing table. I've done this
 before in this database. This works:

 CREATE TABLE `virtual_users` (
   `id` int(11) NOT NULL auto_increment,
   `domain_id` int(11) NOT NULL,
   `user` varchar(40) NOT NULL,
   `password` varchar(128) NOT NULL,
   `quota` bigint(20) NOT NULL DEFAULT 256,
   `quota_messages` int(11) NOT NULL DEFAULT 0,
   PRIMARY KEY (`id`),
   UNIQUE KEY `user` (`user`),
   FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 This does not:

 CREATE TABLE IF NOT EXISTS `lastauth` (
 `user` varchar(40) NOT NULL,
 `remote_ip` varchar(18) NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
 CURRENT_TIMESTAMP,
 PRIMARY KEY (`user`),
 FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 Can anyone spot the situation?

 Thanks.
 Dave.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql