Archive for July, 2010
It is not very common to create a database via scripts or modify a database via scripts, most often the changes are done via a designer which does make the job a lot easier.
In some cases it is worthwhile to do it via a script one of the instances where I use scripts a lot is when I make changes to a development database and I know that I have to make same changes to a production database at some point.
Below is a list of the most common alter statements to modify the structure of a database
- Add a column to a database
ALTER TABLE table_name ADD column_name datatype
- Add a primary key to a database
ALTER TABLE table_name ADD CONSTAINT constraint_name PRIMARY KEY (field_name)
- Add an index to a table
CREATE NONCLUSTERED INDEX index_name ON table_name (field_name ASC)
- Add a foreign constraint to a table
ALTER TABLE table_name WITH CHECK ADD CONSTRAINT contraint_name FOREIGN KEY (field_name) REFERENCES table_name (field_name) ALTER TABLE table_name CHECK CONSTRAINT constraint_name
Note that there are some very good tools out there as well to determine the structural changes between a development and production database but you may not always have access to these tools.