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

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.

CodePudding user response：

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 value valid. 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.

CodePudding user response：

I think there is a bit confusion, if I understand correctly what You're trying to do, then you have two tables each with their own id, which is based on an identity column, so you get new values in those for free. Then you are trying to make a relation table with extra data.

Issue 1: You cannot have FcsGyartjaGya.GyID be identity if it refers to Gyartok.GyID because you will want to insert into it and not rely on an auto increment. If it doesn't refer to the same it should have another name or my head will possibly explode :))

Issue 2: When populating a relation table you need to insert it with what pairs you want, there is no way SQL server can know how it should match these identity pairs in the relation table

I think this is what people are aiming at in the comments, for example to insert a relationship between row with Focicsuka.CsID = 1 to Gyartok.GyID 7 and adding Ar = 300 have to look like

INSERT INTO FCSGYARTJAGYA(GYID, CSID, AR)
VALUES(7, 1, 300)

Unless You've forgotten to mention that you want to put some value for each of some value or based on something which can be scripted, in other words unless You have logics to define the pairs and their values, relationship tables cannot have defaults on their foreign key fields.