Search code examples
sqlalter

How to ALTER TABLE to add CHECK constraint?


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


Solution

  • 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)
    );