How to Add Table to Your Existing Database

[Rafadr13]

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.

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

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

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.

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.

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.

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

Our next topic is on how to create a relationship.

Back to Visual Basic .NET 2008 Tutorial.