Suppose I have 2 tables in my database and 2 Windows Forms (C#) for each table Section and Pages. Now each Section can have 10 Pages.

Note that I am creating a Windows Forms app and first form looks like this

FORM 1 (For Section Table)

And my second form for table pages looks like this:

FORM 2 for Pages Table.

By clicking on each Section, user will be directed to the 2nd form where user will/can have 10 pages.

I have this:

create table Section
(
    Sectionid int primary key, 
    SectionName varchar,
    Pageid int
) 

create table Page
(
    Pageid int primary key, 
    Pagetitle int,
    Noteid int
)

foreign key(pageid) references Page(pageid)

I am confused how to insert data to the database once you have created a page.

AGAIN: 1 SECTION CAN HAVE AT LEAST 10 PAGES AND 1 PAGE CAN HAVE 1 NOTE.

If I have not made it easier to understand please just use Microsoft's One Note mobile app, I am basically copying it - thanks.

CodePudding user response：

The relation between section an page is 1: n.

You can formulate it as: One section has multiple pages, but one page only has one section. You always have to put the foreign key in the "n" table, which is in this case the "page".

So you have something like that, tell me your database and I can write you a exact sql:

Create Table Section(
  Sectionid int primary key,
  SectionName varchar,
)

Create Table Page(
  Pageid int primary key,
  pagetitle int,
  Noteid int,
  SectionId int foreign key  
)

...but I guess the pagetitle is also a varchar?

If you want to insert data, it is easy for the section. A simple insert statement. If you want insert data in the page object, you have to insert the id of the section for the column "SectionId".

Example:

Insert into table Section (Sectionid, SectionName) values (12,'test');
Insert into table Page (Pageid, Sectionid) values(1,12);

The value "12" you insert is the id of the section.

CodePudding user response：

once you introduce constraints into the schema you need to be deliberate with how to insert data.

(temp tables don't enforce constraints like this)

CREATE TABLE #Pages    (PageID INT IDENTITY PRIMARY KEY, PageTitle NVARCHAR(20), NoteID INT)
CREATE TABLE #Sections (SectionID INT IDENTITY PRIMARY KEY, SectionName NVARCHAR(20), PageID INT CONSTRAINT FK_PageID REFERENCES #Pages(PageID))

Now we have our tables, complete with constraints. The FK must exist in the referenced table.

DECLARE @KeyTable TABLE (PageID INT)

INSERT INTO #Pages (PageTitle, NoteID) 
OUTPUT INSERTED.PageID INTO @KeyTable
VALUES ('Some Page', NULL)

INSERT INTO #Sections (SectionName, PageID) 
SELECT 'Some Section', PageID
  FROM @KeyTable
INSERT INTO #Sections (SectionName, PageID) 
SELECT 'Another Section', PageID
  FROM @KeyTable

When we insert a page we can capture the ID generated (or provided if it's not also an identity) and hold it in a table variable. We can then use it in the insert into Sections as well. When you're actually doing this you may want to build some logic around checking if a page already exists and populating the table variable with the appropriate existing PageID if it does or doing the insert and propulating the PageID this way if it does not.