Skip to main content
S
shubhamy0001
August 20, 2021
Question

Check constraint in appian MYSQL database

  • August 20, 2021
  • 5 replies
  • 0 views

Hello,

I wonder if it is possible to apply check constraint in appian database? 

Secondly, can it be possible to update the values in a table column based on the values in second column in the same table?

    5 replies

    csteward
    csteward
    August 20, 2021

    CHECK constraint - if you have access to the database directly I don't see why you could not apply CHECK constraints where you would like, but note I am not a MySQL expert.

    Additionally, you can certainly update values in a table column based on another.  Utilize a!queryEntity() to retrieve data into your CDT, manipulate the CDT as needed, then Write to Datastore back to the DB.  More details on your use case might be helpful here however.  

      X
      xug0001
      September 6, 2021

      Hi,

               I just find appian cloud - database  cannot  add check constraint by sql. It always remind me  

             "  A new statement was found, but no delimiter between it and the previous one. (near "check" at position 84 )"

               

      So I want to know Whether can I use the check order to complete my constraint.

      vimals
      vimals
      September 9, 2021

      xug0001

      You will get this error, when the table already has some data and fails to qualify the check constraint requirements. Try to fix the data first and then create the check constraint.

      I was able to re-produce the issue by following the scripts below

      CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    PRIMARY Key (ID)
);

INSERT INTO `Persons` (`ID`, `LastName`, `FirstName`, `Age`, `City`) VALUES ('10', 'Test', 'Test', '17', 'Test');

ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18); 
-- This will throw the error message as you mentioned


-- Fix the data by using the blow query and create check constraint
UPDATE `Persons` SET `Age` = '18' WHERE `Persons`.`ID` = 10;
ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18);
       

        Terms & ConditionsAccessibility statement