1

I want to create a table name Users where I should have have columns User, cookieID, sessionID, Geo and then I want to first three columns to have some random unique value assigned automatically. I tried to make all three columns AUTO_INCREMENT with User column PRIMARY and 'cookieIDandsessionIDcolumnUNIQUE`. The SQL code is:

CREATE TABLE `users` ( `User` VARCHAR(20) NOT NULL AUTO_INCREMENT ,
 `cookieID` INT(20) NULL DEFAULT NULL AUTO_INCREMENT ,
 `sessionID` INT(20) NULL DEFAULT NULL AUTO_INCREMENT ,
 `Geo` VARCHAR(30) NULL DEFAULT NULL ,
 PRIMARY KEY (`User`), UNIQUE (`cookieID`), UNIQUE (`sessionID`), UNIQUE (`Geo`));

But, it did not work because only one column can be declared as AUTO_INCREMENT which must be PRIMARY. What is the another approach to do this?

2 Answers2

0

Since the auto-increment cannot be applied to multiple to rows and there no option for sequence in MySQL. You can use triggers for the unique update of the row with datetime.

Change to table creation to be of single auto-increment row.

CREATE TABLE `users` ( `User` VARCHAR(20) NOT NULL,
 `cookieID` INT(20) NULL DEFAULT NULL,
 `sessionID` INT(20) NULL DEFAULT NULL AUTO_INCREMENT ,
 `Geo` VARCHAR(30) NULL DEFAULT NULL,
 PRIMARY KEY (`User`), UNIQUE (`cookieID`), UNIQUE (`sessionID`), UNIQUE (`Geo`));

Create a trigger on the same table as below. You can set the unique values under the SET for as many column as you want.

CREATE DEFINER=`root`@`localhost` TRIGGER `users_BEFORE_INSERT` BEFORE INSERT ON `users` FOR EACH ROW BEGIN
SET 
NEW.cookieID = (SELECT curdate()+curtime());
END

Now when you insert into the table as below.

 insert into `users`(`User`) values("test");

You table looks like this.

User  cookieID  sessionID  Geo
test  20315169  0          NULL
Wasim Ansari
  • 260
  • 1
  • 4
0

If the value which are auto incrementing, you wanna keep both values the same. Then copy the value of one column to another during insertion time of new value.

Usman Wahab
  • 1
  • 1
  • I need the values to be independent from each other and not the same as it is. Is there any way to do that? –  Jul 31 '20 at 10:32