CREATE TABLE milkekhareedo.groups ( groupId VARCHAR(36) NOT NULL, name VARCHAR(35) NOT NULL, dateofCreation DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, type VARCHAR(5) NOT NULL DEFAULT 'close', cityId VARCHAR(36) NULL, productId VARCHAR(36) NULL, PRIMARY KEY (groupId), INDEX cityId_idx (cityId ASC), INDEX productId_idx (productId ASC), CONSTRAINT cityId FOREIGN KEY (cityId) REFERENCES milkekhareedo.cities (cityId) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT productId FOREIGN KEY (productId) REFERENCES milkekhareedo.products (productId) ON DELETE SET NULL ON UPDATE CASCADE);
This is my group table.
And my product and city table is CREATE TABLE cities ( cityId varchar(36) NOT NULL, name varchar(100) NOT NULL DEFAULT '', stateId varchar(36) NOT NULL, PRIMARY KEY (cityId), KEY stateId (stateId), CONSTRAINT cities_ibfk_1 FOREIGN KEY (stateId) REFERENCES states (stateId) ) ENGINE=IoDB DEFAULT CHARSET=latin1;
CREATE TABLE products ( productId varchar(36) NOT NULL, name varchar(100) NOT NULL, youtubeLink varchar(200) DEFAULT NULL, description varchar(200) DEFAULT NULL, PRIMARY KEY (productId) ) ENGINE=IoDB DEFAULT CHARSET=utf8;