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.