help with multiple table insert


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: help with multiple table insert

  1. #1
    Join Date
    Jan 2007
    Posts
    5

    Question help with multiple table insert

    i hav 2 tables Field StudentID is in both table Student and StudentSchedules.StudentID is a primary key in table Student and becomes foreign key in table StudentSchedules(StuddentID is not auto increment). How can i populate both table at the same time? i'm using asp.net and would like to create some texboxes to insert these data. So that a student may be associated with many classeSchedules. Or is there another way to do it?

    CREATE TABLE [Student] (
    [StudentID] VARCHAR(40) NOT NULL,
    [Name] VARCHAR(40),
    CONSTRAINT [PK_Student] PRIMARY KEY ([StudentID])
    )

    CREATE TABLE [StudentSchedules] (
    [ClassID] INTEGER IDENTITY(0,1) NOT NULL,
    [ClassStatus] VARCHAR(40),
    [StudentID] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_StudentSchedules] PRIMARY KEY ([ClassID], [StudentID])
    )

    ALTER TABLE [StudentSchedules] ADD CONSTRAINT [Student_StudentSchedules]
    FOREIGN KEY ([StudentID]) REFERENCES [Student] ([StudentID]) ON DELETE CASCADE ON UPDATE CASCADE
    Last edited by AzlanAziz; 01-08-2007 at 12:47 AM.

  2. #2
    Join Date
    Dec 2006
    Posts
    13
    You don't need to populate both tables at the same time. A foreign key requires the value to exist in the referenced table, just that. So you can add a record to table Student without having complaints from the DB, and use the StudentID value for a successive insert to table StudentSchedules.

    So, you can, for example, create the Student in a "Student creation" option in the program, and somewhere else provide an "Assign schedule" option that allows the user to add schedules to a student that has already been added.

    However, if the need arises to modify two tables that are bound by foreign key constraints without having exceptions thrown back at you, some DBMSs (such as PostgreSQL) provide a "delayed constraint check" that allows you to modify the referenced table and later update the reference without having a constraint violation exception (this however usually needs to be done inside a single transaction).

    Cheers.

Similar Threads

  1. how to clone database using mysql
    By zarien in forum Database
    Replies: 1
    Last Post: 05-17-2006, 03:27 AM
  2. Insert into multiple tables in access 97
    By Martin S in forum VB Classic
    Replies: 3
    Last Post: 08-01-2001, 02:31 PM
  3. INSERT for multiple tables with IDENTITY
    By Kay in forum Database
    Replies: 1
    Last Post: 07-04-2001, 05:18 AM
  4. Replies: 1
    Last Post: 02-09-2001, 08:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center