locked
Getting error when creating FK RRS feed

  • Question

  • Hi there,

    I have two tables TableA  and TableB. In TableA the Id column is of type uniquidentifier and it is a primary Key.  In TableB, I'm trying to create a foreign key on column pur_Id which is uniqueidentifier but keep getting error - Error validating the default for column Id. Can someone help?

    ALTER TABLE TableB
    ADD CONSTRAINT "FK_TableA_TableB_Pur_Id" FOREIGN KEY (UNIQUEIDENTIFIER Pur_id)
    REFERENCES TableA] UNIQUEIDENTIFIER (Id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

    Joe

       

    Monday, January 6, 2020 12:43 PM

Answers

  • It is possible there is a data in table b ( child) which does not exists in Table a (parents). I can easily re procedure it

    create table tablea (id UNIQUEIDENTIFIER not null primary key)

    insert into tablea select newid()
    go 3

    select * from tablea
    EA405D9C-7C57-4AFA-9F83-067685235A9B
    2C26317D-43E4-4A8E-8B7A-D57683CD8811
    4400A856-1C74-4CC3-BE2A-E4F8DBD1CA57


    create table tableb (Pur_id UNIQUEIDENTIFIER )

    insert into tableb select newid()
    go 3
    select * from tableb
    BBDF1088-F73F-49B2-8B34-7133B0B72DAF
    FC503E00-465B-4EFA-8C23-87257AA686CC
    9252F981-C650-4462-8687-1BFBD3A1239B

    ALTER TABLE tableb
    ADD CONSTRAINT FK_TableA_TableB_Pur_Id FOREIGN KEY (Pur_id)
    REFERENCES tablea (Id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

    Msg 547, Level 16, State 0, Line 21
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 
    "FK_TableA_TableB_Pur_Id". The conflict occurred in database "tempdb", table "dbo.tablea", column 'id'.

    Now do you know how to fix it? Is it possible to remove some record in table b which does not exist in table a ?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Naomi N Monday, January 6, 2020 1:39 PM
    • Marked as answer by green2004 Monday, January 6, 2020 2:51 PM
    Monday, January 6, 2020 1:14 PM
    Answerer

All replies

  • See if this works

    CREATE TABLE TABLEA (id UNIQUEIDENTIFIER not null primary key)

    create TABLE TableB (Pur_id UNIQUEIDENTIFIER)

    ALTER TABLE TableB
    ADD CONSTRAINT FK_TableA_TableB_Pur_Id FOREIGN KEY (Pur_id)
    REFERENCES TableA (Id)
    ON DELETE CASCADE
    ON UPDATE CASCADE


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 6, 2020 12:49 PM
    Answerer
    • Proposed as answer by Naomi N Monday, January 6, 2020 1:39 PM
    Monday, January 6, 2020 12:50 PM
  • TableA and TableB already exists and contain data. Won't I lose existing data if I

    CREATE TABLE TABLEA (id UNIQUEIDENTIFIER not null primary key)

    create TABLE TableB (Pur_id UNIQUEIDENTIFIER)

    Monday, January 6, 2020 12:55 PM
  • Yep, no problem just add a FK (your syntax is wrong) 

    ALTER TABLE TableB
    ADD CONSTRAINT FK_TableA_TableB_Pur_Id FOREIGN KEY (Pur_id)
    REFERENCES TableA (Id)
    ON DELETE CASCADE
    ON UPDATE CASCADE


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 6, 2020 12:56 PM
    Answerer
  • When I execute the statement

    ALTER TABLE TableB
    ADD CONSTRAINT FK_TableA_TableB_Pur_Id FOREIGN KEY (Pur_id)
    REFERENCES TableA (Id)
    ON DELETE CASCADE

    I'm getting an error -

    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint FK_TableA_TableB_Pur_Id. The conflict occurred in database "catts", table "dbo.TableA", column 'Id'.

    Monday, January 6, 2020 1:04 PM
  • It is possible there is a data in table b ( child) which does not exists in Table a (parents). I can easily re procedure it

    create table tablea (id UNIQUEIDENTIFIER not null primary key)

    insert into tablea select newid()
    go 3

    select * from tablea
    EA405D9C-7C57-4AFA-9F83-067685235A9B
    2C26317D-43E4-4A8E-8B7A-D57683CD8811
    4400A856-1C74-4CC3-BE2A-E4F8DBD1CA57


    create table tableb (Pur_id UNIQUEIDENTIFIER )

    insert into tableb select newid()
    go 3
    select * from tableb
    BBDF1088-F73F-49B2-8B34-7133B0B72DAF
    FC503E00-465B-4EFA-8C23-87257AA686CC
    9252F981-C650-4462-8687-1BFBD3A1239B

    ALTER TABLE tableb
    ADD CONSTRAINT FK_TableA_TableB_Pur_Id FOREIGN KEY (Pur_id)
    REFERENCES tablea (Id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

    Msg 547, Level 16, State 0, Line 21
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 
    "FK_TableA_TableB_Pur_Id". The conflict occurred in database "tempdb", table "dbo.tablea", column 'id'.

    Now do you know how to fix it? Is it possible to remove some record in table b which does not exist in table a ?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Naomi N Monday, January 6, 2020 1:39 PM
    • Marked as answer by green2004 Monday, January 6, 2020 2:51 PM
    Monday, January 6, 2020 1:14 PM
    Answerer
  • There were two records in TableB that weren't in TableA. After I removed those two records from TableB, I was able to add FK. Thanks a bunch.
    Monday, January 6, 2020 2:53 PM
  • .>> I have two tables TableA and TableB. <<

    Why do you think these are good, useful names for tables? Why do you think you need to put metadata into a data element name? Look "tibbling" as a design flaw. 

    >> In TableA the Id [sic] column is of type uniquidentifier and it is a PRIMARY KEY. <<

    Please read any book on RDBMS so you won't do crap like this in the future. The purpose of GUID (G = Global, not local) or UUID (U = Universal, not local) is to let us get to things external to the schema. By definition, a key is a subset of columns in a given table. Again, by definition, columns represent the attributes of the entities in the table using scalar values. Also, there is no generic universal "_id" in RDBMS, so talking about THE ID makes absolutely no sense. Now back in the days of sequential files, this used to be a record number. 

    >> In TableB, I'm trying to create a foreign KEY on column pur_Id which is uniqueidentifier but keep getting error - Error validating the default for column Id. Can someone help? <<

    When I first moved to Austin 20 years ago, I work for company where the developers coded like this. It didn't work and resulted in what had been a wonderful.com start up falling apart. Basically, they were trying to use GUIDs  as pointer chains and had no idea what a relational model was .

    Frankly, given only this narrative (and very little code) and no specs), my advice would be to start over and find someone who can really help you do this correctly.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, January 6, 2020 9:01 PM