Alter Table

An ALTER statement is a DDL T-SQL that modifies a table definition. You can use an ALTER statement to:

  • Add/drop columns/constraints/triggers
  • Change column definition, datatype, maximum length

Syntax

ALTER TABLE <table_name>
[ALTER | ADD | DROP] [ <column_name> COLUMN | CONSTRAINT] datatype;

Before you begin

--Use the Hasura database
USE HASURA;
GO
--Create a new table
CREATE TABLE EMPLOYEE (
EMP_ID INT PRIMARY KEY,
EMP_NAME VARCHAR(40) NOT NULL,
DEPT_ID INT
)
GO
--Retrieve all rows from the table
SELECT * FROM EMPLOYEE;

Add a new column

--Add a new column named 'PROJECT' with datatype varchar(5).
ALTER TABLE EMPLOYEE ADD PROJECT VARCHAR(5);

Add a new column with a constraint

ALTER TABLE EMPLOYEE ADD EMAIL VARCHAR(20) NULL
CONSTRAINT email_unique UNIQUE ;

Modify column definition

--Increase the string limit from 5 to 10
ALTER TABLE EMPLOYEE ALTER COLUMN PROJECT VARCHAR(10) NULL;
--Change the data type of the column from 'INT' to 'DECIMAL'
ALTER TABLE EMPLOYEE ALTER COLUMN DEPT_ID DECIMAL(5,2);

DROP column

ALTER TABLE EMPLOYEE DROP COLUMN PROJECT;

DROP CONSTRAINT

ALTER TABLE EMPLOYEE DROP CONSTRAINT email_unique;

What next

Use the sp_help stored procedure to view the table definition.

sp_help 'EMPLOYEE'
Did you find this page helpful?
Start with GraphQL on Hasura for Free
  • ArrowBuild apps and APIs 10x faster
  • ArrowBuilt-in authorization and caching
  • Arrow8x more performant than hand-rolled APIs
Promo
footer illustration
Brand logo
© 2024 Hasura Inc. All rights reserved
Github
Titter
Discord
Facebook
Instagram
Youtube
Linkedin