Need Help with INSERT problem - 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

Need Help with INSERT problem

Author

Topic

zneely90
Starting Member

2 Posts

Posted - 2009-04-07 : 15:33:13

I am trying to insert values that are missing from one table into another.

I have two tables that are linked by a foreign key.

The foreign key in one table does not have a value in every field, but it still references the primary table.

I have set the field to NULL, but it still does not work.

My code for the table with the foreign key is:

CREATE TABLE TRANSACTIONS (
TRANS_NUM CHAR(6) NOT NULL CHECK(TRANS_NUM IN('100000','100001','100002','100003','100004','100005','100006','100007','100008','100009','100010','100011','100012','100013','100014','100015','100016','100017','100018','100019','100020','100021','100022','100023','100024','100025','100026','100027','100028','100029','100030','100031','100032','100033','100034','100035','100036','100037','100038','100039','100040','100041','100042','100043','100044','100045','100046','100047','100048','100049','100050','100051','100052','100053','100054','100055','100056','100057','100058','100059','100060','100061','100062','100063')),
TRANS_DATE DATETIME NOT NULL,
TRANS_DESCRIPT VARCHAR(25) NOT NULL,
TRANS_PAY_TYPE VARCHAR(25) NOT NULL,
TRANS_PAY_NUM VARCHAR(25) NOT NULL,
TRANS_AMOUNT MONEY DEFAULT 0,
TRANS_CUS_NUM CHAR(5) NOT NULL CHECK(TRANS_CUS_NUM IN('10010','10011','10012','10013','10014','10015','10016','10017','10018','10019','10020','10021','10022','10023','10024','10025','10026','10027','10028','10029','10030','10031','10032','10033','10034','10035','10036','10037','10038','10039','10040','10041','10042','10043','10044','10045','10046','10047','10048','10049','10050','10051','10052','10053','10054','10055','10056','10057','10058','10059','10060')) REFERENCES CUSTOMER (CUS_NUM),
TRANS_CHAR_TRIP CHAR(5) NULL CHECK(TRANS_CHAR_TRIP IN('0','10270','10271','10272','10273','10274','10275','10276','10277','10278','10279','10280','10281','10282','10283','10284','10285','10286','10287','10288','10289','10290','10291','10292','10293','10294','10295','10296','10297')) REFERENCES CHARTER (CHAR_TRIP),
PRIMARY KEY (TRANS_NUM)
)

The table it references to is:

CREATE TABLE CHARTER (
CHAR_TRIP CHAR(5) NOT NULL CHECK(CHAR_TRIP IN('0','10270','10271','10272','10273','10274','10275','10276','10277','10278','10279','10280','10281','10282','10283','10284','10285','10286','10287','10288','10289','10290','10291','10292','10293','10294','10295','10296','10297')),
CHAR_DATE DATETIME NOT NULL,
CHAR_AC_NUM CHAR(5) NOT NULL CHECK(CHAR_AC_NUM IN('1483J','1484P','2082V','2289L','2778V','3345K','3367W','3385Q','3766T','4278Y','8901Y')) REFERENCES AIRCRAFT (AIR_NUM),
CHAR_DESTINATION CHAR(3) NOT NULL,
CHAR_DISTANCE INT DEFAULT 0,
CHAR_HOBBS_OUT FLOAT DEFAULT 0,
CHAR_HOBBS_RETURN FLOAT DEFAULT 0,
CHAR_HRS_FLOWN FLOAT DEFAULT 0,
CHAR_HRS_WAIT FLOAT DEFAULT 0,
CHAR_FUEL FLOAT DEFAULT 0,
CHAR_OIL INT DEFAULT 0,
CHAR_CUS_NUM CHAR(5) NOT NULL CHECK(CHAR_CUS_NUM IN('10010','10011','10012','10013','10014','10015','10016','10017','10018','10019','10020','10021','10022','10023','10024','10025','10026','10027','10028','10029','10030','10031','10032','10033','10034','10035','10036','10037','10038','10039','10040','10041','10042','10043','10044','10045','10046','10047','10048','10049','10050','10051','10052','10053','10054','10055','10056','10057','10058','10059','10060')) REFERENCES CUSTOMER (CUS_NUM),
CHAR_CHG_MILE MONEY DEFAULT 0,
CHAR_CHG_HR MONEY DEFAULT 0,
CHAR_MILE_CHG MONEY DEFAULT 0,
CHAR_WAIT_CHG MONEY DEFAULT 0,
CHAR_SUB_CHG MONEY DEFAULT 0,
CHAR_TAX_8PCT MONEY DEFAULT 0,
CHAR_TOTAL_CHG MONEY DEFAULT 0,
CHAR_PAY_TYPE VARCHAR(15) NOT NULL,
CHAR_PAY_NUM VARCHAR(25) NOT NULL,
CHAR_CUS_UPDATED VARCHAR(3) NOT NULL,
CHAR_AC_UPDATED VARCHAR(3) NOT NULL,
CHAR_PIL_UPDATED VARCHAR(3) NOT NULL,
CHAR_ENG_UPDATED VARCHAR(3) NOT NULL,
PRIMARY KEY (CHAR_TRIP)
)

The field TRANS_CHAR_TRIP references the CHAR_TRIP field, but in the data given, TRANS_CHAR_TRIP has zeroes in it while the CHAR_TRIP does not.

Upon using these inserts:

INSERT INTO TRANSACTIONS VALUES ('100000','6/11/00','CHARTER PAYMENT','CREDIT CARD','4321-3345-7678-8765',1082.94,'10012','10274');
INSERT INTO TRANSACTIONS VALUES ('100001','6/11/00','CHARTER PAYMENT','CASH','0',918.80,'10017','10275');
INSERT INTO TRANSACTIONS VALUES ('100002','6/11/00','CHARTER CHARGE','CREDIT ON ACCOUNT','0',1264.60,'10013','10276');
INSERT INTO TRANSACTIONS VALUES ('100003','6/11/00','CHARTER PAYMENT','CHECK','0000000015',1252.45,'10018','10277');
INSERT INTO TRANSACTIONS VALUES ('100004','6/11/00','PAYMENT ON ACCOUNT','CHECK','0000000105',200.00,'10014','0');
INSERT INTO TRANSACTIONS VALUES ('100005','6/11/00','PAYMENT ON ACCOUNT','CASH','0',500.00,'10017','0');

The code for the last field references the CHAR_TRIP field, but in the original CHAR_TRIP code, there is no field with a zero. The TRANSACTIONS table has blank data that has been replaced with the '0' and now I cannot figure out how to allow the data to be placed into the TRANSACTIONS table without getting this error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__TRANSACTI__TRANS__3E48226A". The conflict occurred in database "AIRLINE3", table "dbo.CHARTER", column 'CHAR_TRIP'.

Can someone please help me?

svicky9
Posting Yak Master

232 Posts

Posted - 2009-04-08 : 03:28:12

Data Integrity Issue..

My NON SQL suggestion would be if to add a test record on the Primary table with a Zero in the char_trip field.

http://www.sqlserver007.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources