1

I am creating a database that will store 'graduate details'. I have been able to set the ID to auto increment, Unique and also my primary key that start from 1...

I also want to make the certificate number field unique and auto-increment value in an increasing order that will start from 00001.

Please any help on how to go about this will be very appreciated. Thank you.

This is the MYSQL code for the database below;

CREATE TABLE IF NOT EXISTS `graduates` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `f_name` varchar(25) NOT NULL,
  `l_name` varchar(25) NOT NULL,
  `gender` varchar(6) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `city` varchar(15) DEFAULT NULL,
  `region` varchar(30) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `certificate_number` int(50) NOT NULL AUTO_INCREMENT,
  `programme` varchar(50) CHARACTER SET utf8mb4 NOT NULL,
  `marks` int(10) NOT NULL,
  `college_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL,
  `date_of_birth` date DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` date DEFAULT NULL,
  `updated_by` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `certificate_number` (`certificate_number`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8;
Osik Newton
  • 13
  • 5
  • Will `certificate_number` have the same number as `id` ? If it has the same number you can use a [generated column](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html) BTW: You cannot have two auto_increment columns in one table – Luuk Jun 13 '21 at 08:03
  • 1
    Leading zero's (in `00001`), is something you should solve in PHP using [str-pad](https://www.php.net/manual/en/function.str-pad.php) – Luuk Jun 13 '21 at 08:10
  • No please they won't have the same number. The id can start incrementing from 1 and the certificate number can also start incrementing from 00001. Thanks – Osik Newton Jun 13 '21 at 08:13
  • If both start incrementing from 1, and they will only increment on a new record being created (which is what auto_increment does do), they will have the same value. Myabe this can be solve using a trigger, see: https://stackoverflow.com/questions/22824439/how-to-create-two-auto-increment-columns-in-mysql – Luuk Jun 13 '21 at 08:18
  • I want both to be auto-incremented and unique as well. So what do I do please? – Osik Newton Jun 13 '21 at 08:22
  • 2
    Think about "If both start incrementing from 1, and they will only increment on a new record being created (which is what auto_increment does do)..." How will both fields ever have a different number? – Luuk Jun 13 '21 at 08:29
  • Please can I set the Certificate number field to auto-generate random values that will also be unique? – Osik Newton Jun 13 '21 at 08:36
  • You have to do some research yourself, I did find this link pretty quick: [Randomizing the Auto Increment in Mysql](https://dba.stackexchange.com/questions/72212/randomizing-the-auto-increment-in-mysql), which looks to have info on what you want to do. – Luuk Jun 13 '21 at 08:41

0 Answers0