I want to ALTER my already created ZIPCODE table so that the sales_tax_applied field can never have a negative value. Here is the code for my already created table:
CREATE TABLE ZIPCODE
(
city VARCHAR(50) NOT NULL,
state CHAR(2) NOT NULL,
zipcode VARCHAR(20) NOT NULL,
sales_tax_applied DECIMAL(10,4) NOT NULL,
PRIMARY KEY (zipcode)
);
What is the code for me to be able to do this? NOTE: all fields must be NOT NULL.
Thank you
You would do:
alter table zipcode add constraint chk_zipcode_sales_tax_applied
check (sales_tax_applied >= 0);
You can also add this into the create table statement in multiple ways, such as:
CREATE TABLE ZIPCODE (
city VARCHAR(50) NOT NULL,
state CHAR(2) NOT NULL,
zipcode VARCHAR(20) NOT NULL,
sales_tax_applied DECIMAL(10,4) NOT NULL,
PRIMARY KEY (zipcode),
constraint chk_zipcode_sales_tax_applied check (sales_tax_applied >= 0)
);