CODEWITHSUNDEEP
sql-serverinsertidentity
Rosario
Rosario

Reputation: 67

Identity conflict with inserting data

I got an issue involving running mySQL code in MS SQL server. I used identities to auto increment id numbers in most tables. For example:

CREATE TABLE klant (
    klantnr INTEGER identity (1, 1) PRIMARY KEY,
    bedrijfsnaam VARCHAR (50) NOT NULL
)

I'm trying to insert this same "klantnr" value that exists in this table in my table Logins, which is defined as a foreign key to the Klant table:

CREATE TABLE Logins (
    GebruikersNaam VARCHAR (30),
    Wachtwoord VARCHAR (30),
    Klantnr INT,
    MdwNr INT,
    CONSTRAINT pk_logID PRIMARY KEY (GebruikersNaam),
    CONSTRAINT fk_klantnr FOREIGN KEY (klantnr) REFERENCES klant (klantnr),
    CONSTRAINT fk_mdwnr FOREIGN KEY (mdwnr) REFERENCES medewerker (mdwnr)
)

Here are the inserts:

INSERT INTO klant VALUES ('Jumbo'); -- (this is the first insert, klantnr is 1)
INSERT INTO Logins VALUES ('KlantTest', 'test', 1, NULL);

Error: Msg 547, Level 16, State 0, Line 302 The INSERT statement conflicted with the FOREIGN KEY constraint "fk_klantnr". The conflict occurred in database "DorblogisticsB31B", table "dbo.klant", column 'klantnr'.

What Am I doing wrong?

Upvotes: 0

Views: 999

Answers (4)

Rosario
Rosario

Reputation: 67

I fixed it. It was the most stupid mistake ever. I tried inserting the Login data before inserting the klant data...

Upvotes: 0

EaziLuizi
EaziLuizi

Reputation: 1615

I am pretty sure you are overlooking something very small, as we usually do with these kinds of errors, run the following sql and see if you get the same results: (NOTE: not trying to use best practices or anything, just trying to get you to your answer)

    IF OBJECT_ID('dbo.Logins', 'U') IS NOT NULL 
      DROP TABLE dbo.Logins; 

    IF OBJECT_ID('dbo.klant', 'U') IS NOT NULL 
      DROP TABLE dbo.klant; 

    CREATE TABLE klant (
        klantnr INTEGER identity (1, 1) PRIMARY KEY,
        bedrijfsnaam VARCHAR (50) NOT NULL
    )

    CREATE TABLE Logins (
        GebruikersNaam VARCHAR (30),
        Wachtwoord VARCHAR (30),
        Klantnr INT,
        MdwNr INT,
        CONSTRAINT pk_logID PRIMARY KEY (GebruikersNaam),
        CONSTRAINT fk_klantnr FOREIGN KEY (klantnr) REFERENCES klant (klantnr)
    )



    --re run this query 3 times...
    INSERT INTO klant VALUES ('Jumbo');
    INSERT INTO Logins VALUES ('KlantTest' + CONVERT(VARCHAR,(SELECT MAX(klantnr) FROM klant)),
                                 'test', 
                                 (SELECT MAX(klantnr) FROM klant) 
                                 , NULL);


    SELECT * FROM klant
    /*
    OUTPUT:
    1   Jumbo
    2   Jumbo
    3   Jumbo
    */
    SELECT * FROM Logins

    /*
    OUTPUT:
    KlantTest1  test    1   NULL
    KlantTest2  test    2   NULL
    KlantTest3  test    3   NULL
    */

Upvotes: 1

irakliG.
irakliG.

Reputation: 176

After inserting into [klant] try this:

SELECT * FROM klant WHERE bedrijfsnaam='Jumbo'

in that way you can find out what ID number is assigned to 'Jumbo'. And then with this ID you can insert values into [logins].

Upvotes: 0

Chirag Kavathiya
Chirag Kavathiya

Reputation: 24

WORKAROUND Here are two ways to work around this: Define all the columns used in a foreign key constraint as NOT NULL.

-or-

Update all the columns used in a foreign key constraint in the same update statement.

https://support.microsoft.com/en-us/kb/256307 https://dba.stackexchange.com/questions/62325/how-to-solve-the-error-msg-547-level-16-state-0-line-1

Upvotes: 0

Related Questions