Make unique key as a Foreign key?

Question

Hello i want to make unique key as a foreign key but i got an error i already checked this question

mY member Table

 CREATE TABLE IF NOT EXISTS `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `facebook` varchar(100) NOT NULL,
  `cnic` varchar(100) NOT NULL,
  `expertise` varchar(100) NOT NULL,
  `com_code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB

and my profile image tabl

create table profileimage(
imageid int primary key not null,
username varchar(100) foreign key references member(username) 
ON DELETE CASCADE
 ON UPDATE CASCADE,
imagepath varchar(255) not null
);

and error i faced #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'foreign key references member(username) ON DELETE CASCADE ON UPDATE CASCADE,' at line 6

Answer 1

Yes, you can have a FOREIGN KEY constraint that references a column with a UNIQUE constraint.

The syntax error you get is because you didn't provide a datatype for the column. It should be the same type as the referenced column: username varchar(50).

Another issue is the syntax for foreign constraints. If you put them "inline", i.e immediately after the column definition, you skip the FOREIGN KEY and have only REFERENCES:

-- Don't do this in MySQL !!!!
CREATE TABLE profileimage
( imageid int  NOT NULL PRIMARY KEY,
  username varchar(50)
     REFERENCES member (username) 
     ON DELETE CASCADE
     ON UPDATE CASCADE,
  imagepath varchar(255) NOT NULL
) ENGINE=InnoDB ;

or you put them separately, with the FOREIGN KEY keyword:

-- Do this in MySQL
CREATE TABLE profileimage
( imageid int NOT NULL PRIMARY KEY,
  username varchar(50),
  imagepath varchar(255) NOT NULL,
  FOREIGN KEY (username)
     REFERENCES member (username) 
     ON DELETE CASCADE
     ON UPDATE CASCADE
) ENGINE=InnoDB ;

Big surprise: Both syntaxes work in MySQL with a catch: the first is parsed for syntax validity (and it passes) but the foreign key is not created! (you get a warning)

The second syntax works fine.

Answer 2

MySQL 5.7 Reference Manual / ... / Using FOREIGN KEY Constraints

Furthermore, MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

So it should be:

create table profileimage(
    imageid int primary key not null,
    username varchar(50) not null,
    imagepath varchar(255) not null,

    foreign key (username) references member(username)
    on delete cascade on update cascade
);

Answer 3

It is a pity that MySQL has not supported this for 20 years, just look at (in chronological order): bug #4919, bug #17943, bug #102904.

The task would be rather simple to address, with not much coding involved. Otherwise MySQL engineers, please give a clear warning that the functionality is still not supposed to work. Do not feed wrong expectations.

MariaDB started to support this with release 10.5.