3.3 Table Creation
The most basic syntax for creating a table is shown here. A table has a name and at least one column. Each column must have a specified data type which indicates the type of data – numeric, string, date etc. There are other options that we will be using to create our product table.
create table table_name
(column_name datatype,
column_name datatype,
…
)
Its clear from the analysis that we need to model the product name, a start date and an end date. As database developers we know we will also need a unique ID. The table creation statement for our product is shown in the next listing. Copy, paste and run this SQL on your development database (see chapter 1 for setting up a development database).
create table product
(
product_id int identity primary key, product_name varchar(60) not null, start_date datetime not null )
go
The first column in our product table is called product_id, which will provide a unique ID for each record. By using the <identity> keyword in the column definition, SQL Server will ensure that each record has a unique numeric value in this column. The first record inserted will be given the value of 1, the next will be given 2 and so on.
The second column is the product_name. We have chosen a datatype of varchar(60) – this is a string datatype with a maximum of 60 characters. The data analyst has advised us that the product names are within 60 characters – some are short names and some are long names so we have used varchar instead of the fixed char datatype.
The third column is the start_date. Note that we’ve used the <not null> keyword with the this column and the product_name. This means that a value must be specified when inserting records into the table. A non-nullable product name makes sense from the business point of view – you can’t buy a nameless product. We checked with the business department about whether they wanted a nullable date - the department manager insisted on forcing value entry to avoid staff input error.