How to add records to Detail table? RRS feed

  • Question

  • Hi all,

    Let say I have two tables in Master / Detail two-way relationship like this:

    dept_id  integer  PRIMARY KEY
    dept_name  varchar(40)
    dept_contact_staff_id  integer REFERENCES Staff(staff_id)

    staff_id  integer  PRIMARY KEY
    dept_id  integer  REFERENCES Dept(dept_id)
    staff_name  varchar(40)

    ALTER TABLE Dept ADD CONSTRAINT fk_Dept_Contact_Staff_id
        FOREIGN KEY (dept_contact_staff_id)

    In my C# application, I have a form to maintain these two tables records. Can may think the screen is splitted into two part, Dept & Staff. In Dept part, it has one TextBox for input department name value and one ComboBox for choosing from Staff those belongs to current department.

    Initially two tables are blank. So the application lets user to add the department record and staff record on the same screen. While the user drop down the Contact Staff combo-box, of cause the drop down list is blank because there is none of staff under this department. So the user switch the input focus to the Staff part on the same screen. After user add 3 Staff records there, I want the Contact Staff combo-box on the department part can be updated IMMEDIATELY. What I means immediately is, I want the combo-box can be updated BEFORE the application insert the department record to database.

    Furture more In LINQ, the department data object that DBML generate for me is, I can access the Detail Staffs from Dept object by "dept.Staffs". So I want to know how to bind the "dept.Staffs" to the Staff controls in the staff part in this screen I mention before.

    I know that this feature must work with the database which is under DEFERRED constraint checking mode. Would you mind tell me how to create deferrable constraint in SQL Server 2000?

    Thursday, September 18, 2008 7:29 AM