make a column can only accept 2 values-CodePudding

One of the value I want to insert into a column named 'type' can only be 'Y' or 'N'. I'm not allowed to modify the constraint, adding check/enum to the table.

Is there anyway to modify the insert query to restrict the values to be inserted into 'type' column can only be 'Y' or 'N'? Such as using if statement?

insert into table_name(id, name, type) values(:id, :name, :type)

CodePudding user response：

Use a CASE expression:

insert into table_name(
  id,
  name,
  type
) values(
  :id,
  :name,
  CASE :type WHEN 'Y' THEN 'Y' ELSE 'N' END
);

Then regardless of what value is passed in to the :type bind variable then the type column will be set to only either 'Y' or 'N'.

However, you should be using a CHECK constraint to enforce it on the table rather than trying to implement an equivalent version in an INSERT statement (which is not actually equivalent as anyone can use an UPDATE statement to change the Y or N value to something else).