Foreign key shenanigans - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Foreign key shenanigans

Author

Topic

tjaeger
Starting Member

2 Posts

Posted - 2011-01-12 : 22:47:37

Hello, I learning SQL and I am new to these forums. Anyway, here is my situation:

Table1 has a column ValueID of type int.
Table2 has a column Value of type varchar(100) and column ID of type int.

Currently, I have it set up so that Table1.ValueID is a foreign key of ID. It works in the sense that I can only add IDs to Table1.ValueID that exist in Table2.ID. Is it possible to set up a FK relationship so that if I try to add a string in the Table1.ValueID column it will check to see if that string exists in the Table2.Value column and use the corresponding Table2.ID value for Table1.ValueID?

Thanks, hopefully my explanation made sense.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-13 : 00:34:17

yes simply set the required column as foreign key to the table2.ID. Following is the syntax of adding a foreign key constraint

ALTER TABLE table2
ADD CONSTRAINT FK_table2_ID FOREIGN KEY (ID)
REFERENCES table1 (ID) ;

tjaeger
Starting Member

2 Posts

Posted - 2011-01-13 : 03:39:55

I ran that code and it didn't really do much of anything.

If I try to insert a string into Table1.ValueID I will get this error:

"Conversion failed when converting the varchar value 'text' to data type int."

And not only that, but I can insert int values into Table2.ValueID that don't exist in Table2.ID.

This is the original code I ran:

ALTER TABLE Table1
WITH NOCHECK
ADD CONSTRAINT FK_Table1_ID FOREIGN KEY (ValueID)
REFERENCES Table2 (ID) ;

Now, let's say I have only one row in Table two that is ["one", 1] for Value and ID. If I try to add a row to Table1 with ValueID not equal to 1 then it will give me an error, which is what I expect. What I really want to do is be able to insert "one" for Table1.ValueID and have Table1.ValueID be equal to 1. However, if I try to insert "one" into ValueID it will give me the type conversion error, and if I try to run the code like:

ALTER TABLE Table1
WITH NOCHECK
ADD CONSTRAINT FK_Table1_ID FOREIGN KEY (ValueID)
REFERENCES Table2 (Value) ;

It will give me an error about ValueID and Value being mismatched types. BTW, I'm not sure if this matters but I have Table2.ID set as a primary key.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-13 : 05:06:47

First of all; the conversion failed error Means that you are trying to insert Varchar/Text data into a column having datatype as INT. so please make sure that you are mapping correct information over required fields as per their datatypes and then insert it.

And you question regarding the relationship .. please go through the below Example where i have implemented primary- foreign Key relationship between table1 and table2

Create table table1 (ID1 int primary Key, sName2 varchar(10));
Create table table2 (ID2 int, sName2 varchar(10),ID1 int);

--Adding FK constraint for the table2.ID1 , which will validate its information on behalf of the table1.ID1 column
ALTER TABLE table2
ADD CONSTRAINT FK_table2_ID1 FOREIGN KEY (ID1)
REFERENCES table1 (ID1) ;

--Testing as if the relationship is working
Insert into table1 values (1,'One'); --This will work
go
Insert into table2 values (1,'Checking',1); -- This will work
go
Insert into table2 values (1,'Checking',2); -- this will raise an error..

go through it and try to implement it in your database as required :)

Cheers!~
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources