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 |
|
umarsql
Starting Member
3 Posts |
 Posted - 2011-01-10 : 07:20:53
|
| Hello programmers...----------------------------------------I have tow tableSTUDENTS with tow col---> Code(int), Name(varchar(50)ORDERS with tow col---> OrderNo(int), SCode(int), Quantity(int)int the first table Code is PK and on second OrderNo is PK.This tow tables has relation which "SCode" on ORDERS table is foreign key of "Code" on STUDENTS.there is tow records in STUDENTS table.code = 1 Name = 'AAAA'code = 2 Name = 'BBBB'now, when i want to insert a record in table ORDERS with these values every thing is ok:OrderNo = 1 SCode = 1 Quantity = 3but when SCode is 0 an error occures and nothing inserted...!i know why this happen! because the value 0 is not exists on STUDENTS table on column Code.now this is my question.... How can i insert this record with same 0 value??? some times i want insert records that does not exists on PK table!can any one help me...?! as soon as u can please...!I am using SQL Server 2000. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-01-10 : 07:24:24
|
You could insert a DUMMY entry in the table STUDENTS with Code=0 and Name='Dummy'.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
umarsql
Starting Member
3 Posts |
Posted - 2011-01-10 : 07:30:07
|
quote:
Originally posted by webfred
You could insert a DUMMY entry in the table STUDENTS with Code=0 and Name='Dummy'.
No, you're never too old to Yak'n'Roll if you're too young to die.
Yes i know...!!but in this case i must place a "WHERE Code > 0" in every selects..!!!i mean i do not want to involve sql server in every selects in my application...!there is not any other way to insert record without let sql server check PK existance...?! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-01-10 : 07:35:24
|
There is a constraint.So the answer is: no there is no way.Or remove the constraint but I think that isn't what you really should do.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-10 : 10:55:06
|
| this is typical scenario that usually happens in warehouse tables. there also solution is to include the 0 valued record in parent table in referential integrity. While selecting from table you've to specifically exclude it if you dont need it.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-01-11 : 07:47:01
|
quote:
Originally posted by visakh16
this is typical scenario that usually happens in warehouse tables. there also solution is to include the 0 valued record in parent table in referential integrity. While selecting from table you've to specifically exclude it if you dont need it.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
That is what we already talked about  and OP don't want that.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
insert record with out check existance on PK table