CODEWITHSUNDEEP
mysqlcreate-table
user2025469
user2025469

Reputation: 1571

create an auto increment column which isn't a primary key

I read a lot about why this is a bad idea but here is why I want to do this. I want my table to look like this:

id   |   article   |   link   |   text_cont
 1   |   //text    |   www... |  parsed text content
 2   |   //text2   |   www..2 |  parsed text content2

And so on. The reson why I don't want my id to be the primary key is because I want my LINK to be the primary key...that way when I run a Cron Job to get more data, I will not have duplicate fields in my table. I can use INSERT IGNORE and it will not include same links twice. But I still want id to auto increment so I can use the "infinite scroll" using the id's.

Is this possible to do? I really hope so because I think this is a good reason to have this...

Upvotes: 1

Views: 739

Answers (2)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

You don't need a primary key to be able to have it auto increment. This works fine.

create table dude (
  id int auto_increment,
  link varchar(255),
  primary key (link),
  unique key (id)
);

insert into dude values (null, "www.google.com");
insert into dude values (null, "www.stackoverflow.com");
insert into dude values (null, "www.google.com");

It might not be the best table design but it answers your question.

Upvotes: 0

Nick Cardoso
Nick Cardoso

Reputation: 21733

You cant auto-increment a text field.

Also if you don't want Id as your key, why do you want id? It seems like you need to change the way you run your cron job, not your schema

--

If you still want to know how to create an AI field:

link_id int NOT NULL AUTO_INCREMENT

And for existing data:

ALTER TABLE articles MODIFY COLUMN link_id int(4) auto_increment

Upvotes: 1

Related Questions