Skip to content
Advertisement

How to allow only numbers in (Oracle SQL)

I want to add a integrity constraint which only lets numeric data be added to the Unit_Price.

Create Table Invoice
(InvoiceID Varchar2(6) PRIMARY KEY NOT NULL,
Quantity number(10) NOT NULL, 
Date_of_purchase Date NOT NULL,
Unit_Price number(5,2) CHECK (ISNUMERIC(Unit_Price)) NOT NULL, 
Amount number(10) NOT NULL,
payment_method Varchar(18) CHECK (payment_method in ('Cash','Card','Paypal')) NOT NULL);

Also, I have a email column in another table, how can I make it so a @ sign is required. Furthermore, if they dont enter email then it should be ‘ ‘unknow@gmail.con’ as default?

Edit: Sorry the datatype was supposed to be varchar2 and not number, thus the need for the constraint.

Advertisement

Answer

First, unit_price IS a number. There is no need to check that is is a number; the data type takes care of that.

The basic definition for email would be:

email varchar2(255) default 'unknown@gmail.com' check (email like '%@%.%')

Here is a db<>fiddle.

I strongly advise you to not use a default like that, though. NULL makes much more sense in this situation.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement