Monday, May 12, 2008

Creating a table in SQL server 2005

After creating a database, you need to create tables in your database.Its as simple as creating the database. Expand the database in which you want to create tables in and select the tables tab. Right click on the tables tab to get an option "New Table".

When you click the new table option, you are presented with the column creation page. In this page the column names, datatype and null or not null characters can be specified.

Although you can create columns with spaces, it will cause issues later as it cant be used in many operations. So dont leave spaces in column names.

After adding the required number of columns the table can saved by clicking on the save button.

The table is saved to the database and can be seen under the tables tab under the database you had selected.

Three types of constraints can be set on columns for restricting the entry of data into them.They are:

  1. Check Constraint - This is used to set a constraint against which data is validated before entry
  2. Default Constraint - This sets a default value to be filled in a column when no value is specified by the insert command
  3. Unique Constraint - when the unique constraint is set on a column, it cant have duplicate values.

A check constraint can be set by selecting the "Constraint" tab under the database of choice. A Check Constraint box opens up asking you to specify the Constraint expression.

Ex: (column_name like '[0-9]')

once the constraint is added, only values that match the constraint can be added to the table. In this case only values from 0 to 9 can be added to this column.

The default value can be set by selecting the table in design mode and choosing the column on which the default value has to be set. This can be done while creating the table as well. The Default value or binding can be set as below

The unique constraint can be set by running the update query to set a column as unique.

EX: Alter Table_name Add Constraint Constraint_name Unique(column_name)

No comments: