CODEWITHSUNDEEP
mysqlmysql-workbench
The King Eugene
The King Eugene

Reputation: 23

How to creating auto_increment columns in MySQL Workbench?

I'm using MySQL Workbench to create my DB tables. I have a table called Movies. Among its fields is one called purchase_times which is meant to record the total number of times a movie has been purchased. So, it should auto increment whenever a particular movie is purchased, but it isn't the primary key in the table. I want the column to read zero when no one has purchased the movie, then change to 1 when the first person purchases it, then keep incrementing as more purchases of the movie are made.

Q1. Is it possible to have two fields with auto-increment in the same table?

Q2. How can I set the value of the said column to be zero by default i.e before the movie is purchased by anyone?

Here is what I did:

CREATE TABLE Movies (
  movie_id INT(11) NOT NULL AUTO_INCREMENT,
  title VARCHAR(50) NOT NULL,
  genre VARCHAR(20) NOT NULL,
  release_year INT(11) NOT NULL,
  description TEXT(200) NOT NULL,
  poster_url VARCHAR(250) NOT NULL,
  purchase_times INT(11) AUTO_INCREMENT,
  PRIMARY KEY (movie_id)
);

Upvotes: 1

Views: 2140

Answers (1)

Gilang Pratama
Gilang Pratama

Reputation: 431

Q1. Yes, It's possible. But, If you have two auto_increment columns they would be the same, so there is no point having two auto_increment columns.

Q2. You can follow this query to set 0 default.

CREATE TABLE Movies (
  movie_id INT(11) NOT NULL AUTO_INCREMENT,
  title VARCHAR(50) NOT NULL,
  genre VARCHAR(20) NOT NULL,
  release_year INT(11) NOT NULL,
  description TEXT(200) NOT NULL,
  poster_url VARCHAR(250) NOT NULL,
  purchase_times INT(11) DEFAULT '0', //here is the zero default
  PRIMARY KEY (movie_id)
);

Upvotes: 1

Related Questions