[Rt-devel] Constraints for MySQL

[Ruslan U. Zakirov]

	Hello, all.
Attached file with updated constraints to RT DB schema.

			Best regards. Ruslan.
-------------- next part --------------
ALTER TABLE Attachments ADD FOREIGN KEY (TransactionId) REFERENCES Transactions(id);
# Can't be applied because RT use '0' instead undef(NULL) for undefined parents
#ALTER TABLE Attachments ADD FOREIGN KEY (Parent) REFERENCES Attachments(id);

CREATE INDEX Attachments4 ON Attachments (Creator);
ALTER TABLE Attachments ADD FOREIGN KEY (Creator) REFERENCES Principals(id);

CREATE INDEX Queues3 ON Queues (Creator);
ALTER TABLE Queues ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX Queues4 ON Queues (LastUpdatedBy);
ALTER TABLE Queues ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);

CREATE INDEX LinksFKI1 ON Links(LocalBase);
ALTER TABLE Links ADD FOREIGN KEY (LocalBase) REFERENCES Tickets(id);
# RT uses empty string as default {{{
#CREATE INDEX LinksFKI2 ON Links(LocalTarget);
#ALTER TABLE Links ADD FOREIGN KEY (LocalTarget) REFERENCES Tickets(id);
# }}}
CREATE INDEX LinksFKI3 ON Links(Creator);
ALTER TABLE Links ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX LinksFKI4 ON Links(LastUpdatedBy);
ALTER TABLE Links ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);

ALTER TABLE Groups ADD FOREIGN KEY (id) REFERENCES Principals(id);

CREATE INDEX ScripConditionsFKI1 ON ScripConditions(Creator);
ALTER TABLE ScripConditions ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX ScripConditionsFKI2 ON ScripConditions(LastUpdatedBy);
ALTER TABLE ScripConditions ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);

ALTER TABLE Transactions ADD FOREIGN KEY (Ticket) REFERENCES Tickets(id);
# Here RT glitch too
#ALTER TABLE Transactions ADD FOREIGN KEY (EffectiveTicket) REFERENCES Tickets(id);
CREATE INDEX TransactionsFKI1 ON Transactions(Creator);
ALTER TABLE Transactions ADD FOREIGN KEY (Creator) REFERENCES Principals(id);

CREATE INDEX ScripsFKI1 ON Scrips(ScripCondition);
ALTER TABLE Scrips ADD FOREIGN KEY (ScripCondition) REFERENCES ScripConditions(id);
CREATE INDEX ScripsFKI2 ON Scrips(ScripAction);
ALTER TABLE Scrips ADD FOREIGN KEY (ScripAction) REFERENCES ScripActions(id);
CREATE INDEX ScripsFKI3 ON Scrips(Template);
ALTER TABLE Scrips ADD FOREIGN KEY (Template) REFERENCES Templates(id);
# Hehe, and here problems too, Global queue
#CREATE INDEX ScripsFKI4 ON Scrips(Queue);
#ALTER TABLE Scrips ADD FOREIGN KEY (Queue) REFERENCES Queues(id);
CREATE INDEX ScripsFKI5 ON Scrips(Creator);
ALTER TABLE Scrips ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX ScripsFKI6 ON Scrips(LastUpdatedBy);
ALTER TABLE Scrips ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);

CREATE INDEX ACLFKI1 ON ACL(PrincipalId);
ALTER TABLE ACL ADD FOREIGN KEY (PrincipalId) REFERENCES Principals(id);
# Sucks {{{
#CREATE INDEX ACLFKI2 ON ACL(DelegatedBy);
#ALTER TABLE ACL ADD FOREIGN KEY (DelegatedBy) REFERENCES Principals(id);
#CREATE INDEX ACLFKI3 ON ACL(DelegatedFrom);
#ALTER TABLE ACL ADD FOREIGN KEY (DelegatedFrom) REFERENCES ACL(id);
# }}}

CREATE INDEX GroupMembersFKI1 ON GroupMembers(GroupId);
ALTER TABLE GroupMembers ADD FOREIGN KEY (GroupId) REFERENCES Principals(id);
CREATE INDEX GroupMembersFKI2 ON GroupMembers(MemberId);
ALTER TABLE GroupMembers ADD FOREIGN KEY (MemberId) REFERENCES Principals(id);

