Liquibase前提条件:如何检查不可为空的列? [英] Liquibase preconditions: How do I check for a column being non-nullable?

问题描述

我有一个数据库升级脚本来删除列上的非null约束.我想进行前提条件检查，并且仅当ALTER TABLE为非null时才调用它.

I have a db upgrade script to remove the non-null constraint on a column. I want to do a precondition check, and call ALTER TABLE only when it is non-null.

master.xml脚本是一个渐进式脚本，在该脚本中，我不断添加脚本，并且每次都在运行整个脚本.第一次运行我的Alter Table脚本后，我不想再次运行它.

The master.xml script is a progressive one where I keep adding scripts and the entire thing runs everytime. After the first time my Alter Table script has run, I do not want it to be run again.

无法为此找到预定义的前提条件，也无法编写sqlcheck.

Couldn't find a predefined precondition for this, and could not write an sqlcheck either.

推荐答案

可以使用sqlCheck完成.

Can be done with sqlCheck.

• 对于MySql

• For MySql

<preConditions onFail="MARK_RAN" onError="CONTINUE">
    <sqlCheck expectedResult="NO">
        SELECT is_Nullable
        FROM  INFORMATION_SCHEMA.COLUMNS 
        WHERE table_name='<table_name>' 
        AND column_name='<column_name>' 
    </sqlCheck>   
</preConditions>

对于Oracle:

For Oracle:

<preConditions onFail="MARK_RAN" onError="CONTINUE">
    <sqlCheck expectedResult="N">
        SELECT Nullable
        FROM user_tab_columns
        WHERE table_name = '<table_name>'
        AND column_name = '<column_name>'
    </sqlCheck>
</preConditions>

对于SQL Server:

For SQL Server:

<preConditions onFail="MARK_RAN" onError="CONTINUE">
    <sqlCheck expectedResult="0">
      SELECT is_nullable 
      FROM sys.columns
      WHERE  object_id = OBJECT_ID('<table_name>')  
      AND name = '<column_name>' 
    </sqlCheck>
</preConditions>

这篇关于Liquibase前提条件:如何检查不可为空的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！