Creating Database Keys

[Papa_Don]

Group,

The SQL Server (I'm using the edition that comes with Visual Studio 2010 Express) is different in every aspect form the database(s) that existed in the UNIX based program I work with over the last 15 years. There is much to learn and understand. With that said, I've connected to those databases using EXCEL and have had to link several tables to garner the information that I need. I have created 2 of the many tables I'm going to need for a Sales and Inventory program I'm trying to build. I know that these two table will need to be linked via "keys".

My questions are:

1) Can you create multiple keys within a database table? If so, how is this done? It is my belief that I will need these multiple keys to allow 1 database to link with several different databases.
2) When creating these keys, how is it you decide which is is to be "Primary"?
3) When setting up the "column" for the key, what "Data Type" is used?
4) Are these keys meant to hold data? If so, what kind of data?

Forgive my ignorance on this subject. In the classes I've taken on Visual Basic, we did touch on databases but didn't spend anywhere near enough time on the details. More specifically, we didn't discuss how to setup database tables. I really am a "newbie" but I'm really enjoying the learning. I've garnered so much information for this forum and really appreciate all who have contributed.

I look forward to hearing from each of you.

Don

[Reverend Jim]

When you create a table, by default it has no Primary Key. When the records get inserted they appear, physically, in the order in which they are added. It is possible to have duplicate records (generally not a good idea). When you want to retrieve a record, because the table is unsorted, it can take a long time to locate the requested record. When you designate a field (or fields) as a primary key, records are sorted on that key. When you retrieve recordswithoout specifying a sort order, the records are returned in the order in which they appear in the table (sorted by primary key). Retrieving a record based on that key is fast (like using the table of contents in a book to go to a particular chapter). You can also designate another field as an index for other frequently used sorts and searches. However, each addition index requires the creation of supplementary data structures in the database so there is a tradeoff between speed and size.

So a primary key is like having a table of contents. It forces the records to be stored in a particular order. An additional index is like having an index at the back of the book except you need a separate index for each additional column designated as an index. Another term you might come across for primary key is clustered index Any other defined index is non-clustered.

To create a table containing last and first names you could do

CREATE TABLE [dbo].[Table_1](
    [LastName] [varchar](50) NOT NULL,
    [FirstName] [varchar](50) NOT NULL
) ON [PRIMARY]

This would have no key or index. To designate LastName as the primary key you would have to do

CREATE TABLE [dbo].[Table_1](
    [LastName] [varchar](50) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [LastName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The clause IGNORE_DUP_KEY = OFF says that if you try to insert two records with the same LastName you will get an error. The easiest way to manage your databases and tables is to install the free SQL Server Management Studio from Microsoft. Instructions on how to do that can be found here