SQL Server/T-SQL Tutorial/Table/ALTER TABLE — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:22, 26 мая 2010
Содержание
- 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
<source lang="sql">
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></source>
Altering a Table: A Basic Form
<source lang="sql">
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></source>
Altering a Table: Enhanced Form
<source lang="sql">
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</source>
Alter table to add a constraint based on IN function
<source lang="sql">
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;</source>
Alter table to add column
<source lang="sql">
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</source>
Alter table to drop a column
<source lang="sql">
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</source>
Change the length of the emp_name column in the employee table from varchar(30) to varchar(50)
<source lang="sql">
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></source>
Create a Table Index
<source lang="sql">
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</source>
Creating a Computed Column in alter table command
<source lang="sql">
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)</source>
drop column syntax
<source lang="sql">
ALTER TABLE table-name
DROP COLUMN column-name [, next-column-name]...</source>
Each declarative integrity constraint can be removed using the DROP clause of the ALTER TABLE statement.
<source lang="sql">
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</source>
Expanded nvarchar(20) to nvarchar(300)
<source lang="sql">
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</source>
The basic syntax of the ALTER TABLE statement
<source lang="sql">
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}</source>
The DROP COLUMN clause provides the ability to drop an existing column of the table.
<source lang="sql">
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></source>
Use ALTER TABLE statement to define the foreign key of a table.
<source lang="sql">
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></source>
Use ALTER TABLE statement to define the primary key
<source lang="sql">
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</source>
