SQL Server/T-SQL Tutorial/Table/ALTER TABLE
Содержание
- 1 ALTER data size for a COLUMN
- 2 Altering a Table: A Basic Form
- 3 Altering a Table: Enhanced Form
- 4 Alter table to add a constraint based on IN function
- 5 Alter table to add column
- 6 Alter table to drop a column
- 7 Change the length of the emp_name column in the employee table from varchar(30) to varchar(50)
- 8 Create a Table Index
- 9 Creating a Computed Column in alter table command
- 10 drop column syntax
- 11 Each declarative integrity constraint can be removed using the DROP clause of the ALTER TABLE statement.
- 12 Expanded nvarchar(20) to nvarchar(300)
- 13 The basic syntax of the ALTER TABLE statement
- 14 The DROP COLUMN clause provides the ability to drop an existing column of the table.
- 15 Use ALTER TABLE statement to define the foreign key of a table.
- 16 Use ALTER TABLE statement to define the primary key
ALTER data size for a COLUMN
8> CREATE TABLE department(
9> dept_no CHAR(4) NOT NULL,
10> dept_name CHAR(25) NOT NULL,
11> location CHAR(30) NULL)
12> GO
1>
2> ALTER TABLE department
3> ALTER COLUMN location CHAR(25) NOT NULL
4> GO
1>
2> drop table department;
3> GO
1>
Altering a Table: A Basic Form
ALTER TABLE table_name
ADD col_name type [NULL | IDENTITY]
[{, col_name type [NULL | IDENTITY]} ...]
DROP COLUMN col_name [{, col_name ...]
ALTER COLUMN col_name type {NULL | IDENTITY}
[{, col_name type NULL | IDENTITY} ...]
12> CREATE TABLE employee(
13> id INTEGER NOT NULL PRIMARY KEY,
14> first_name VARCHAR(10),
15> last_name VARCHAR(10),
16> salary DECIMAL(10,2),
17> start_Date DATETIME,
18> region VARCHAR(10),
19> city VARCHAR(20),
20> managerid INTEGER
21> );
22> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> ALTER TABLE employee
4> ADD telephone_no CHAR(12) NULL
5>
6>
7> select * from employee;
8> GO
id first_name last_name salary start_Date region city managerid telephone_no
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- ------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3 NULL
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4 NULL
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5 NULL
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6 NULL
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7 NULL
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8 NULL
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9 NULL
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9 NULL
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10 NULL
(9 rows affected)
1> drop table employee;
2> GO
1>
2>
Altering a Table: Enhanced Form
ALTER TABLE table_name
[WITH CHECK | NOCHECK]
ADD col_name type [{NULL | IDENTITY}]
[{, col_name type NULL | IDENTITY} ...]
DROP COLUMN col_name [{, col_name} ...]
ALTER COLUMN col_name type {NULL | IDENTITY}
[{, col_name type NULL | IDENTITY} ...]
ADD table_constraint
DROP table_constraint
16> CREATE TABLE sales
17> (order_no INTEGER NOT NULL PRIMARY KEY,
18> order_date DATETIME NOT NULL,
19> ship_date DATETIME NOT NULL)
20> GO
1>
2> ALTER TABLE sales
3> ADD CONSTRAINT order_check CHECK(order_date <= ship_date)
4> GO
1>
2> drop table sales;
3> GO
Alter table to add a constraint based on IN function
5> CREATE TABLE MyTable (
6> MyID Int IDENTITY(1, 1) NOT NULL
7> ,MyDescription nVarChar(50) NOT NULL
8> ,Region nVarChar(10) NOT NULL
9> ,CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (MyID))
10> go
1>
2>
3> ALTER TABLE MyTable WITH NOCHECK
4> ADD CONSTRAINT ckRegion CHECK
5> (REGION IN ("PNW","SW","MT","CENTRAL","EAST","SOUTH"))
6> GO
1>
2> drop table MyTable;
Alter table to add column
4> CREATE TABLE employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> ALTER TABLE Employee
4> ADD LeadTime SmallInt Null
5> GO
1>
2> drop table employee;
3> GO
Alter table to drop a column
4> CREATE TABLE employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> ALTER TABLE Employee
4> DROP COLUMN city
5> GO
1>
2>
3> drop table employee;
4> GO
Change the length of the emp_name column in the employee table from varchar(30) to varchar(50)
15> CREATE TABLE employee
16> (
17> emp_id int NOT NULL PRIMARY KEY,
18> emp_name varchar(30) NOT NULL,
19> mgr_id int NOT NULL REFERENCES employee(emp_id)
20> )
21> GO
1>
2> ALTER TABLE employee
3> ALTER COLUMN emp_name varchar(50)
4> GO
1>
2> drop table employee;
3> GO
1>
Create a Table Index
4>
5> CREATE TABLE employee(
6> id INTEGER NOT NULL,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> ALTER TABLE Employee
5> ADD CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (ID)
6> go
1>
2>
3>
4> drop table employee;
5> GO
Creating a Computed Column in alter table command
3>
4>
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> ALTER TABLE employee
5> ADD CostPerUnit AS (salary/ID)
6>
7> SELECT *
8> FROM employee;
9>
10>
11>
12> drop table employee;
13> GO
id first_name last_name salary start_Date region city managerid CostP
erUnit
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- -----
------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
5890.0000000000000
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
2394.5000000000000
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
2226.0000000000000
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
1391.7500000000000
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
893.4000000000000
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
1076.0000000000000
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
763.5714285714285
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
529.2500000000000
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
680.3333333333333
(9 rows affected)
drop column syntax
ALTER TABLE table-name
DROP COLUMN column-name [, next-column-name]...
Each declarative integrity constraint can be removed using the DROP clause of the ALTER TABLE statement.
5> CREATE TABLE sales
6> (order_no INTEGER NOT NULL PRIMARY KEY,
7> order_date DATETIME NOT NULL,
8> ship_date DATETIME NOT NULL)
9> GO
1>
2> ALTER TABLE sales
3> ADD CONSTRAINT order_check CHECK(order_date <= ship_date)
4> GO
1>
2>
3> ALTER TABLE sales
4> DROP CONSTRAINT order_check
5> GO
1>
2> drop table sales;
3> GO
Expanded nvarchar(20) to nvarchar(300)
4>
5>
6> CREATE TABLE employee(
7> id INTEGER NOT NULL PRIMARY KEY,
8> first_name VARCHAR(10),
9> last_name VARCHAR(10),
10> salary DECIMAL(10,2),
11> start_Date DATETIME,
12> region VARCHAR(10),
13> city VARCHAR(20),
14> managerid INTEGER
15> );
16> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> ALTER TABLE employee
4> ALTER COLUMN first_name nvarchar(300) NOT NULL
5>
6> select * from employee;
7> GO
id first_name
last_name salary start_Date
region city managerid
----------- ------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------ ---------- ------------ -----------------------
---------- -------------------- -----------
1 Jason
Martin 5890.00 2005-03-22 00:00:00.000
North Vancouver 3
2 Alison
Mathews 4789.00 2003-07-21 00:00:00.000
South Utown 4
3 James
Smith 6678.00 2001-12-01 00:00:00.000
North Paris 5
4 Celia
Rice 5567.00 2006-03-03 00:00:00.000
South London 6
5 Robert
Black 4467.00 2004-07-02 00:00:00.000
East Newton 7
6 Linda
Green 6456.00 2002-05-19 00:00:00.000
East Calgary 8
7 David
Larry 5345.00 2008-03-18 00:00:00.000
West New York 9
8 James
Cat 4234.00 2007-07-17 00:00:00.000
West Regina 9
9 Joan
Act 6123.00 2001-04-16 00:00:00.000
North Toronto 10
(9 rows affected)
1>
2>
3>
4> drop table employee;
5> GO
The basic syntax of the ALTER TABLE statement
ALTER TABLE table_name [WITH CHECK|WITH NOCHECK]
{ADD new_column_name data_type [column_attributes] |
DROP COLUMN column_name |
ALTER COLUMN column_name new_data_type [NULL|NOT NULL] |
ADD [CONSTRAINT] new_constraint_definition |
DROP [CONSTRAINT] constraint_name}
The DROP COLUMN clause provides the ability to drop an existing column of the table.
5>
6> CREATE TABLE employee(
7> id INTEGER NOT NULL PRIMARY KEY,
8> first_name VARCHAR(10),
9> last_name VARCHAR(10),
10> salary DECIMAL(10,2),
11> start_Date DATETIME,
12> region VARCHAR(10),
13> city VARCHAR(20),
14> managerid INTEGER
15> );
16> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> ALTER TABLE employee
4> ADD telephone_no CHAR(12) NULL
5> GO
1>
2>
3> ALTER TABLE employee
4> DROP COLUMN telephone_no
5> GO
1>
2>
3> drop table employee;
4> GO
1>
2>
3>
Use ALTER TABLE statement to define the foreign key of a table.
4> CREATE TABLE salesman(
5> no INT NOT NULL UNIQUE,
6> fname CHAR(20) NOT NULL,
7> lname CHAR(20) NOT NULL,
8> product_no CHAR(10))
9> GO
1> CREATE TABLE product(
2> product_no CHAR(10) NOT NULL UNIQUE,
3> product_name CHAR(20) NULL,
4> price MONEY NULL)
5> GO
1> ALTER TABLE product
2> ADD CONSTRAINT prim_prod PRIMARY KEY(product_no)
3> GO
1>
2>
3> ALTER TABLE salesman
4> ADD CONSTRAINT prim_sales PRIMARY KEY (no),
5> CONSTRAINT foreign_sales FOREIGN KEY(product_no)
6> REFERENCES product
7> GO
1>
2> drop table salesman;
3> GO
1> drop table product;
2> GO
1>
Use ALTER TABLE statement to define the primary key
5> CREATE TABLE product(
6> product_no CHAR(10) NOT NULL UNIQUE,
7> product_name CHAR(20) NULL,
8> price MONEY NULL)
9> GO
1> ALTER TABLE product
2> ADD CONSTRAINT prim_prod PRIMARY KEY(product_no)
3> GO
1>
2> drop table product;
3> GO