I am using a MySQL database and I am trying to execute a database creation sctipt but I get the following error without indication of which line is concerned:
SQL Error (1071): Specified key was too long; max key length is 3072 bytes
This is my script:
/*==============================================================*/
/* Table: MDEV_ADDRESS */
/*==============================================================*/
create table MDEV_ADDRESS
(
id_address bigint not null auto_increment,
street_1 varchar(2500),
street_2 varchar(2500),
zip_code int,
city varchar(255),
region varchar(255),
country varchar(255),
is_delivery_address bool,
primary key (id_address)
);
/*==============================================================*/
/* Table: MDEV_BANK_CARD */
/*==============================================================*/
create table MDEV_BANK_CARD
(
id_bank_card bigint not null auto_increment,
id_user bigint not null,
card_type varchar(150) not null,
card_number int not null,
expiration_date timestamp not null,
security_code int not null,
holder_name varchar(255),
primary key (id_bank_card)
);
/*==============================================================*/
/* Table: MDEV_CATEGORY */
/*==============================================================*/
create table MDEV_CATEGORY
(
id_category bigint not null auto_increment,
id_parent_category bigint,
name varchar(1500) not null,
description varchar(2500),
primary key (id_category)
);
/*==============================================================*/
/* Table: MDEV_CUSTOMER_HAS_ADDRESSES */
/*==============================================================*/
create table MDEV_CUSTOMER_HAS_ADDRESSES
(
id_user bigint not null,
id_address bigint not null,
primary key (id_user, id_address)
);
/*==============================================================*/
/* Table: MDEV_LOGIN_ATTEMPT */
/*==============================================================*/
create table MDEV_LOGIN_ATTEMPT
(
id_login_attempt bigint not null auto_increment,
id_user bigint not null,
attempt_date timestamp not null,
ip_address varchar(150) not null,
has_logged_in bool not null,
log_out_date timestamp,
os varchar(255),
browser varchar(255),
primary key (id_login_attempt)
);
/*==============================================================*/
/* Table: MDEV_NOTIFICATION */
/*==============================================================*/
create table MDEV_NOTIFICATION
(
id_notification bigint not null auto_increment,
id_user bigint not null,
creation_date timestamp not null,
message varchar(2500) not null,
link_url varchar(2500),
is_viewed bool not null,
primary key (id_notification)
);
/*==============================================================*/
/* Table: MDEV_ORDER */
/*==============================================================*/
create table MDEV_ORDER
(
id_order bigint not null auto_increment,
id_user bigint not null,
id_status bigint not null,
reference_number varchar(255) not null unique,
order_date timestamp not null,
payment_date timestamp,
payment_method varchar(255),
primary key (id_order)
);
/*==============================================================*/
/* Table: MDEV_ORDER_LINE */
/*==============================================================*/
create table MDEV_ORDER_LINE
(
id_order_line bigint not null auto_increment,
id_product bigint not null,
id_order bigint not null,
quantity int not null,
primary key (id_order_line)
);
/*==============================================================*/
/* Table: MDEV_PRODUCT */
/*==============================================================*/
create table MDEV_PRODUCT
(
id_product bigint not null auto_increment,
id_category bigint,
reference_number varchar(255) not null unique,
name varchar(1500),
description varchar(2500),
image_path varchar(2500),
price float,
primary key (id_product)
);
/*==============================================================*/
/* Table: MDEV_PRODUCT_HAS_TAGS */
/*==============================================================*/
create table MDEV_PRODUCT_HAS_TAGS
(
id_product bigint not null,
id_tag bigint not null,
primary key (id_product, id_tag)
);
/*==============================================================*/
/* Table: MDEV_ROLE */
/*==============================================================*/
create table MDEV_ROLE
(
id_role bigint not null auto_increment,
name varchar(1500) not null unique,
description varchar(2500),
primary key (id_role)
);
/*==============================================================*/
/* Table: MDEV_SITE_PREFERENCES */
/*==============================================================*/
create table MDEV_SITE_PREFERENCES
(
id_site_preferences bigint not null auto_increment,
name varchar(1500) unique,
root_images_folder_path varchar(2500),
image_max_size int,
image_max_size_unit varchar(2),
image_min_size int,
image_min_size_unit varchar(2),
root_videos_folder_path varchar(2500),
video_max_size int,
video_max_size_unit varchar(2),
video_min_size int,
video_min_size_unit varchar(2),
primary key (id_site_preferences)
);
/*==============================================================*/
/* Table: MDEV_STATUS */
/*==============================================================*/
create table MDEV_STATUS
(
id_status bigint not null auto_increment,
name varchar(1500) not null unique,
primary key (id_status)
);
/*==============================================================*/
/* Table: MDEV_TAG */
/*==============================================================*/
create table MDEV_TAG
(
id_tag bigint not null auto_increment,
name varchar(1500) not null unique,
primary key (id_tag)
);
/*==============================================================*/
/* Table: MDEV_USER */
/*==============================================================*/
create table MDEV_USER
(
id_user bigint not null auto_increment,
id_status bigint not null,
discriminator varchar(50) not null,
u_email varchar(255) not null unique,
u_password varchar(255) not null,
u_username varchar(255) unique,
u_first_name varchar(255) not null,
u_last_name varchar(255) not null,
u_birth_date timestamp,
u_gender varchar(150),
u_registration_date timestamp not null,
u_confirmation_date timestamp,
u_expiration_date timestamp,
u_locking_date timestamp,
u_disabling_date timestamp,
u_credentials_expiration_date timestamp,
u_deletion_date timestamp,
u_locking_reason varchar(5000),
u_disabling_reason varchar(5000),
u_deletion_reason varchar(5000),
u_used_language varchar(150),
u_security_question varchar(255),
u_security_answer varchar(255),
u_code varchar(255) not null unique,
c_phone int,
e_job varchar(255),
e_cnss_number varchar(255),
primary key (id_user)
);
/*==============================================================*/
/* Table: MDEV_USER_HAS_ROLES */
/*==============================================================*/
create table MDEV_USER_HAS_ROLES
(
id_role bigint not null,
id_user bigint not null,
primary key (id_role, id_user)
);
alter table MDEV_BANK_CARD add constraint FK_MDEV_CUSTOMER_HAS_BANK_CARDS foreign key (id_user)
references MDEV_USER (id_user) on delete cascade on update cascade;
alter table MDEV_CATEGORY add constraint FK_MDEV_CATEGORY_HAS_SUB_CATEGORIES foreign key (id_parent_category)
references MDEV_CATEGORY (id_category) on delete cascade on update cascade;
alter table MDEV_CUSTOMER_HAS_ADDRESSES add constraint FK_MDEV_CUSTOMER_HAS_ADDRESSES foreign key (id_user)
references MDEV_USER (id_user) on delete cascade on update cascade;
alter table MDEV_CUSTOMER_HAS_ADDRESSES add constraint FK_MDEV_CUSTOMER_HAS_ADDRESSES2 foreign key (id_address)
references MDEV_ADDRESS (id_address) on delete cascade on update cascade;
alter table MDEV_LOGIN_ATTEMPT add constraint FK_MDEV_USER_HAS_LOGIN_ATTEMPTS foreign key (id_user)
references MDEV_USER (id_user) on delete cascade on update cascade;
alter table MDEV_NOTIFICATION add constraint FK_MDEV_CUSTOMER_HAS_NOTIFICATIONS foreign key (id_user)
references MDEV_USER (id_user) on delete cascade on update cascade;
alter table MDEV_ORDER add constraint FK_MDEV_CUSTOMER_HAS_ORDERS foreign key (id_user)
references MDEV_USER (id_user) on delete cascade on update cascade;
alter table MDEV_ORDER add constraint FK_MDEV_ORDER_HAS_STATUS foreign key (id_status)
references MDEV_STATUS (id_status) on delete cascade on update cascade;
alter table MDEV_ORDER_LINE add constraint FK_MDEV_ORDER_HAS_ORDER_LINES foreign key (id_order)
references MDEV_ORDER (id_order) on delete cascade on update cascade;
alter table MDEV_ORDER_LINE add constraint FK_MDEV_ORDER_LINE_HAS_PRODUCT foreign key (id_product)
references MDEV_PRODUCT (id_product) on delete cascade on update cascade;
alter table MDEV_PRODUCT add constraint FK_MDEV_CATEGORY_HAS_PRODUCTS foreign key (id_category)
references MDEV_CATEGORY (id_category) on delete cascade on update cascade;
alter table MDEV_PRODUCT_HAS_TAGS add constraint FK_MDEV_PRODUCT_HAS_TAGS foreign key (id_product)
references MDEV_PRODUCT (id_product) on delete cascade on update cascade;
alter table MDEV_PRODUCT_HAS_TAGS add constraint FK_MDEV_PRODUCT_HAS_TAGS2 foreign key (id_tag)
references MDEV_TAG (id_tag) on delete cascade on update cascade;
alter table MDEV_USER add constraint FK_MDEV_USER_HAS_STATUS foreign key (id_status)
references MDEV_STATUS (id_status) on delete cascade on update cascade;
alter table MDEV_USER_HAS_ROLES add constraint FK_MDEV_USER_HAS_ROLES foreign key (id_role)
references MDEV_ROLE (id_role) on delete cascade on update cascade;
alter table MDEV_USER_HAS_ROLES add constraint FK_MDEV_USER_HAS_ROLES2 foreign key (id_user)
references MDEV_USER (id_user) on delete cascade on update cascade;
I am executing this script using HeidiSQL and apparently the executions throws the error when it reaches the creation of the table:
/*==============================================================*/
/* Table: MDEV_ROLE */
/*==============================================================*/
create table MDEV_ROLE
(
id_role bigint not null auto_increment,
name varchar(1500) not null unique,
description varchar(2500),
primary key (id_role)
);
As far as I know the only indexes that I am creating are the primary keys and they are bigint of only 8 bytes long.
If somebody can help me here it would be great? Thanks in advance.
You didn't mention version of MySQL. I'm presuming you are using InnoDB and MySQL 5.7.
You can find this snippet:
The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character.
It happens because you have unique name which will create an index which is subject of the aforementioned constraints.
More here: https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html