0
 MYSQL SYNTAX 


table STUDENT
      student_id : S001,S002,S003..... SO ON

In my student table I wanted varchar data as primary key and auto_increment that value. please do not comment like use server bases languages like PHP, JAVA OR ANY OTHER Language.

I wanted pure MySQL syntax.

Rajendra arora
  • 2,186
  • 1
  • 16
  • 23
  • 1
    If you are sure that the prefix would always be fixed as `S`, then why not just handle this entirely in your presentation layer? – Tim Biegeleisen Jan 03 '19 at 05:15

2 Answers2

0

Could you make the primary key a composite key? Then you could have the first column a prefix ('s' in this case) and an auto incrementing value as the second column. Together they would be your primary key. 

CREATE TABLE Student (
id INT NOT NULL AUTO_INCREMENT,
prefix CHAR(1) NOT NULL,
PRIMARY KEY (id, prefix),
...

Here's a link to a very similar question.

— Travis W.

iamtravisw
  • 321
  • 1
  • 3
  • 16
0

The idea in database design, is to keep each data element separate. And each element has its own datatype, constraints and rules. That c0002 is not one field, but two. Same with XXXnnn or whatever. It is incorrect , and it will severely limit your ability to use the data, and use database features and facilities.

Break it up into two discrete data items: column_1 CHAR(1)

column_2 INTEGER

Then set AUTOINCREMENT on column_2

And yes, your Primary Key can be (column_1, column_2), so you have not lost whatever meaning c0002 has for you.

check this very similar question and answer is also accepted

How to autoincrement a varchar

Pervaiz Iqbal
  • 316
  • 2
  • 8