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
[1044 byte] By [
AzlanAziz] at [2007-11-11 10:08:14]
# 1 Re: help with multiple table insert
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.
mikkus at 2007-11-11 23:43:34 >
