如何为transaction表添加事务类型非空与枚举约束及正确添加字段？

It looks like your current ALTER statement is adding a CUSTOMER column, which isn't aligned with your goal of restricting the transaction_type field. Let's fix that with the correct constraints to meet your requirements:

Key Requirements Recap #

• transaction_type cannot be empty (NOT NULL constraint)
• transaction_type can only be either 'Deposit' or 'Withdrawal' (CHECK constraint)

Scenario 1: The transaction_type column already exists #

If the column is already present in your transaction table, run these two commands:

1. First, enforce the NOT NULL constraint:

ALTER TABLE transaction MODIFY transaction_type VARCHAR2(20) NOT NULL;

(Adjust the VARCHAR2(20) length to match your existing column's datatype if needed)

2. Then add a CHECK constraint to restrict allowed values:

ALTER TABLE transaction ADD CONSTRAINT chk_transaction_type 
CHECK (transaction_type IN ('Deposit', 'Withdrawal'));

Optional Case-Insensitive Check: If you want to accept values like 'deposit' or 'WITHDRAWAL' (case variations), modify the constraint to use UPPER():

ALTER TABLE transaction ADD CONSTRAINT chk_transaction_type 
CHECK (UPPER(transaction_type) IN ('DEPOSIT', 'WITHDRAWAL'));

Scenario 2: You need to add the transaction_type column from scratch #

If the column doesn't exist yet, you can add it with both constraints in a single statement:

ALTER TABLE transaction ADD transaction_type VARCHAR2(20) 
NOT NULL 
CONSTRAINT chk_transaction_type CHECK (transaction_type IN ('Deposit', 'Withdrawal'));

Verify Your Constraints #

To confirm the constraints are applied correctly, run this query:

SELECT constraint_name, constraint_type 
FROM user_constraints 
WHERE table_name = 'TRANSACTION';

You should see a C (CHECK) constraint named CHK_TRANSACTION_TYPE and a NN (NOT NULL) constraint for the transaction_type column.

内容的提问来源于stack exchange，提问作者Rasika Dewangan