CODEWITHSUNDEEP
sqloracle-database
Henry Bai
Henry Bai

Reputation: 447

make a column can only accept 2 values

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)

Upvotes: 0

Views: 77

Answers (1)

MT0
MT0

Reputation: 168232

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).

Upvotes: 2

Related Questions