0
CREATE TABLE Thesis 
(
    ThesisNo numeric(8,0) NOT NULL,
    AuthorID int NOT NULL,
    EnstituteID int NOT NULL,
    SupervisorID int NOT NULL,
    Title nvarchar(100)NOT NULL,
    Abstract nvarchar(500)NOT NULL,
    Pages int NOT NULL,
    SumbitDate datetime NOT NULL,
    [Type] nchar(30) NOT NULL,
    [Language] nchar(20) NOT NULL,
    PRIMARY KEY (ThesisNo),
    FOREIGN KEY (EnstituteID) REFERENCES Enstitute(EnstituteID),
    FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID),
    FOREIGN KEY (SupervisorID) REFERENCES Supervisor(SupervisorID)
)

CREATE TABLE Person
(
    PersonID int NOT NULL,
    PersonFullName nvarchar(70) NOT NULL,
    PRIMARY KEY (PersonID)
)

I use a Windows Forms application written in C#.

I take the author as a string, but when adding a foreign key to the thesis, I need to add that author's primary key so that I can access the contact table and find that person. How can I find out what is the primary key of the person name entered?

 string query = "INSERT INTO Thesis (AuthorID,EnstituteID,SupervisorID,CoSupervisorID,Title,Abstract,Pages,SubmitDate,Type,Language)" +
                            "VALUES (@author,@enstitute,@supervisor,@cosupervisor,@title,@abstract,@pages,@submitdate,@type,@language) ";

AuthorID or other ID members are int and foreign keys but the entered values are string @author @enstitute, @supervisor, @cosupervisor

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Baris
  • 29
  • 8
  • You need to do another query to retrieve id based on the name. You can do it in c# before saving a thesis. You can do the primary key query as part of the "insert" statement, but I would suggest to do it in the c# application to make it clear what dependencies thesis creation has. – Fabio Jan 03 '21 at 22:03
  • 1
    Is `ThesisNo` / `PersonID` an IDENTITY column? If so, https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver15 . – mjwills Jan 03 '21 at 22:26
  • Your question is primarily related to your application and the GUI you use to allow a person to enter this information. Typically one uses some sort of drop-down list to select the values corresponding to the FK columns. Either way, you need to retrieve the ID associated with the "string" that was entered. A little strange that you show DDL for Person but not for any of the actual FK tables. Perhaps you need guidance about coding an effective application and its GUI? – SMor Jan 03 '21 at 22:54
  • I dont know why you use the same old teknik there is now ORM like EntityFrame Work that you could use. – Alen.Toma Jan 03 '21 at 22:54

2 Answers2

0

You can do a SELECT and JOIN inside of the INSERT. So you could say for example:

INSERT INTO Thesis  
(AuthorID,EnstituteID,SupervisorID,CoSupervisorID,Title,Abstract,Pages,SubmitDate,Type,Language)
SELECT
a.PersonID,
e.EnstituteID,
s.PersonID,
@cosupervisor,
@title,
@abstract,
@pages,
@submitdate,
@type,
@language
FROM Person a
JOIN Enstitute e ON e.Name = @enstitute
LEFT JOIN Person s ON s.Name = @supervisor
WHERE a.Name = @author;

Where a value can be null, you should LEFT JOIN.

Be aware: if the name is not unique you will get multiple rows in your insert.

If you are inserting the two tables together, you can use the function SCOPE_IDENTITY() in the statement immediately following the first INSERT to get the IDENTITY value. Alternatively, you can use the OUTPUT clause into a table variable.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
0

If you take author's name as a string then you can search in Person table using simple queries Like =>
1. Select PersonID FROM Person Where PersonFullName Like '%Author Name%'
2. Select PersonID FROM Person Where PersonFullName='Author Name'
but there are some problems using those queries . If you use the first one, than there is silght chance that the query might return multiple rows. If you use the second one, then you have a chance to not get any row.

Finally, my suggesion is not to take author as a string. Use a combobox and load all authors into that combobox and then select the specific author and save the author id.

New Upate On that Solution (Using Combo box):
If you do not know how to bind list to combobox and how to use SelectedIndexChanged event then please check given links->
How to bind a List to a ComboBox?
Getting selected value of a combobox 

public class Person //Your Person Class
{
    public int PersonID { get; set; }
    public string PersonFullName { get; set; }
} 
public partial class Form1 : Form //Your form class where you use combo box
{
    public Form1()
    {
        InitializeComponent();
        loadCombo();
    }
    void loadCombo() //load dummy data to combo
    {
        //Load Combobox with List Of Persons
        List<Person> persons = new List<Person>();
        persons.Add(new Person() { PersonID = 1, PersonFullName = "Vikbaris" });
        persons.Add(new Person() { PersonID = 2, PersonFullName = "Linker-SJNF" });
        
        var bindingSource1 = new BindingSource();
        bindingSource1.DataSource = persons;
        comboBox1.DataSource = bindingSource1.DataSource;
        comboBox1.DisplayMember = "PersonFullName";
        comboBox1.ValueMember = "PersonID";
    }

    // Selected AuthorID event callback  
    private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
        ComboBox cmb = (ComboBox)sender;
        int selectedIndex = cmb.SelectedIndex;
        Person selectedValue = (Person)cmb.SelectedValue;
        int authorID = selectedValue.PersonID; //Here is your desire AuthorID
    }
}
Srijon Chakraborty
  • 2,007
  • 2
  • 7
  • 20