sql - How do I insert values into empty tables that have foreign keys attached to them? -

sql - How do I insert values into empty tables that have foreign keys attached to them? -

i have several tables created, lot of them referencing each other foreign keys. created them they're empty in sql server 2012.

create table candidate ( candidateid int primary key, qualificationid int not null, trainingsessionid int not null, prerequesiteid int not null, courseid int not null, qualification nvarchar(20), jobhistoryid int not null, name nvarchar(20) ) insert candidate values (1, 1, 1, 1, 1, 'plumer', 1, 'jordan')

i want error

the insert statement conflicted foreign key constraint "fk__candidate__quali__29572725". conflict occurred in database "tec", table "dbo.qualifications", column 'qualificationid'.

my qualification table

create table qualifications ( qualificationid int primary key, name nvarchar(20) )

and have other tables

create table trainingsession ( trainingsessionid int primary key, candidateid int foreign key references candidate(candidateid), courseid int not null, prerequesiteid int not null, qualificationid int not null, trainingname nvarchar(20) )

and said alot of them reference each other.

so how insert values these tables when error?

edit: after created tables altered them add together foreign keys not there

alter table candidate add together foreign key (qualificationid) references qualifications(qualificationid) alter table candidate add together foreign key (trainingsessionid) references trainingsession(trainingsessionid)

edit 2: seek , insert candidate, error above, seek inset training session, same error in candidate

the insert statement conflicted foreign key constraint "fk__trainings__candi__173876ea". conflict occurred in database "tec", table "dbo.candidate", column 'candidateid'.

there's don't understand database design. @ this:

create table candidate ( candidateid int primary key, qualificationid int not null, trainingsessionid int not null, /* reference trainingsession?*/ prerequesiteid int not null, courseid int not null, qualification nvarchar(20), jobhistoryid int not null, name nvarchar(20) ) create table trainingsession ( trainingsessionid int primary key, candidateid int foreign key references candidate(candidateid), courseid int not null, prerequesiteid int not null, qualificationid int not null, trainingname nvarchar(20) )

they both seem referencing each other seems weird me , not right. there should ideally 1 reference between them.

also, if trainingsessionid in candidate table not foreign key, why there? can value via bring together query. isn't reason why candidateid foreign key in trainingsession table?

update:

the problem having :

"you need trainingsesssionid value in trainingsession table insert row in candidate table. since don't have one, gives foreign key constraint error. similarly, need candidateid value in candidate table insert row in trainingsession table. gives foreign key constraint error. there's no escaping this. hence, design flawed.

what can is:

remove trainingsessionid column candidate table or remove candidateid column trainingsession table. point need remove 1 of these foreign key references. one? depends on particular scenario's , use-cases. main point here you'll have drop 1 of these foreign key references. redundant anyways. you need 1 foreign key create relationship between 2 tables. due existence of (note:single) foreign key, can relate between 2 tables , fetch values either tables based on foreign key. there no need have foreign key.

secondly, since qualifications has no foreign key reference, in context seems master table. so, insertion begins here. then, you'd have insert in candidate table (in case trainingsessionid not foreign key in table) , in trainingsession table.

hope helps!!!

sql sql-server