CREATE INDEX CachedGroupMembersFKI1 ON CachedGroupMembers(ImmediateParentId);
ALTER TABLE CachedGroupMembers ADD FOREIGN KEY (ImmediateParentId) REFERENCES Principals(id);
ALTER TABLE CachedGroupMembers ADD FOREIGN KEY (GroupId) REFERENCES Principals(id);
CREATE INDEX CachedGroupMembersFKI2 ON CachedGroupMembers(MemberId);
ALTER TABLE CachedGroupMembers ADD FOREIGN KEY (MemberId) REFERENCES Principals(id);
CREATE INDEX CachedGroupMembersFKI3 ON CachedGroupMembers(Via);
ALTER TABLE CachedGroupMembers ADD FOREIGN KEY (Via) REFERENCES CachedGroupMembers(id);

ALTER TABLE Users ADD FOREIGN KEY (id) REFERENCES Principals(id);
CREATE INDEX UsersFKI1 ON Users(Creator);
ALTER TABLE Users ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX UsersFKI2 ON Users(LastUpdatedBy);
ALTER TABLE Users ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);

ALTER TABLE Tickets ADD FOREIGN KEY (EffectiveId) REFERENCES Tickets(id);
ALTER TABLE Tickets ADD FOREIGN KEY (Queue) REFERENCES Queues(id);
ALTER TABLE Tickets ADD FOREIGN KEY (Owner) REFERENCES Principals(id);
CREATE INDEX TicketsFKI1 ON Tickets(Creator);
ALTER TABLE Tickets ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX TicketsFKI2 ON Tickets(LastUpdatedBy);
ALTER TABLE Tickets ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);

CREATE INDEX ScripActionsFKI1 ON ScripActions(Creator);
ALTER TABLE ScripActions ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX ScripActionsFKI2 ON ScripActions(LastUpdatedBy);
ALTER TABLE ScripActions ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);

# global queue {{{
#CREATE INDEX TemplatesFKI1 ON Templates(Queue);
#ALTER TABLE Templates ADD FOREIGN KEY (Queue) REFERENCES Queues(id);
# }}}
CREATE INDEX TemplatesFKI2 ON Templates(Creator);
ALTER TABLE Templates ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX TemplatesFKI3 ON Templates(LastUpdatedBy);
ALTER TABLE Templates ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);

CREATE INDEX TicketCustomFieldValuesFKI1 ON TicketCustomFieldValues(Ticket);
ALTER TABLE TicketCustomFieldValues ADD FOREIGN KEY (Ticket) REFERENCES Tickets(id);
ALTER TABLE TicketCustomFieldValues ADD FOREIGN KEY (CustomField) REFERENCES CustomFields(id);
CREATE INDEX TicketCustomFieldValuesFKI2 ON TicketCustomFieldValues(Creator);
ALTER TABLE TicketCustomFieldValues ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX TicketCustomFieldValuesFKI3 ON TicketCustomFieldValues(LastUpdatedBy);
ALTER TABLE TicketCustomFieldValues ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);

# global queue {{{
#CREATE INDEX CustomFieldsFKI1 ON CustomFields(Queue);
#ALTER TABLE CustomFields ADD FOREIGN KEY (Queue) REFERENCES Queues(id);
# }}}
CREATE INDEX CustomFieldsFKI2 ON CustomFields(Creator);
ALTER TABLE CustomFields ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX CustomFieldsFKI3 ON CustomFields(LastUpdatedBy);
ALTER TABLE CustomFields ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);

ALTER TABLE CustomFieldValues ADD FOREIGN KEY (CustomField) REFERENCES CustomFields(id);
CREATE INDEX CustomFieldValuesFKI1 ON CustomFieldValues(Creator);
ALTER TABLE CustomFieldValues ADD FOREIGN KEY (Creator) REFERENCES Principals(id);
CREATE INDEX CustomFieldValuesFKI2 ON CustomFieldValues(LastUpdatedBy);
ALTER TABLE CustomFieldValues ADD FOREIGN KEY (LastUpdatedBy) REFERENCES Principals(id);