In this post we will be explaining some basic SQL commands that will help you create the kind of tables you want to see.
Creating A Foreign Key Constraint
Creating a foreign key is similar to linking tables in Access. You may have a table called Customers and a table called Orders. Each Order requires a customer so we have a CustomerID_FK field in the Orders table where we can enter the relevant CustomerID. But we need to make sure that only existing CustomerIDs can be entered. If only have 6 customers, we do not want to see CustomerID 7 anywhere in the Orders table.
First we create the Orders table:
USE TestDB; CREATE TABLE dbo.orders ( ID INT IDENTITY , OrderDate Date, CustomerID_FK INT NOT NULL, PRIMARY KEY (ID) )
Refresh the tables by right-clicking on tables and selecting refresh.
dbo.Orders will appear. Open it up by right-clicking on it and choosing Edit Top 200 Rows. Enter the following information:
We have a CustomerID_FK 1 and 2 because we entered two Customers in the previous post (1 and 2 are the IDs of those customers). But we can enter CustomerID_FK 3 here and we don’t want to so we will add the constraint.
Create a new query and enter this code:
Use TestDB ALTER TABLE dbo.Orders ADD FOREIGN KEY (CustomerID_FK) REFERENCES Customers(ID)
In this code we are altering the CustomerID_FK column of dbo.Orders and telling it to Reference the ID field of Customers.
If we now try and enter an Order with a CustomerID of 3 we get:
Adding A Column
What do we do if we need to add a column? You guessed it. SQL!
So, we want to add a PostCode column to the Customer’s table.
Create a new query and add this code:
USE TestDB; ALTER TABLE dbo.customers ADD Address VARCHAR(255) NULL;
This code instructs SQL Server to add a column called Address to dbo.customers that uses the VARCHAR data type (similar to text in Access). We have set a limit of 255 characters for this field.
Deleting A Column
So, how do we perform the opposite action?
Again, create a new query and enter this code:
USE TestDB; ALTER TABLE dbo.customers DROP COLUMN Address;
If the command is completed successfully, the Address column will have been deleted.
Adding A Default Value To A Column
It is typical that certain columns will have a default value attached. This means that when a new record is entered a particular field will immediately contain a particular value unless it is over written. A classic example would be the OrderDate field. We don’t want to have to add today’s date in every time we create an order. We just want to tell the system: “OK, whenever I add a new record in the orders table, please add today’s date to the OrderDate field”. You don’t really need to say please. It is only a computer.
New query please:
USE TestDB ALTER TABLE dbo.orders ADD CONSTRAINT order_date_constraint DEFAULT GETDATE() FOR OrderDate
This is similar to the Foreign Key constraint above. We are saying “Please add a default value of GETDATE() to the OrderDate column of the dbo.orders table. And by the way, I want to name this constraint order_date_constraint so that if I am error checking later on, I will know what it is referring to!”
GETDATE() just returns today’s date so default can hold either a hard-coded value or a function.
We have named this constraint but we don’t need to.
If we enter a new record in dbo.orders we only have to enter the CustomerID_FK, close the datasheet and then reopen it to see that the date has been added for us:
Renaming A Column
Having named the Column CustomerName you have now decided that you want to shorten it to CName. This is not uncommon and it is worth knowing how to achieve this through SQL.
As per, create a new query and enter this code:
USE TestDB Go SP_RENAME 'dbo.Customers.[CustomerName]' , 'CName', 'COLUMN' Go
If you click on Edit Top 200 Rows, you will see that the CustomerName field has changed to CName.
Above we have listed some SQL Statements that will help you to create and alter tables in SQL. It is now time to mention that most of the commands can be carried out through the Table Designer! The Table Designer is much easier to use but you won’t learn SQL in quite the same way so you may want to persevere with the code.
The Table Designer
Locate the table you wish to change in the Object Explorer, right-click and select Design:
You will the be presented with the table in Design View:
You can then modify the name, data type, default values, constraints, etc.
As stated above, this is a much simpler way of dealing with table changes but you won’t have the understanding of SQL that will come with writing code.
