In SQL server, ALTER TABLE statement is used for the following purposes\u00a0<\/p>\n\n\n\n
Syntax to add columns and constraints while creating column<\/p>\n\n\n\n
ALTER TABLE tableName \nADD columnName1 datatype, \ncolumnName2 datatype CONSTRAINT constraintName constraintType<\/pre>\n\n\n\nSyntax to add column(s)<\/p>\n\n\n\n
ALTER TABLE tableName \nADD columnName1 datatype, columnName2 datatype<\/pre>\n\n\n\nOne or more columns can be added at once using the above syntax. <\/p>\n\n\n\n
Syntax to add constraint(s) to the existing column<\/p>\n\n\n\n
ALTER TABLE tableName \nADD CONSTRAINT constraintName1 constraintType, CONSTRAINT constraintName2 constraintType<\/pre>\n\n\n\nconstraintType – Type of constraints like Primary Key, Default, etc.<\/p>\n\n\n\n
To add constraint(s) to the existing column, use the ADD CONSTRAINT keyword in the ALTER TABLE statement.<\/p>\n\n\n\n
Points to note when adding columns and constraints:<\/strong><\/p>\n\n\n\n
- non-nullable columns can be added only when the table is empty. <\/li>
- To add a non-nullable column when the table isn’t empty, the column must be added with a default constraint like below<\/li><\/ul>\n\n\n\n
ALTER TABLE tableName \nADD columnName DATETIME NOT NULL \nCONSTRAINT DF_tableName_columnName DEFAULT GETDATE()<\/pre>\n\n\n\n
- When adding a nullable column with a default constraint and the table isn’t empty, define default constraint WITH VALUES to update the column in the existing rows in the table with default values.<\/li><\/ul>\n\n\n\n
ALTER TABLE tableName \nADD columnName DATETIME NOT NULL \nCONSTRAINT DF_tableName_columnName DEFAULT GETDATE() WITH VALUES<\/pre>\n\n\n\nLet us see some of the examples to understand this better<\/p>\n\n\n\n
ALTER TABLE tblPlayer \nADD battingStyle VARCHAR(10), modifiedDate DATETIME<\/pre>\n\n\n\nThe above query adds the ‘battingStyle’ and ‘modifiedDate’ columns to the table ‘tblPlayer’. Both the columns are NULL by default. <\/p>\n\n\n\n
ALTER TABLE tblPlayer \nADD CONSTRAINT DF_tblPlayer_modifiedDate DEFAULT GETDATE() FOR modifiedDate<\/pre>\n\n\n\nThe above query adds the default constraint for the column ‘modifiedDate’. <\/p>\n\n\n\n
ALTER TABLE tblPlayer \nADD battingStyle VARCHAR(10), modifiedDate DATETIME CONSTRAINT DF_tblPlayer_modifiedDate DEFAULT GETDATE() WITH VALUES<\/pre>\n\n\n\nThe above query adds the columns ‘battingStyle’ and ‘modifiedDate’ to the table ‘tblPlayer’. It also adds the default constraint to the column ‘modifiedDate’. <\/p>\n\n\n\n
DROP columns\/constraints<\/strong><\/h2>\n\n\n\n
Similar to adding a new column, the ALTER TABLE statement is also used to DROP the columns and constraints. A column can’t be dropped when it has any constraints or indexes. Before dropping the column, drop all the constraints first.<\/p>\n\n\n\n
Syntax to drop constraints and columns<\/p>\n\n\n\n
ALTER TABLE tableName \nDROP CONSTRAINT constraintName, COLUMN columnName<\/pre>\n\n\n\nSyntax to drop column(s) and constraint(s) in a separate queries<\/p>\n\n\n\n
ALTER TABLE tableName \nDROP COLUMN columnName<\/pre>\n\n\n\nALTER TABLE tableName \nDROP CONSTRAINT constraintName<\/pre>\n\n\n\nNow, let us see some examples for the above syntaxes to understand.<\/p>\n\n\n\n
ALTER TABLE tblPlayer \nDROP CONSTRAINT DF_tblPlayer_modifiedDate<\/pre>\n\n\n\nThe above query drops the default constraint ‘DF_tblPlayer_modifiedDate’ on the column ‘modifiedDate’. <\/p>\n\n\n\n
ALTER TABLE tblPlayer \nDROP COLUMN modifiedDate <\/pre>\n\n\n\nThe above query drops the column ‘modifiedDate’ from the table ‘tblPlayer’. <\/p>\n\n\n\n
ALTER columns<\/strong><\/h2>\n\n\n\n
In ALTER TABLE statement, ALTER COLUMN keyword is mainly used to,<\/p>\n\n\n\n
- Modify the data type of a column.\u00a0<\/li>
- Change the size of a column.\u00a0\u00a0<\/li>
- Change NULL to NOT NULL or vice versa.\u00a0<\/li><\/ul>\n\n\n\n
It is not possible<\/strong> to change the column name using ALTER TABLE statement.<\/p><\/div><\/div>\n\n\n\n
Syntax to alter column in a table<\/p>\n\n\n\n
ALTER TABLE tableName \nALTER COLUMN columnName datatype <\/pre>\n\n\n\nHere are some different cases of using Alter column statement<\/p>\n\n\n\n
ALTER TABLE tblPlayer \nALTER COLUMN battingStyle VARCHAR(15) <\/pre>\n\n\n\nThe above query increases the size of ‘battingStyle’ column from 10 to 15. <\/p>\n\n\n\n
ALTER TABLE tblPlayer \nALTER COLUMN battingStyle VARCHAR(15) NOT NULL<\/pre>\n\n\n\nThe query alters the ‘battingStyle’ column from NULL to NOT NULL.<\/p>\n\n\n\n
ALTER TABLE tblPlayer \nALTER COLUMN battingStyle NVARCHAR(15)<\/pre>\n\n\n\nAbove query alters the datatype of ‘battingStyle’ column from VARCHAR to NVARCHAR. <\/p>\n\n\n\n
Here are some important points to consider while using ALTER statement<\/strong><\/p>\n\n\n\n
- A column can’t be dropped if it has constraints or indexes. <\/li>
- Primary key or foreign key column can’t be altered. <\/li>
- Changing the primary key column from NOT NULL to NULL is not allowed. <\/li>
- The previous data type must be implicitly convertible to the new data type. <\/li>
- Altering the size of a column when the table isn’t empty, the new size should be greater than the current size. <\/li>
- The current data type of the column must be implicitly convertible to the new data type. <\/li>
- A column with a timestamp data type can’t be altered. <\/li><\/ul>\n\n\n\n
To alter a NULL column to NOT NULL when the table isn’t empty, update the null values with some values and alter the column as NOT NULL. <\/p><\/div><\/div>\n\n\n\n
UPDATE tableName SET columnName = ' ' WHERE columnName IS NULL; \nALTER TABLE tableName ALTER COLUMN columnName VARCHAR(20) NOT NULL;<\/pre>\n\n\n\nENABLE\/ DISABLE triggers<\/strong><\/h2>\n\n\n\n
ALTER TABLE statement can also be used to enable and disable a trigger or all triggers in a table. <\/p>\n\n\n\n
Syntax to enable a trigger(s) in a table<\/p>\n\n\n\n
--Enable a trigger\r\nALTER TABLE tableName\r\nENABLE TRIGGER triggerName;\r\n\r\n--Enable All triggers\r\nALTER TABLE tableName\r\nENABLE TRIGGER ALL;\r\n<\/pre>\n\n\n\nSyntax to disable a trigger(s) in a table<\/p>\n\n\n\n
--Disable a trigger\r\nALTER TABLE tableName\r\nDISABLE TRIGGER triggerName\r\n\r\n--Disable All triggers\r\nALTER TABLE tableName\r\nDISABLE TRIGGER ALL<\/pre>\n\n\n\nAlso, Check the article on CREATE TABLE<\/a><\/p>\n\n\n\n
References: ALTER TABLE (Transact-SQL)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
In SQL server, ALTER TABLE statement is used for the following purposes\u00a0 ADD columns\/ constraints in the existing table.\u00a0 DROP columns\/ constraints in the existing table.\u00a0 ALTER columns in the existing table.\u00a0 ENABLE\/ DISABLE triggers. ADD columns\/constraints Syntax to add columns and constraints while creating column Syntax to add column(s) One or more columns can\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[10,15],"tags":[],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/251"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=251"}],"version-history":[{"count":13,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/251\/revisions"}],"predecessor-version":[{"id":601,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/251\/revisions\/601"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}