Sql表设计问题。一个包含两个表键的表 [英] Sql table design issue. One table that contains two table keys

问题描述

我有两张桌子：老师和课程



每位老师都可以有一个或多个课程我创建一个新桌子并将其命名为TeacherLesson：

I have two tables : Teacher and Lesson

Every teacher may have one or more lessons i create a new table and named it TeacherLesson :

CREATE TABLE [dbo].[TeacherLesson] (
[Id]        INT IDENTITY (1, 1) NOT NULL,
[teacherID] INT NULL,
[LessonID]   INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC));

当用户尝试添加新教师时，他/她可以选择l新老师的essons所以我有一个用户可以选择它们的课程列表。这意味着我已经拥有了课程ID，但教师用的内容是什么？教师表中的
teacherID是一个身份规范（auto int）字段，所以我不知道下一个id是什么！我如何使用teacherID和LessonID在TeacherLesson表中插入新记录

请注意我使用linq to SQL和WPF C＃！



我可以使用foreign-key和make teacherID外键并做这样的事情？ ：

when user try to add a new teacher he/she can choose lessons for new teacher so i have a list of lessons that user can select them. that means i already have the lesson id but what about TeacherID
teacherID in teacher table is an identity specification (auto int) field so i don't know what is next id ! how i can insert new record in TeacherLesson Table with teacherID and LessonID
note that i use linq to SQL and WPF C#!

can i use foreign-key an make teacherID foreign-key and do something like this ? :

Teachers.TeacherLesson.add(newTeacherLesson);

技术上还可以吗？



谢谢！



我的尝试：



SQL表格设计问题。一个表包含两个表键

is it technically OK ?

thank you!

What I have tried:

SQL Table design Issue. one table that contains two table Keys

推荐答案

看一下使用ExecuteScalar函数来获取ID。

Have a look at using ExecuteScalar function to get the ID.

Int32 newId = (Int32) myCommand.ExecuteScalar();

< br $> b $ b

查看

检索身份或自动编号值 [ ^ ]

您好，

我认为您可以根据需要创建3个表格。

a。课程

b。老师

3. TeacherLesson（教师和课程之间的关系表）

现在，首先在课程表中插入数据，因为你已经上课了。现在，当您在Teacher中同时插入数据时，在TeacherLesson表中插入记录也意味着首先在Teacher表中插入数据，然后从该表中检索最后插入的TeacherId，然后逐个插入Lessons用于TeacherId和结构TeacherLesson应该与你的定义相同意味着

Hello ,
I think you can create 3 Tables for your requirement .
a. Lesson
b. Teacher
3. TeacherLesson (relation table between Teacher and Lesson )
Now , First insert data in Lesson table as you already have lessons . Now , when you are inserting data in Teacher at the same time insert record in TeacherLesson Table also means first insert data in Teacher table and then retreive the last inserted TeacherId from that table and then insert Lessons one by one for the TeacherId and the structure of TeacherLesson should be same as your definition means

CREATE TABLE [dbo].[TeacherLesson] (
[Id]        INT IDENTITY (1, 1) NOT NULL,
[teacherID] INT NULL,
[LessonID]   INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC));

并且对于获取最后插入的标识值，您可以使用

and for get last-inserted identity value you may use

@@IDENTITY

更多详情参考： 这里



希望它可以帮到你。



谢谢

For more details Reference : Here

Hope it helps you .

Thanks

非常感谢您的回答



i use LINQ和WPF-C＃不是T-SQl！



我已经使用的解决方案有效，但我想知道它在技术上是否正常并且最适合LINQ C＃？

thank you very much for your answer

i use LINQ and WPF-C# not T-SQl !

the solution that i already use works but i want to know if it is technically OK and best for LINQ C# ?

public static void InsertOrUpdateTeacher( string Name,string Family
,string Licence ,  byte[] Bytes,List<lesson> Lessons)
        {
 
            DataClassesDataContext dc = new DataClassesDataContext();
                Table<teacher> TeacherTable = dc.GetTable<teacher>();
                Teacher teacher = new Teacher();
                teacher.Name = Name;
                teacher.Family = Family;
                teacher.Licence = Licence;
                teacher.Image = Bytes;
                foreach (Lesson item in Lessons)
                {
                    TeacherLesson tl = new TeacherLesson();
                    tl.LessonID = item.Id;
                    Teacher.TeacherLessons.Add(tl);
                }
                TeacherTable.InsertOnSubmit(Teacher);
                TeacherTable.Context.SubmitChanges();
 

           
          
 
        }
</teacher></teacher></lesson>

这是带有外键的TeacherLesson表

and this is TeacherLesson table with Foreign-key

CREATE TABLE [dbo].[TeacherLesson] (
    [Id]        INT IDENTITY (1, 1) NOT NULL,
    [teacherID] INT NULL,
    [LessonID]   INT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_TeacherLesson_ToTable] FOREIGN KEY ([teacherID]) REFERENCES [dbo].[Teacher] ([Id])
);

这篇关于Sql表设计问题。一个包含两个表键的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！