Loading

How to Add Table to Your Existing Database

Submitted by: 
Visitors have accessed this post 12675 times.


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

  1. USE [LibSys]
  2. GO
  3.  
  4. /****** Object: Table [dbo].[Borrow] Script Date: 10/31/2010 21:38:10 ******/
  5. SET ANSI_NULLS ON
  6. GO
  7.  
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10.  
  11. CREATE TABLE [dbo].[Borrow](
  12. [BorrowID] [INT] NOT NULL,
  13. [MemberID] [INT] NULL,
  14. [DateBorrowed] [datetime] NULL,
  15. CONSTRAINT [PK_Borrow] PRIMARY KEY CLUSTERED
  16. (
  17. [BorrowID] ASC
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  19. ) ON [PRIMARY]
  20.  
  21. GO

Borrow Details Table

  1. USE [LibSys]
  2. GO
  3.  
  4. /****** Object: Table [dbo].[BorrowDetails] Script Date: 10/31/2010 21:38:20 ******/
  5. SET ANSI_NULLS ON
  6. GO
  7.  
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10.  
  11. CREATE TABLE [dbo].[BorrowDetails](
  12. [BorrowDetailID] [INT] IDENTITY(1,1) NOT NULL,
  13. [BorrowID] [INT] NULL,
  14. [BookID] [INT] NULL,
  15. CONSTRAINT [PK_BorrowDetails] PRIMARY KEY CLUSTERED
  16. (
  17. [BorrowDetailID] ASC
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  19. ) ON [PRIMARY]
  20.  
  21. GO
  22.  
  23. ALTER TABLE [dbo].[BorrowDetails] WITH CHECK ADD CONSTRAINT [FK_BorrowDetails_Borrow] FOREIGN KEY([BorrowID])
  24. REFERENCES [dbo].[Borrow] ([BorrowID])
  25. ON DELETE CASCADE
  26. GO
  27.  
  28. ALTER TABLE [dbo].[BorrowDetails] CHECK CONSTRAINT [FK_BorrowDetails_Borrow]
  29. GO

Course Table

  1. USE [LibSys]
  2. GO
  3.  
  4. /****** Object: Table [dbo].[Course] Script Date: 10/31/2010 21:38:31 ******/
  5. SET ANSI_NULLS ON
  6. GO
  7.  
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10.  
  11. CREATE TABLE [dbo].[Course](
  12. [CourseID] [INT] IDENTITY(1,1) NOT NULL,
  13. [CourseTitle] [nvarchar](50) NULL,
  14. [CourseDescription] [nvarchar](50) NULL,
  15. CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
  16. (
  17. [CourseID] ASC
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  19. ) ON [PRIMARY]
  20.  
  21. GO

Members Table

  1. USE [LibSys]
  2. GO
  3.  
  4. /****** Object: Table [dbo].[Members] Script Date: 10/31/2010 21:38:41 ******/
  5. SET ANSI_NULLS ON
  6. GO
  7.  
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10.  
  11. CREATE TABLE [dbo].[Members](
  12. [MemberID] [INT] IDENTITY(1,1) NOT NULL,
  13. [Name] [nvarchar](50) NULL,
  14. [Address] [nvarchar](50) NULL,
  15. [CourseID] [INT] NULL,
  16. [PhoneNo] [nvarchar](50) NULL,
  17. [BirthDate] [datetime] NULL,
  18. CONSTRAINT [PK_Members] PRIMARY KEY CLUSTERED
  19. (
  20. [MemberID] ASC
  21. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  22. ) ON [PRIMARY]
  23.  
  24. GO

Return Table

  1. USE [LibSys]
  2. GO
  3.  
  4. /****** Object: Table [dbo].[Return] Script Date: 10/31/2010 21:38:47 ******/
  5. SET ANSI_NULLS ON
  6. GO
  7.  
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10.  
  11. CREATE TABLE [dbo].[RETURN](
  12. [ReturnID] [INT] NOT NULL,
  13. [MemberID] [INT] NULL,
  14. [DateReturned] [datetime] NULL,
  15. CONSTRAINT [PK_Return] PRIMARY KEY CLUSTERED
  16. (
  17. [ReturnID] ASC
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  19. ) ON [PRIMARY]
  20.  
  21. GO

Return Details Table

  1. USE [LibSys]
  2. GO
  3.  
  4. /****** Object: Table [dbo].[ReturnDetails] Script Date: 10/31/2010 21:38:56 ******/
  5. SET ANSI_NULLS ON
  6. GO
  7.  
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10.  
  11. CREATE TABLE [dbo].[ReturnDetails](
  12. [ReturnDetailID] [INT] IDENTITY(1,1) NOT NULL,
  13. [ReturnID] [INT] NULL,
  14. [BookID] [INT] NULL,
  15. CONSTRAINT [PK_ReturnDetails] PRIMARY KEY CLUSTERED
  16. (
  17. [ReturnDetailID] ASC
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  19. ) ON [PRIMARY]
  20.  
  21. GO

Subject Table

  1. USE [LibSys]
  2. GO
  3.  
  4. /****** Object: Table [dbo].[Subject] Script Date: 10/31/2010 21:39:03 ******/
  5. SET ANSI_NULLS ON
  6. GO
  7.  
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10.  
  11. CREATE TABLE [dbo].[Subject](
  12. [SubjectID] [INT] IDENTITY(1,1) NOT NULL,
  13. [Subject] [nvarchar](50) NULL,
  14. CONSTRAINT [PK_Subject] PRIMARY KEY CLUSTERED
  15. (
  16. [SubjectID] ASC
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  18. ) ON [PRIMARY]
  19.  
  20. 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.




Add new comment

(If you're a human, don't change the following field)
Your first name.
(If you're a human, don't change the following field)
Your first name.

Filtered HTML

  • You may insert videos with [video:URL]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
To prevent automated spam submissions leave this field empty.