Vraag Controleer de voorwaarde voor wederzijds exclusieve kolommen in SQLAlchemy


Als ik een declaratief model van SQLAlchemy heb zoals hieronder:

class Test(Model):
    __tablename__ = 'tests'

    id = Column(Integer, Sequence('test_id_seq'), primary_key=True)
    ...
    Atest_id = Column(Integer, ForeignKey('Atests.id'), nullable=True)
    Btest_id = Column(Integer, ForeignKey('Btests.id'), nullable=True)
    Ctest_id = Column(Integer, ForeignKey('Ctests.id'), nullable=True)
    Dtest_id = Column(Integer, ForeignKey('Dtests.id'), nullable=True)
    Etest_id = Column(Integer, ForeignKey('Etests.id'), nullable=True)
    ...
    date = Column(DateTime)
    status = Column(String(20))  # pass, fail, needs_review

En ik zou ervoor willen zorgen dat slechts een van de *test_id vreemde sleutels zijn aanwezig in een bepaalde rij, hoe kan ik dat bereiken in SQLAlchemy?

Ik zie dat er een is SQLAlchemy  CheckConstraint voorwerp (zie docs), maar MySQL biedt geen ondersteuning voor controlebeperkingen.

Het gegevensmodel heeft interactie buiten SQLAlchemy, dus het zou bij voorkeur een controle op databaseniveau zijn (MySQL)


14
2017-11-25 00:26


oorsprong


antwoorden:


Wel, gezien je vereisten "Het gegevensmodel heeft interactie buiten SQLAlchemy, dus het zou bij voorkeur een controle op databaseniveau (MySQL) zijn" en 'zorg ervoor dat slechts één [..] niet nul is'. Ik denk dat de beste aanpak is om een ​​trigger als deze te schrijven:

DELIMITER $$
CREATE TRIGGER check_null_insert BEFORE INSERT 
ON my_table
FOR EACH ROW BEGIN
IF CHAR_LENGTH(CONCAT_WS('', NEW.a-NEW.a, NEW.b-NEW.b, NEW.c-NEW.c)) = 1 THEN 
    UPDATE `Error: Only one value of *test_id must be not null` SET z=0;
END IF;
END$$
DELIMITER ;

Enkele trucs en overwegingen:

  1. ALS VERKLARING: Om het vervelende schrijven van vinkjes te voorkomen, is elke kolom niet leeg, terwijl andere null zijn, heb ik deze truc gedaan: Verklein elke kolom tot één teken en controleer hoeveel tekens er zijn. Let daar op NEW.a-NEW.a keert altijd 1 teken terug als NEW.a is een Integer, NULL geeft 0 tekens en de bewerking terug NULL-NULL komt terug NULL op MySQL.

  2. ERROR TRIGGERING: Ik veronderstel dat je een fout wilt melden, dus hoe dit te doen op MySQL? Je hebt de MySQL-versie niet genoemd. Enkel en alleen op MySQL 5.5 kunt u de SIGNAL-syntaxis gebruiken om een ​​uitzondering te genereren. Dus de meer draagbare manier is het afgeven van een ongeldige verklaring zoals: UPDATE xx SET z=0. Als u MySQL 5.5 gebruikt, kunt u het volgende gebruiken: signal sqlstate '45000' set message_text = 'Error: Only one value of *test_id must be not null'; in plaats van UPDATE `Error: Only one value of *test_id must be not null` SET z=0;

Ik denk ook dat je dit ook op updates wilt controleren, dus gebruik:

DELIMITER $$
CREATE TRIGGER check_null_update BEFORE UPDATE 
ON my_table
FOR EACH ROW BEGIN
IF CHAR_LENGTH(CONCAT_WS('', NEW.a-NEW.a, NEW.b-NEW.b, NEW.c-NEW.c)) = 1 THEN 
    UPDATE `Error: Only one value of *test_id must be not null` SET z=0;
END IF;
END$$
DELIMITER ;

Of maak een opgeslagen procedure en noem het.

Bijwerken

Voor databases die dit ondersteunen beperkingen controleren, de code is eenvoudiger, zie dit voorbeeld voor SQL Server:

CREATE TABLE MyTable (col1 INT NULL, col2 INT NULL, col3 INT NULL);
GO

ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK (
  LEN(CONCAT(col1-col1, col2-col2, col3-col3)) = 1
)
GO

6
2017-12-03 14:06