操作数类型冲突:int 与日期不兼容 + INSERT 语句与 FOREIGN KEY 约束冲突 [英] Operand type clash: int is incompatible with date + The INSERT statement conflicted with the FOREIGN KEY constraint

问题描述

创建表飞行员(emp_num int,pl_license varchar (3),pl_ratings varchar (30),pl_med_type int，pl_med_date 日期，pl_pt135_date 日期，约束 PK_pilot 主键 (emp_num))插入飞行员(emp_num，pl_license，pl_ratings，pl_med_type，pl_med_date，pl_pt135_date)值 (101,'ATP','SEL/MEL/instr/CFII',1,12-4-2005,15-6-2005)插入飞行员(emp_num，pl_license，pl_ratings，pl_med_type，pl_med_date，pl_pt135_date)值 (104,'ATP','SEL/MEL/instr',1,10-5-2005,23-3-2006)插入飞行员(emp_num，pl_license，pl_ratings，pl_med_type，pl_med_date，pl_pt135_date)值 (105,'COM','SEL/MEL/instr/CFI',2,25-2-2006,12-2-2005)插入飞行员(emp_num，pl_license，pl_ratings，pl_med_type，pl_med_date，pl_pt135_date)值 (106,'COM','SEL/MEL/instr',2,02-4-2006,24-12-2005)插入飞行员(emp_num，pl_license，pl_ratings，pl_med_type，pl_med_date，pl_pt135_date)值 (109,'COM','SEL/MEL/instr/CFII',1,14-4-2006,21-4-2006)

我的问题是每次插入都有错误

<块引用>

操作数类型冲突:int 与日期不兼容

如何解决这个问题?

<小时>

也在这里...

创建表员工(emp_num int,约束 PK_employee 主键(emp_num)，外键(emp_num)引用pilot(emp_num)，emp_title varchar (4),emp_lname varchar (20),emp_fname varchar (30),emp_initial varchar (2),emp_dob 日期，emp_hire_date 日期，)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (100,'Mr.','Kolmycz','George','D',15-5-1942,15-3-1987)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (101,'Ms.','Lewis','Rhonda','G',19-3-1965,25-4-1988)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (102,'先生','Vandam','Rhett','',14-11-1958,20-12-1992)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (103,'Ms.','Jones','Anne','M',16-10-1974,28-8-2005)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (104,'Mr.','Lange','John','P',08-11-1971,20-10-1996)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (105,'先生','威廉姆斯','罗伯特','D',14-3-1975,08-1-2006)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (106,'Mrs.','Duzak','Jeanine','K',12-2-1968,05-1-1991)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (107,'先生','Diante','Jorge','D',21-8-1974,05-1-1991)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (108,'Mr.','Wlesenbach','Paul','R',14-2-1966,18-11-1994)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (109,'Ms.','Travis','Elizabeth','K',18-6-1961,14-4-1991)插入员工(emp_num，emp_title，emp_lname，emp_fname，emp_initial，emp_dob，emp_hire_date)值 (110,'Mrs.','Genkazi','Leighla','W',19-5-1970,01-12-1992)

<块引用>

消息 547，级别 16，状态 0，第 1 行INSERT 语句与 FOREIGN KEY 约束FK__crew__emp_num__0F975522"冲突.冲突发生在数据库melisa"、表dbo.employee"、emp_num"列中.出错这张桌子

创建表赚取(emp_num int,约束 PK_earndarating 主键(emp_num, rtg_code),rtg_code varchar(6),外键(emp_num)引用pilot(emp_num)，外键(rtg_code)引用评级(rtg_code)，Earningth_date varchar(20),)插入收入(emp_num，rtg_code，earningth_date)值(101，'CFI'，'18-Feb-98')插入收入(emp_num，rtg_code，earningth_date)值(101，'CFII'，'14-Dec-05')插入收入(emp_num，rtg_code，earningth_date)值(101，'INSTR'，'08-Nov-93')插入收入(emp_num，rtg_code，earningth_date)值(101，'MEL'，'23-Jun-94')插入收入(emp_num，rtg_code，earningth_date)值(101，'SEL'，'21-Apr-93')插入收入(emp_num，rtg_code，earningth_date)值(104，'INSTR'，'14-Jul-96')插入收入(emp_num，rtg_code，earningth_date)值(104，'MEL'，'29-Jan-97')插入收入(emp_num，rtg_code，earningth_date)值(104，'SEL'，'12-Mar-95')插入收入(emp_num，rtg_code，earningth_date)值(105，'CFI'，'18-Nov-97')插入收入(emp_num，rtg_code，earningth_date)值(105，'INSTR'，'17-Apr-95')插入收入(emp_num，rtg_code，earningth_date)值(105，'MEL'，'12-Aug-95')插入收入(emp_num，rtg_code，earningth_date)值(105，'SEL'，'23-Sep-94')插入收入(emp_num，rtg_code，earningth_date)值(106，'INSTR'，'20-Dec-95')插入收入(emp_num，rtg_code，earningth_date)值(106，'MEL'，'02-Apr-95')插入收入(emp_num，rtg_code，earningth_date)值(105，'SEL'，'10-Mar-94')插入收入(emp_num，rtg_code，earningth_date)值(109，'CFI'，'05-Nov-98')插入收入(emp_num，rtg_code，earningth_date)值(109，'CFII'，'21-Jun-03')插入收入(emp_num，rtg_code，earningth_date)值(109，'INSTR'，'23-Jul-96')插入收入(emp_num，rtg_code，earningth_date)值(109，'MEL'，'15-Marc-97')插入收入(emp_num，rtg_code，earningth_date)值(109，'SEL'，'05-Feb-96')插入收入(emp_num，rtg_code，earningth_date)值(109，'SES'，'12-May-96')

