Foreign keys and sharing

Question

Since foreign keys are not supported by mySQL databases at the moment, I would like to hear some pro and con for a read-strength application that will process about 1-400,000 rows per table. Unfortunately, I do not have enough experience in this area to draw a conclusion myself ...

Thank you so much!

Literature:

How to handle a foreign key when splitting

Separating mySQL tables with foreign keys?

Answer 1

Well, if you need a table partition of up to 400,000 rows, then you will get a database other than MySQL. Jokes aside. By modern standards, any table below 1,000,000 rows is usually small (not even small) if you also do not have an index, etc. And modern standards in this regard are about 10 years.

Answer 2

Well, a section is not a good solution for a complex data model. If you have only 2-3 tables depending on each other, you can do it, but it is not very. Each table should have a column defining a section. Then each table must have a trigger to create a new table, set a foreign key and a unique constraint.

For example, audit

Each audit has from 0 to n audits. table auditentry contains the foreign key of the transaction. Both tables must have column createDate, as they are used to separate both tables.

------ create a trigger to insert an audittransaction in a trigger

create or replace function audittransaction_insert_function() returns trigger as $$ DECLARE tablepartition varchar; tablename varchar; startbounds timestamp; endbounds timestamp; BEGIN tablepartition := to_char(date_trunc('month', NEW.whendone), 'YYYYMMDD'); tablename := 'audittransaction_' || tablepartition ; if not exists(select * from information_schema.tables where table_name = tablename) then startbounds := date_trunc('month', NEW.whendone); endbounds := startbounds + cast('1 months' as interval); execute 'create table ' || tablename || ' ( CHECK (whendone >= ' || quote_literal(startbounds) || ' and whendone < ' || quote_literal(endbounds)|| ') ) inherits (audittransaction)'; execute 'ALTER TABLE '|| tablename ||' ADD CONSTRAINT '||tablename||'_unique_id UNIQUE (id)'; end if; execute 'insert into ' || tablename || ' (id, operationid, whendone, "comment", ticketid ,transactionid, userid ) values (' || quote_literal(NEW.id) || ',' || quote_literal(NEW.operationid) || ',' || quote_literal(NEW.whendone) || ')'; return null; END; $$ LANGUAGE plpgsql; create trigger insert_audittrans 

----- then create a trigger for autientry

 create or replace function auditentry_insert_function() returns trigger as $$ DECLARE tablepartition varchar; tablename varchar; startbounds timestamp; endbounds timestamp; BEGIN tablepartition := to_char(date_trunc('month', NEW.transactiontimestampgmt), 'YYYYMMDD'); tablename := 'auditentry_' || tablepartition ; if not exists(select * from information_schema.tables where table_name = tablename) then startbounds := date_trunc('month', NEW.transactiontimestampgmt); endbounds := startbounds + cast('1 months' as interval); execute 'create table ' || tablename || ' ( CHECK (transactiontimestampgmt >= ' || quote_literal(startbounds) || ' and transactiontimestampgmt < ' || quote_literal(endbounds)|| ') ) inherits (auditentry)'; execute 'ALTER TABLE '|| tablename ||' ADD CONSTRAINT '||tablename||'_unique_id UNIQUE (id)'; execute 'ALTER TABLE ' || tablename ||' ADD CONSTRAINT auditentry FOREIGN KEY (audit_transaction_id) REFERENCES audittransaction_'||tablepartition ||'(id)'; end if; execute 'insert into ' || tablename || ' (id, audit_transaction_id, eventid, transactiontimestampgmt,timestampgmt, acknowledged, resolved, acknowledgedbyusername, acknowledgeddate, notificationlevel, resolvedbyusername, resolveddate, severity, parentauditentry_id ) values (' || quote_literal(NEW.id) || ',' || quote_literal(NEW.audit_transaction_id) || ',' || quote_literal(NEW.eventid) || ','||quote_literal(NEW.transactiontimestampgmt)||')'; return null; END; $$ LANGUAGE plpgsql; create trigger insert_auditentry before insert on auditentry for each row execute procedure auditentry_insert_function();