How to Add Table to Your Existing Database

Submitted by admin on Sunday, October 31, 2010 - 22:27.

In my previous tutorial I discuss on “How to Create Database in SQL Server 2005 Express Edition”. This time you will learn how to add a table in your “LibSys” database using SQL Server Management Studio.

Since LibSys database may contain several tables, I will only teach you to create at least one table and the rest will be done by executing a script.

1. Now open your SQL Management Studio Tools and expand the database called “LibSys” under the Object Explorer.

Object Explorer

2. To create tables right click on “Table” under your database.

New Table

3. Next type the following fields to create a table called “Books”.

Add New Table

On the BookID column properties set the “(Is Identity)” to Yes. To create BookID as the Primary Key right click on it and click “Set Primary Key”.

4. After you type all the fields needed for this table click the save button at the toolbar.

Save Table

Sine you know already how to create and save a table into your database using Management Studio Tools, now let’s add the rest of the table by simply executing the script.

Please follow the steps below.

1. If you have multiple databases, be sure to select “LibSys”.
2. Click “New Query”.
3. Paste the code.
4. Click Execute.

Add Table using Script

Here’s the code needed to create the rest of the table of LibSys.

Borrow Table

USE [LibSys] GO /****** Object: Table [dbo].[Borrow] Script Date: 10/31/2010 21:38:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Borrow]( [BorrowID] [int] NOT NULL, [MemberID] [int] NULL, [DateBorrowed] [datetime] NULL, CONSTRAINT [PK_Borrow] PRIMARY KEY CLUSTERED ( [BorrowID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

Borrow Details Table

USE [LibSys] GO /****** Object: Table [dbo].[BorrowDetails] Script Date: 10/31/2010 21:38:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[BorrowDetails]( [BorrowDetailID] [int] IDENTITY(1,1) NOT NULL, [BorrowID] [int] NULL, [BookID] [int] NULL, CONSTRAINT [PK_BorrowDetails] PRIMARY KEY CLUSTERED ( [BorrowDetailID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[BorrowDetails] WITH CHECK ADD CONSTRAINT [FK_BorrowDetails_Borrow] FOREIGN KEY([BorrowID]) REFERENCES [dbo].[Borrow] ([BorrowID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[BorrowDetails] CHECK CONSTRAINT [FK_BorrowDetails_Borrow] GO

Course Table

USE [LibSys] GO /****** Object: Table [dbo].[Course] Script Date: 10/31/2010 21:38:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Course]( [CourseID] [int] IDENTITY(1,1) NOT NULL, [CourseTitle] [nvarchar](50) NULL, [CourseDescription] [nvarchar](50) NULL, CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ( [CourseID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

Members Table

USE [LibSys] GO /****** Object: Table [dbo].[Members] Script Date: 10/31/2010 21:38:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Members]( [MemberID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL, [Address] [nvarchar](50) NULL, [CourseID] [int] NULL, [PhoneNo] [nvarchar](50) NULL, [BirthDate] [datetime] NULL, CONSTRAINT [PK_Members] PRIMARY KEY CLUSTERED ( [MemberID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

Return Table

USE [LibSys] GO /****** Object: Table [dbo].[Return] Script Date: 10/31/2010 21:38:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Return]( [ReturnID] [int] NOT NULL, [MemberID] [int] NULL, [DateReturned] [datetime] NULL, CONSTRAINT [PK_Return] PRIMARY KEY CLUSTERED ( [ReturnID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

Return Details Table

USE [LibSys] GO /****** Object: Table [dbo].[ReturnDetails] Script Date: 10/31/2010 21:38:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ReturnDetails]( [ReturnDetailID] [int] IDENTITY(1,1) NOT NULL, [ReturnID] [int] NULL, [BookID] [int] NULL, CONSTRAINT [PK_ReturnDetails] PRIMARY KEY CLUSTERED ( [ReturnDetailID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

Subject Table

USE [LibSys] GO /****** Object: Table [dbo].[Subject] Script Date: 10/31/2010 21:39:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Subject]( [SubjectID] [int] IDENTITY(1,1) NOT NULL, [Subject] [nvarchar](50) NULL, CONSTRAINT [PK_Subject] PRIMARY KEY CLUSTERED ( [SubjectID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

After you finish adding all the necessary tables, right click on the Tables node and click Refresh.

Refresh Table Node

And see if you have the same number of tables below.

Tables

Our next topic is on how to create a relationship.

Back to Visual Basic .NET 2008 Tutorial.

Tags

Add new comment

About text formats
  • 123 views