解决方案

这个表达式 12-4-2005 是一个计算出来的整数，值为 -1997.你应该这样做，而不是在 '2005-04-12' 前后加上 '.

create table pilot (
emp_num int,
pl_license varchar (3),
pl_ratings varchar (30),
pl_med_type int,
pl_med_date date,
pl_pt135_date date,
constraint PK_pilot primary key (emp_num)
)

insert into pilot(emp_num,pl_license,pl_ratings,pl_med_type,pl_med_date,pl_pt135_date)
values (101,'ATP','SEL/MEL/instr/CFII',1,12-4-2005,15-6-2005)
insert into pilot(emp_num,pl_license,pl_ratings,pl_med_type,pl_med_date,pl_pt135_date)
values (104,'ATP','SEL/MEL/instr',1,10-5-2005,23-3-2006)
insert into pilot(emp_num,pl_license,pl_ratings,pl_med_type,pl_med_date,pl_pt135_date)
values (105,'COM','SEL/MEL/instr/CFI',2,25-2-2006,12-2-2005)
insert into pilot(emp_num,pl_license,pl_ratings,pl_med_type,pl_med_date,pl_pt135_date)
values (106,'COM','SEL/MEL/instr',2,02-4-2006,24-12-2005)
insert into pilot(emp_num,pl_license,pl_ratings,pl_med_type,pl_med_date,pl_pt135_date)
      values (109,'COM','SEL/MEL/instr/CFII',1,14-4-2006,21-4-2006)

My question is there is an error in every insert

Operand type clash: int is incompatible with date

How to fix this?

---

Also here ...

create table employee (
emp_num int,
constraint PK_employee primary key (emp_num),
foreign key(emp_num) references pilot(emp_num),
emp_title varchar (4),
emp_lname varchar (20),
emp_fname varchar (30),
emp_initial varchar (2),
emp_dob date,
emp_hire_date date, 
)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (100,'Mr.','Kolmycz','George','D',15-5-1942,15-3-1987)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (101,'Ms.','Lewis','Rhonda','G',19-3-1965,25-4-1988)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (102,'Mr.','Vandam','Rhett',' ',14-11-1958,20-12-1992)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (103,'Ms.','Jones','Anne','M',16-10-1974,28-8-2005)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (104,'Mr.','Lange','John','P',08-11-1971,20-10-1996)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (105,'Mr.','williams','Robert','D',14-3-1975,08-1-2006)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (106,'Mrs.','Duzak','Jeanine','K',12-2-1968,05-1-1991)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (107,'Mr.','Diante','Jorge','D',21-8-1974,05-1-1991)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (108,'Mr.','Wlesenbach','Paul','R',14-2-1966,18-11-1994)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (109,'Ms.','Travis','Elizabeth','K',18-6-1961,14-4-1991)
insert into employee(emp_num,emp_title,emp_lname,emp_fname,emp_initial,emp_dob,emp_hire_date)
values (110,'Mrs.','Genkazi','Leighla','W',19-5-1970,01-12-1992)        

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__crew__emp_num__0F975522". The conflict occurred in database "melisa", table "dbo.employee", column 'emp_num'. error on this table

create table earndrating(
emp_num int,
constraint PK_earndarating primary key(emp_num, rtg_code),
rtg_code varchar(6),
foreign key (emp_num) references pilot(emp_num),
foreign key(rtg_code) references rating(rtg_code),
earningth_date varchar(20),
)
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(101,'CFI','18-Feb-98' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(101,'CFII','14-Dec-05' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(101,'INSTR','08-Nov-93' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(101,'MEL','23-Jun-94' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(101,'SEL','21-Apr-93' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(104,'INSTR','14-Jul-96' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(104,'MEL','29-Jan-97' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(104,'SEL','12-Mar-95' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(105,'CFI','18-Nov-97' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(105,'INSTR','17-Apr-95' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(105,'MEL','12-Aug-95' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(105,'SEL','23-Sep-94' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(106,'INSTR','20-Dec-95' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(106,'MEL','02-Apr-95' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(105,'SEL','10-Mar-94' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(109,'CFI','05-Nov-98' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(109,'CFII','21-Jun-03' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(109,'INSTR','23-Jul-96' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(109,'MEL','15-Marc-97' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(109,'SEL','05-Feb-96' )
    insert into earndrating(emp_num,rtg_code,earningth_date)
    values(109,'SES','12-May-96' )

解决方案

This expression 12-4-2005 is a calculated int and the value is -1997. You should do like this instead '2005-04-12' with the ' before and after.

这篇关于操作数类型冲突:int 与日期不兼容 + INSERT 语句与 FOREIGN KEY 约束冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！