MySQL

AUTO_INCREMENT

References: Using AUTO_INCREMENT (MySQL Manual)

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100

To insert a new record into the "Persons" table, we will not have to specify a value for the "P_Id" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

Alter a table column to be AUTO_INCREMENT

-- previous column was:
-- id_livro int not null,
ALTER TABLE livro MODIFY COLUMN id_livro INT NOT NULL AUTO_INCREMENT;

ALTER TABLE

References: ALTER TABLE Syntax

Examples:

ALTER TABLE Persons AUTO_INCREMENT=100
ALTER TABLE livro MODIFY COLUMN id_livro INT NOT NULL AUTO_INCREMENT;
TODO
TODO

Unique

ref: CREATE TABLE Syntax, When should I use a unique constraint instead of a unique index?, SQL UNIQUE Constraint, MySQL INDEXS, Differences between index, primary, unique, fulltext? MySQL

You can either use a UNIQUE CONSTRAINT or a UNIQUE INDEX: under the hood a unique constraint is implemented the same way as a unique index (because an index is needed to efficiently fulfill the requirement to enforce the constraint).
So for a database that supports both features the choice of which to use will often come down to preferred style and consistency. But to make it more obvious:

If you are planning to use the index as an index (i.e. your code may rely on searching/sorting/filtering on that field to be quick) I would explicitly use a unique index;
Likewise if you are only needing to enforce uniqueness as a business rule rather than the field needing to be searched or used for sorting then I'd use the constraint

Using a UNIQUE CONSTRAINT:

Ex1:

CREATE TABLE Book
(
P_Id int NOT NULL AUTO_INCREMENT,
Title varchar(255) NOT NULL,
ISBN varchar(255),
PRIMARY KEY (P_Id),
UNIQUE(ISBN)
)

Ex2:
To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Book
(
P_Id int NOT NULL AUTO_INCREMENT,
Title varchar(255) NOT NULL,
ISBN varchar(255),
PRIMARY KEY (P_Id),
CONSTRAINT uc_Book UNIQUE(P_Id,ISBN)
)

Using a UNIQUE INDEX:

Ex:

CREATE TABLE Book
(
P_Id int NOT NULL AUTO_INCREMENT,
Title varchar(255) NOT NULL,
ISBN varchar(255),
PRIMARY KEY (P_Id),
UNIQUE INDEX (ISBN)
)

You can also create the index separately:
Syntax:

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

TODO

code

TODO

code

TODO

code

TODO

code

TODO

code

IT

Pages

MySQL

AUTO_INCREMENT

ALTER TABLE

Unique

Using a UNIQUE CONSTRAINT:

Using a UNIQUE INDEX:

No comments:

Post a Comment

Labels