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.
| Author |
Topic |
|
david_reinjal
Starting Member
36 Posts |
 Posted - 2006-06-14 : 05:26:00
|
| i have this code,CREATE TABLE s(sno VARCHAR(5) NOT NULL PRIMARY KEY, name VARCHAR(16), city VARCHAR(16))CREATE TABLE p(pno VARCHAR(5) NOT NULL PRIMARY KEY, descr VARCHAR(16), color VARCHAR(8))CREATE TABLE sp(sno VARCHAR(5) NOT NULL REFERENCES s, pno VARCHAR(5) NOT NULL REFERENCES p, qty INT CHECK (qty>=0), PRIMARY KEY (sno, pno))when i run this query i get an error saying: Column 's.sno' is not the same length as referencing column 'sp.sno' in foreign key 'FK__sp__sno__5EBF139D'. Columns participating in a foreign key relationship must be defined with the same length.can anyone assist me what to be done? |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-06-14 : 05:56:18
|
| Hi,I dont see any problem in your create statements. Moreover i tried exxactly what you posted on my sql server it works perfectly. |
 |
|
|
david_reinjal
Starting Member
36 Posts |
Posted - 2006-06-14 : 06:31:54
|
| which sql version is it? because i m using sql 2005 and its not working. |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-06-14 : 07:17:05
|
| Its working in Sql server 2000 as well as SQL server 2005. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-06-14 : 07:41:34
|
| The only way that I can recreate your error message is to change the length of one of the varchar columns so that they are not the same.Check your code.-------Moo. :) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-06-15 : 08:15:51
|
| Have you tried:CREATE TABLE sp(sno VARCHAR(5) NOT NULL REFERENCES s(sno),pno VARCHAR(5) NOT NULL REFERENCES p(pno),qty INT CHECK (qty>=0),PRIMARY KEY (sno, pno)Never hurts to explicitly indicate which columns are being referenced. I tried both forms in SQL 2005 and both worked. I'm wondering if you attached or restored the database from a previous version of SQL Server and the compatibility level is affecting it. |
|
|
|
david_reinjal
Starting Member
36 Posts |
Posted - 2006-06-15 : 10:04:09
|
| anywayz thanks for the change u told me. i will try it. |
|
|
|
|
|
|
|
|
Reference error