BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
SQL doesn’t just select data; you can also use it to create tables and link them together with relationships. This blog shows you how!
- Creating Tables in SQL
- Creating Databases using SQL
- Creating a Table Programmatically in SQL (this blog)
- Linking Tables (Foreign Keys and Relationships)
This blog is part of a much longer tutorial on programming in SQL. Alternatively, have a look at our classroom-based training courses in SQL.
Posted by Andy Brown on 05 October 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Creating a Table Programmatically in SQL
If you want complicated syntax structures, you can get these from the Microsoft website; this blog concentrates on practical examples.
Creating Simple Tables
To create a simple table, just list out all the columns that you want to create, giving each a name and a data type:
-- create the table, giving it a name
CREATE TABLE tblAuthor (
-- list all the fields (name and type)
FirstName as varchar(100),
LastName as varchar(100)
)
You can see a separate blog on the possible data types that you can use, but here are some of the main ones:
| What you're storing | Use | Notes |
|---|---|---|
| Any string of text | varchar(n) | A string of text with up to n characters. |
| Any whole number | int | Any whole number (eg 42). |
| Any other number | float | Any other type of number (eg 3.14). |
| Any date | date | Any date |
| Any yes/no value | bit | Either 0 (False) or 1 (True). |
Setting a Primary Key
You can set which column in a table is the primary key using the PRIMARY KEY key words, and make this automatically number rows using IDENTITY:
CREATE TABLE tblAuthor (
-- AuthorId field is primary key
AuthorId int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100)
)
-- type in a couple of authors
INSERT INTO tblAuthor(
FirstName, LastName
) VALUES (
'Barbara','Kingsolver'
)
INSERT INTO tblAuthor(
FirstName, LastName
) VALUES (
'Ian','Banks'
)
-- show the results
SELECT * FROM tblAuthor
What the above code would do is to create and show the following rows:
The rows returned by the above SQL.
Notice that because we've used the IDENTITY keyword, there is no need to specify the AuthorId field when inserting records - its value will be generated automatically, beginning at 1 and incrementing by 1 for each new row.
Setting Default Values for Columns
You can specify for any column that it should take a default value if not specifically set. Here we set the date added column to default to today's date:
CREATE TABLE tblAuthor (
AuthorId int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100),
-- the date when the author was added
-- will default to today's date
DateAdded datetime DEFAULT GetDate()
)
If you insert rows into the table, you can choose whether or not to set a value for the DateAdded column:
-- first author defaults to today's date
INSERT INTO tblAuthor(
FirstName, LastName
) VALUES (
'Barbara','Kingsolver'
)
-- for next author, we'll set date explicitly
INSERT INTO tblAuthor(
FirstName, LastName, DateAdded
) VALUES (
'Ian','Banks', '01/01/2012'
)
-- show the results
SELECT * FROM tblAuthor
The results of running this query will look like this (assuming "today" is 05/10/2012):
The date has been explicitly set for the second author.
Preventing Null Values for Columns
In our simple authors table, it's likely that we'll want to ensure the last name is always filled in. You can do this by using the NOT NULL keywords:
CREATE TABLE tblAuthor (
AuthorId int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
-- make sure last name is filled in
LastName varchar(100) NOT NULL,
DateAdded datetime DEFAULT GetDate()
)
If you then insert a row, you'll get an error if you don't specify the last name, but the first name can still be null:
-- first author defaults to today's date
INSERT INTO tblAuthor(
LastName
) VALUES (
'Kingsolver'
)
-- show the results
SELECT * FROM tblAuthor
Thus the SQL above would create one row, with the first name appearing as NULL:
The results of running the SQL above.
Adding Constraints to a Column
Sometimes you want to ensure that a column in a table will only accept certain values (for our example, we might want to add a rating, which must be between 0 and 10). You can do this by adding a constraint to the table.
To show how this works, let's first create a table containing a Rating field:
CREATE TABLE tblAuthor (
AuthorId int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100),
-- rating must be an integer
Rating int
)
Now we'll add in an author with a rating outside the permissible bounds:
-- add in an author
INSERT INTO tblAuthor(
FirstName,LastName, Rating
) VALUES (
'Barbara', 'Kingsolver', 11
)
To add a constraint, we have to alter the definition of the table:
-- now check rating lies between 0 and 10
ALTER TABLE tblAuthor WITH NOCHECK
ADD CONSTRAINT tblAuthorCheckRating
CHECK (Rating>=0 and Rating <>
A couple of notes about this:
- You have to call the constraint something. Here we've called it tblAuthorCheckRating to ensure that this name won't be used elsewhere in the database.
- We need the words WITH NOCHECK to ensure that the constraint isn't applied to existing data (which would fail the check).
If we try adding another author with rating 11 now ...
-- next time try adding rating of 11 ...
INSERT INTO tblAuthor(
FirstName,LastName, Rating
) VALUES (
'Ian', 'Banks', 11
)
-- show the results
SELECT * FROM tblAuthor
... we'll generate an error, and the row won't be added:
The error you'll see if you run the SQL above.
Indexing Columns
You can index a column to speed up sorting and searching by it. For our authors table, it's likely that we'll frequently want to sort authors by their last name, so we'll index this field:
CREATE TABLE tblAuthor (
AuthorId int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100),
)
-- index the LastName field
CREATE INDEX ix_tblAuthor_LastName
ON tblAuthor(LastName)
The index name must be unique over all tables, which is why we've used the naming convention ix_TableName_ColumnName.
- Creating Tables in SQL
- Creating Databases using SQL
- Creating a Table Programmatically in SQL (this blog)
- Linking Tables (Foreign Keys and Relationships)