How can I insert values into a table with a composite primary key?

Question

These are the tables I already have:

CREATE TABLE Gyartok 
(
    GyID INT IDENTITY(2, 3),
    Nev VARCHAR(20),

    CONSTRAINT PK_Gyartok PRIMARY KEY (GyID)
)

CREATE TABLE Focicsuka 
(
    CsID INT IDENTITY(2, 2),
    Meret INT, 

    CONSTRAINT PK_Focicsuka PRIMARY KEY (CsID)
)

CREATE TABLE FcsGyartjaGya 
(
    GyID INT IDENTITY(3, 2), 
    CsID INT,
    Ar INT,

    CONSTRAINT FK_FcsGyartjaGya1 
        FOREIGN KEY (GyID) REFERENCES Gyartok(GyID),
    CONSTRAINT FK_FcsGyartjaGya2 
        FOREIGN KEY (CsID) REFERENCES Focicsuka(CsID),
    CONSTRAINT PK_FcsGyartjaGya 
        PRIMARY KEY (GyID, CsID)
)

The problem is that every time I try to add new values to the table (like such)

INSERT INTO FcsGyartjaGya (Ar) VALUES (300);

I get an error saying I didn't initialize the CsID INT column:

Cannot insert the value NULL into column 'CsID', table 'Lab3.dbo.FcsGyartjaGya'; column does not allow nulls. INSERT fails.

I know I must initialize it with something, but I have no idea what do to it with, because IDENTITY(x, y) doesn't work (it's occupied by another column already) and adding another parameter to the code (like such)

INSERT INTO FcsGyartjaGya (Ar, CsID) VALUES (300, 7);

creates another error which says

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FcsGyartjaGya1". The conflict occurred in database "Lab3a", table "dbo.Gyartok", column 'GyID'.

It is important to note that I already filled every column with data, so that couldn't be the problem.

Answer 1

As I mention in the comments, your INSERT will work fine, provided the stars align correctly. For your table Gyartok you have GyID as your PRIMARY KEY, which is defined as a IDENTITY(2,3); so the first value generated is 2 and then each row attempted to be INSERTed will increment by 3.

So, if we run the following, we get the IDs 2, 5, 7 and 17. (11 and 14 are skipped as the INSERT failed).

CREATE TABLE Gyartok (
    GyID INT IDENTITY(2, 3),
    Nev VARCHAR(20),
    CONSTRAINT PK_Gyartok PRIMARY KEY (GyID)
);
GO
INSERT INTO dbo.Gyartok (Nev)
VALUES ('asdfjahsbvd'),
       ('ashjkgdfakd'),
       ('kldfbhjo');
GO
INSERT INTO dbo.Gyartok (Nev)
VALUES (REPLICATE('A',25)), --Force a truncation error
       ('ashjkgdfakd');
GO
INSERT INTO dbo.Gyartok (Nev)
VALUES (REPLICATE('A',15));

Let's now add some data for your other table:

CREATE TABLE Focicsuka (
    CsID INT IDENTITY(2, 2),
    Meret INT, 
    CONSTRAINT PK_Focicsuka PRIMARY KEY (CsID)
)
INSERT INTO dbo.Focicsuka (Meret)
VALUES(12),
      (25);

Now we want to INSERT into the table FcsGyartjaGya, defined as the following:

CREATE TABLE FcsGyartjaGya (
    GyID INT IDENTITY(3, 2), 
    CsID INT,
    Ar INT,

    CONSTRAINT FK_FcsGyartjaGya1 FOREIGN KEY (GyID) REFERENCES Gyartok(GyID),
    CONSTRAINT FK_FcsGyartjaGya2 FOREIGN KEY (CsID) REFERENCES Focicsuka(CsID),
    CONSTRAINT PK_FcsGyartjaGya PRIMARY KEY (GyID, CsID)
)

This has a IDENTITY on GyID, but defined as an IDENTITY(3,2), so the first value is 3 and then incremented by 2.

As this has 2 foreign keys, on GyID and CsID when we INSERT the row the values must appear in the respective tables. As GyID is defined as anIDENTITY(3,2) however, this is where we need to rely on the Stars luck for the INSERT to work. Why? Well 2 + (3*n) and 3+(2*n) can give very different numbers. The first are as you saw at the start of this answer. For the latter, we have numbers like 3, 5, 7, 9, 11. As you can see, only 1 in 3 of these numbers match a number in our original sequence, so luck is what we are going to be relying on.

Let's, therefore, try a single INSERT.

INSERT INTO dbo.FcsGyartjaGya (CsID,Ar)
VALUES(2,1);

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FcsGyartjaGya1". The conflict occurred in database "Sandbox", table "dbo.Gyartok", column 'GyID'.

Well, that didn't work, but it was expected. 3 isn't a value in the table Gyartok. Let's try again!

INSERT INTO dbo.FcsGyartjaGya (CsID,Ar)
VALUES(2,2);

It worked! The stars Luck was our side, and the IDENTITY value was a value in the table Gyartok. Let's try a couple of rows this time!

INSERT INTO dbo.FcsGyartjaGya (CsID,Ar)
VALUES(4,3),
      (4,4);

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FcsGyartjaGya1". The conflict occurred in database "Sandbox", table "dbo.Gyartok", column 'GyID'.

No!! Not again. :( That's because the stars didn't align; 7 and 9 aren't in the other table. But wait, 11 was in the sequence, so let's try that:

INSERT INTO dbo.FcsGyartjaGya (CsID,Ar)
VALUES(4,5);

Error, again?! No, it cannot be!!! :( Oh wait, I forgot, the stars were against us before, because that INSERT failed against Gyartok for the value of 11. I need to wait for 17!

--13 fails
INSERT INTO dbo.FcsGyartjaGya (CsID,Ar)
VALUES(4,6);
GO
--15 fails
INSERT INTO dbo.FcsGyartjaGya (CsID,Ar)
VALUES(4,6);
GO
--17 works!
INSERT INTO dbo.FcsGyartjaGya (CsID,Ar)
VALUES(4,6);

And now we have another row in our table.

---

So what is the problem? Your design. GyID is defined as an IDENTITY and a FOREIGN KEY; meaning you are at the "whims" of SQL Server generating a valid value. This is not what you want. Just don't define the column as an IDENTITY and then INSERT the data with all 3 of your columns defined:

CREATE TABLE FcsGyartjaGya (
    GyID int,-- IDENTITY(3, 2), 
    CsID INT,
    Ar INT,

    CONSTRAINT FK_FcsGyartjaGya1 FOREIGN KEY (GyID) REFERENCES Gyartok(GyID),
    CONSTRAINT FK_FcsGyartjaGya2 FOREIGN KEY (CsID) REFERENCES Focicsuka(CsID),
    CONSTRAINT PK_FcsGyartjaGya PRIMARY KEY (GyID, CsID)
)
GO

INSERT INTO dbo.FcsGyartjaGya (GyID, CsID, Ar)
VALUES(2,2,1),
      (2,4,2),
      (5,4,3),
      (8,2,4),
      (8,4,5);

And all these rows insert fine.