时间戳

首页 > 解决方案 > 如何使一列依赖于同一表中的另一列

sql - 如何使一列依赖于同一表中的另一列

问题描述

我正在创建一个新的数据库表，我想让一列依赖于另一列。

我试图添加一些检查约束


CREATE TABLE borrower (
   borrower_salary_id INT REFERENCES borrower_salary(id),
   borrower_additional_income BOOLEAN NOT NULL,
   borrower_additional_income_amount INT,
   borrower_previous_loans BOOLEAN NOT NULL,
   borrower_previous_loans_payment_delay BOOLEAN,
);

当且仅当borrower_additional_income 为真时，borrower_additional_income_amount 应该包含一些值我该怎么做？

标签： sqlpostgresql

解决方案

check约束如下所示：

CREATE TABLE borrower (
   borrower_salary_id INT REFERENCES borrower_salary(id),
   borrower_additional_income BOOLEAN NOT NULL,
   borrower_additional_income_amount INT,
   borrower_previous_loans BOOLEAN NOT NULL,
   borrower_previous_loans_payment_delay BOOLEAN,
   CHECK ( (borrower_additional_income AND  borrower_additional_income_amount IS NOT NULL) OR
           (NOT borrower_additional_income AND borrower_additional_income_amount IS NULL )
         )
);

或简化为：

   CHECK ( borrower_additional_income =  (borrower_additional_income_amount IS NOT NULL)
         )
);

但是，问题是为什么要有一面旗帜？为什么不只检查是否borrower_additional_income_amount是或不是NULL？

推荐阅读