Getting error when creating FK

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

   

Answer 1

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

Answer 2

Your syntax is incorrect.  You don't pass data types in FKs.  

Please see:

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-ver15#create-a-foreign-key-in-a-new-table

Answer 3

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)

Answer 4

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

Answer 5

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'.

Answer 6

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

Answer 7

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.

Answer 8

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