Foreign Key Error - 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 Error

Author

Topic

Orion
Starting Member

2 Posts

Posted - 2009-03-10 : 16:03:14

I've been having this problem for a week, but I haven't been able to solve it. I have to create 6 tables and fill at least 3 rows for each table, but there're 3 tables, which have Foreign Key, and everytime I try to fill the fields wit INSERT INTO... an error ocurr:

msg 213, level 6, state 1, line 1
Insert Error: Column name or number of supplied values does not match table definition.

For what I understand, my Insert To lack a value for the Foreign Key column, but I don't know how to solve it.

This is my code:

CREATE TABLE ITEM (
ItemID Integer NOT NULL IDENTITY(1, 1),
ItemDescription Char(30) NOT NULL,
PurchaseDate Char(20) NOT NULL,
ItemCost INTEGER NOT NULL,
ItemPrice INTEGER NOT NULL,
VendorID INTEGER NOT NULL,
CONSTRAINT Ite_PK PRIMARY KEY(ItemID),
CONSTRAINT Ven_FK FOREIGN KEY(VendorID) REFERENCES VENDOR(VendorID)
);
INSERT INTO ITEM VALUES ('Keyboard', '4/6/2008', '15', '20');
INSERT INTO ITEM VALUES ('Mouse', '5/9/2008', '10', '15');
INSERT INTO ITEM VALUES ('Speakers', '2/2/2009', '25', '30');

Thanks in advance.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-10 : 18:03:45

The number of values is 1 less then the number of fields minus the identity field.

You need another value in that list. for VendorID.

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Orion
Starting Member

2 Posts

Posted - 2009-03-10 : 18:21:30

Yhanks, I tried and worked, but, shouldn't this field be filled automatically because is a Foreign Key?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 13:34:12

quote:
Originally posted by Orion

Yhanks, I tried and worked, but, shouldn't this field be filled automatically because is a Foreign Key?

Nope. the field wont be automatically filled by creating a foreign key on it. the foreign key just ensures the value used for populating field is a valid value existing in master table and will raise a fk violation error if it doesnt exist.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources