SQL ，出现外键约束的错误信息 [英] SQL , the error message appeared with foreign key constraint

问题描述

 IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'animal_vaccinations')
   DROP TABLE animal_vaccinations
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'animals')
   DROP TABLE animals
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'vaccine_codes')
   DROP TABLE vaccine_codes
 IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'intake_codes')
   DROP TABLE intake_codes

go
--1.2 CREATE TABLE: intake_codes
create table intake_codes(
        intake_code varchar(1) not null ,
        intake_desc varchar(200) not null)

go

-- 1.3 CREATE TABLE: vaccine_codes
create table vaccine_codes(
        vac_code varchar(50) not null,
        vac_desc varchar(200) not null)

 go

-- 1.4 CREATE TABLE: animals
create table animals(
        anm_id int identity(3,3) not null,
        anm_name varchar(100) not null,
        anm_species char(100) not null,
        anm_breed varchar(100) not null,
        anm_age decimal(4,2) not null,
        anm_gender char(1) default 'F' not null,
        anm_size char(5) not null,
        anm_spayorneuter char(1) null,
        anm_intake_date datetime default getdate() not null,
        anm_intake_code varchar(1) not null,
        anm_notes varchar(200) not null)

go


-- 1.5 CREATE TABLE: animal_vaccinations
create table animal_vaccinations(
        av_anm_id int not null,
        av_vac_code varchar(50) not null,
        av_vac_date datetime not null,
        primary key (av_anm_id, av_vac_code))

go

-- Part 2: Add Table Constraints (PKs and Checks ONLY) --


-- 2.1 ADD TABLE CONSTRAINTS: animal_vaccinations
Alter table animal_vaccinations
add constraint uq_animal_vaccinations Unique (av_anm_id, av_vac_code),
constraint ck_av_vac_code CHECK (av_vac_code = 'CPV,CDV,CBV,CR,CL,CRV,FHV1,FCV,FPV,FR')


go

-- 2.2 ADD TABLE CONSTRAINTS: animals

 Alter table animals
ADD constraint pk_anm_id PRIMARY KEY (anm_id)




-- 2.3 ADD TABLE CONSTRAINTS: intake_codes
Alter table intake_codes
ADD CONSTRAINT pk_intake_code PRIMARY KEY (intake_code)


go

-- 2.4 ADD TABLE CONSTRAINTS: vaccine_codes

ALTER TABLE vaccine_codes
ADD constraint pk_vac_code PRIMARY KEY (vac_code)

go




-- Part 3: Add Foreign Key Constraints 


-- 3.1 ADD FOREIGN KEY CONSTRAINTS: animal_vaccinations
ALTER TABLE animal_vaccinations
ADD constraint fk_av_vac_code FOREIGN KEY (av_vac_code) REFERENCES vaccine_codes(vac_code),
    constraint fk_av_anm_id FOREIGN KEY (av_anm_id) REFERENCES animals(anm_id)

go

-- 3.2 ADD FOREIGN KEY CONSTRAINTS: animals
 ALTER TABLE animals
 ADD constraint fk_anm_intake_code FOREIGN KEY (anm_intake_code) REFERENCES intake_codes(intake_code)

go  
-- Part 4: Insert Base Data as shown in the assignment. Order is important. Do a table
-- at a time and place a go statement after each set of inserts. 

-- 4.1 Add Your First Table Rows Here

insert into animals
(anm_name,anm_species,anm_breed,anm_age,anm_gender,anm_size,anm_spayorneuter,anm_intake_date,anm_intake_code,anm_notes)
 values ('Tom', 'Canine', 'Mix.pit/Poodle', 8.80, 'M', 'SM', 'Y', 12/23/16,'C', 'Needs additional water/Hoursebroken'),
     ('Chi', 'Feline', 'House', 0.80, 'F', 'SM', 'Y', 11/11/16, 'F', 'Very affectionate' ),
         ('Lin', 'Canine', 'Beagle', 2.30, 'M', 'SM', 'N', 1/17/16, 'B', 'Hoursebroken/loves to play ball'),
         ('Frisky', 'Feline', 'Mix.pit/Poodle', 11.50, 'F', 'Med',' N', 12/2/16, 'B', 'Best in low activity home'),
         ('Shady', 'Canine', 'House', 4.50, 'F', 'Med', 'Y', 1/16/17, 'C ', 'Null'),
         ('Sparky', 'Canine', 'Mix.pit/Poodle', 4.10, 'F', 'Lrg', 'N', 1/17/17, 'F', 'Not housebroken/love kids/gentle'),
         ('Lucy', 'Feline', 'House', 1.10, 'F', 'XL', 'Y', 12/3/16, 'E', 'Null'),
         ('Blue', 'Canine', 'Lab/Pit.Mixed', 1.20, 'F', 'SM', 'N', 2/4/17, 'B', 'Not housebroken')

go

-- 4.2 Add Your Second Table Rows Here
insert into vaccine_codes(
vac_code, 
vac_desc)
values ('CPV',' Canine Parvovirus'),
('CDV',' Canine Distemper'), 
('CBV',' Canine Bordetella'), 
('CR',' Canine Rabies'),
(' CL',' Canine Lepto'),
(' CRV',' Canine Rattlesnake Vaccine'),
(' FHV1',' Feline Herpesvirus'),
(' FCV',' Feline Calicivirus'),
(' FPV','Feline Panleukopenia'),
(' FR',' Feline Rabies')
go

-- 4.3 Add Your Third Table Rows Here
insert into intake_codes (
intake_code,
intake_desc)
values ('B',' Stray/At Large'),
       ('C',' Relinquished by Owner'),
       ('D',' Owner Intended Euthanasia'),
       ('E',' Transferred in'),
       ('F',' Other Intakes')
go

出现错误信息

INSERT 语句与 FOREIGN KEY 约束fk_anm_intake_code"冲突.冲突发生在数据库IST359_M003_lwang105"，表dbo.intake_codes"，列intake_code".

The INSERT statement conflicted with the FOREIGN KEY constraint "fk_anm_intake_code". The conflict occurred in database "IST359_M003_lwang105", table "dbo.intake_codes", column 'intake_code'.

请大家帮忙

推荐答案

难道你不应该在动物之前插入摄入代码吗?您插入与尚不存在的摄入代码相关的动物

shouldn't you do the insert into intake codes before animals? you insert into animals with a relation to intake codes that does not exist yet

这篇关于SQL ，出现外键约束的错误信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！