ALTER TABLE CHARITY_ASSOCIATE DROP INDEX IF EXISTS CHARITY_ASSOCIATE_PK;
DROP TABLE IF EXISTS CHARITY_ASSOCIATE;
CREATE TABLE CHARITY_ASSOCIATE
(
CHARITY_ASSOCIATE_ID MEDIUMINT NOT NULL AUTO_INCREMENT,
CHARITY_ID MEDIUMINT, -- FOREIGN KEY
CHARITY_ASSOCIATE_TYPE MEDIUMINT,
CHARITY_ASSOCIATE_NAME VARCHAR(80) NOT NULL,
CHARITY_ASSOCIATE_SURNAME VARCHAR(80) NOT NULL,
CHARITY_ASSOCIATE_DOB DATE NOT NULL,
PRIMARY KEY (CHARITY_ASSOCIATE_ID)
);
CREATE UNIQUE INDEX CHARITY_ASSOCIATE_PK ON CHARITY_ASSOCIATE (CHARITY_ASSOCIATE_ID);
ALTER TABLE `CHARITY_ASSOCIATE`
ADD CONSTRAINT `FK_CHARITY_ID_ASSOCIATE` FOREIGN KEY (`CHARITY_ID`)
REFERENCES `CHARITY` (`CHARITY_ID`)
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE `CHARITY_ASSOCIATE_ADDRESS` DROP FOREIGN KEY IF EXISTS `FK_CHARITY_ASSOCIATE_ADDRESS`;
DROP TABLE IF EXISTS `CHARITY_ASSOCIATE_ADDRESS`;
CREATE TABLE `CHARITY_ASSOCIATE_ADDRESS`
(
`CHARITY_ASSOCIATE_ADDRESS_ID` MEDIUMINT NOT NULL AUTO_INCREMENT,
`CHARITY_ASSOCIATE_ID` MEDIUMINT NOT NULL,
`CHARITY_ASSOCIATE_CHARITY_ASSOCIATE_ADDRESS_LINE1` VARCHAR(60),
`CHARITY_ASSOCIATE_CHARITY_ASSOCIATE_ADDRESS_LINE2` VARCHAR(60),
`CHARITY_ASSOCIATE_CITY` VARCHAR(60),
`CHARITY_ASSOCIATE_POSTAL_CODE` VARCHAR(10),
`CHARITY_ASSOCIATE_CHARITY_ASSOCIATE_ADDRESS_START_DATE` DATE,
`CHARITY_ASSOCIATE_CHARITY_ASSOCIATE_ADDRESS_END_DATE` DATE,
PRIMARY KEY (`CHARITY_ASSOCIATE_ADDRESS_ID`)
);
CREATE UNIQUE INDEX CHARITY_ASSOCIATE_ADDRESS_PK ON CHARITY_ASSOCIATE_ADDRESS (CHARITY_ASSOCIATE_ADDRESS_ID);
ALTER TABLE `CHARITY_ASSOCIATE_ADDRESS`
ADD CONSTRAINT `FK_CHARITY_ASSOCIATE_ADDRESS` FOREIGN KEY (`CHARITY_ASSOCIATE_ID`)
REFERENCES `CHARITY_ASSOCIATE` (`CHARITY_ASSOCIATE_ID`) #
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE CHARITY_ASSOCIATE_EMAIL DROP INDEX IF EXISTS CHARITY_ASSOCIATE_EMAIL_PK;
DROP TABLE IF EXISTS CHARITY_ASSOCIATE_EMAIL;
CREATE TABLE CHARITY_ASSOCIATE_EMAIL
(
CHARITY_ASSOCIATE_EMAIL_ID MEDIUMINT NOT NULL AUTO_INCREMENT,
CHARITY_ASSOCIATE_ID MEDIUMINT, -- FOREIGN KEY
CHARITY_ASSOCIATE_EMAIL_PRIORITY MEDIUMINT,
CHARITY_ASSOCIATE_EMAIL_EMAIL VARCHAR(250) NOT NULL,
PRIMARY KEY (CHARITY_ASSOCIATE_EMAIL_ID)
);
CREATE UNIQUE INDEX CHARITY_ASSOCIATE_EMAIL_PK ON CHARITY_ASSOCIATE_EMAIL (CHARITY_ASSOCIATE_EMAIL_ID);
ALTER TABLE `CHARITY_ASSOCIATE_EMAIL`
ADD CONSTRAINT `FK_CHARITY_ASSOCIATE_ID_EMAIL` FOREIGN KEY (`CHARITY_ASSOCIATE_ID`)
REFERENCES `CHARITY_ASSOCIATE` (`CHARITY_ASSOCIATE_ID`) #
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE CHARITY_ASSOCIATE_PHONE DROP INDEX IF EXISTS CHARITY_ASSOCIATE_PHONE_PK;
DROP TABLE IF EXISTS CHARITY_ASSOCIATE_PHONE;
CREATE TABLE CHARITY_ASSOCIATE_PHONE
(
CHARITY_ASSOCIATE_PHONE_ID MEDIUMINT NOT NULL AUTO_INCREMENT,
CHARITY_ASSOCIATE_ID MEDIUMINT, -- FOREIGN KEY
CHARITY_ASSOCIATE_PHONE_PRIORITY MEDIUMINT,
CHARITY_ASSOCIATE_PHONE_CELLPHONE VARCHAR(20) NOT NULL,
PRIMARY KEY (CHARITY_ASSOCIATE_PHONE_ID)
);
CREATE UNIQUE INDEX CHARITY_ASSOCIATE_PHONE_PK ON CHARITY_ASSOCIATE_PHONE (CHARITY_ASSOCIATE_PHONE_ID);
ALTER TABLE `CHARITY_ASSOCIATE_PHONE`
ADD CONSTRAINT `FK_CHARITY_ASSOCIATE_ID_PHONE` FOREIGN KEY (`CHARITY_ASSOCIATE_ID`)
REFERENCES `CHARITY_ASSOCIATE` (`CHARITY_ASSOCIATE_ID`) #
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE CHARITYDB.`CHARITY_ASSOCIATE_TYPES` DROP INDEX IF EXISTS `IDX_CHARITY_ASSOCIATE_TYPE`;
DROP TABLE IF EXISTS CHARITYDB.`CHARITY_ASSOCIATE_TYPES`;
CREATE TABLE CHARITYDB.`CHARITY_ASSOCIATE_TYPES`
(
`CHARITY_ASSOCIATE_TYPE_ID` MEDIUMINT NOT NULL AUTO_INCREMENT,
`CHARITY_ASSOCIATE_ID` MEDIUMINT NOT NULL, -- FOREIGN KEY
`CHARITY_ASSOCIATE_TYPE_NAME` VARCHAR(50),
CONSTRAINT `CHARITY_ASSOCIATE_TYPE_PK` PRIMARY KEY (`CHARITY_ASSOCIATE_TYPE_ID`)
);
CREATE UNIQUE INDEX `IDX_CHARITY_ASSOCIATE_TYPE` ON CHARITYDB.`CHARITY_ASSOCIATE_TYPES` (`CHARITY_ASSOCIATE_TYPE_ID` ASC);
ALTER TABLE `CHARITY_ASSOCIATE_TYPES`
ADD CONSTRAINT `FK_CHARITY_ASSOCIATE_TYPES` FOREIGN KEY (`CHARITY_ASSOCIATE_ID`)
REFERENCES `CHARITY_ASSOCIATE` (`CHARITY_ASSOCIATE_ID`) #
ON DELETE RESTRICT
ON UPDATE CASCADE;