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

Data Corruption Issues

Foreign Key Issue

Author

Topic

Mattx020
Starting Member

3 Posts

Posted - 2014-11-11 : 16:25:54

I'm getting this error "INSERT statement conflicted with the FOREIGN KEY constraint "FK__Movies__ActorID__15502E78".
And I don't know how to fix it if anyone can point me in the right direction it would be great.

Use Master

Create Database Oliveira_Henrys_Movies

Go

Use Oliveira_Henrys_Movies

Create table Directors
(
DirectorID Varchar(2) NOT NULL Primary Key,
DirectorName Varchar(50) NOT NULL,
DirectorBYear Varchar(10) NOT NULL ,
DirectorDyear Varchar(10),
DirectorStatues Varchar(15) NOT NULL,
);
Go
Insert Into Directors
Values
(1,'Steven Spielberg','1946',null,'Alive'),
(2,'Stanley Kubrick','1928','1999','Deceased'),
(3,'George Lucas','1944',null,'Alive'),
(4,'Christopher Nolan','1970',null,'Alive');

Create table Actors
(
ActorID Varchar(2) NOT NULL Primary Key,
ActorsName Varchar(50) NOT NULL,
ActorBPlace Varchar(50) NOT NULL,
ActorBYear Varchar(10) NOT NULL,
ActorDYear Varchar(10),
ActorStatues Varchar(15) NOT NULL,
);
Go
Insert Into Actors
Values
(1,'Matthew Modine','Loma Linda','1959',null,'Alive'),
(2,'Dorian Harewood','Dayton','1950',null,'Alive'),
(3,'Hayden Christensen','Vancouver','1981',null,'Alive'),
(4,'Christian Bale','Haverfordwest','1974',null, 'Alive'),
(5,'Natalie Portman','Jerusalem','1981',null,'Alive'),
(6,'Heath Ledger','Manhattan','1979','2008','Deceased'),
(8,'Roy Scheider','Orange','1932','2008','Deceased'),
(9,'Lorraine Gary','New York City','1937',null,'Alive');

Create table Movies
(
MovieID Varchar(2) NOT NULL Primary Key,
MovieTitle Varchar(50) NOT NULL,
MovieYear Varchar(10) NOT NULL,
MovieRating Varchar(5) NOT NULL,
MovieStars Varchar(1) NOT NULL,
DirectorID Varchar(2) NOT NULL Foreign Key References Directors(DirectorID),
DirectorName Varchar(50) NOT NULL,
ActorID Varchar(2) NOT NULL Foreign key References Actors(ActorID),
ActorName Varchar(50) NOT NULL,
);
Go
Insert Into Movies
Values
(1,'The Dark Knight','2008','PG-13','4', 4,'Christopher Nolan', 4 + 6,'Christian Bale' + 'Heath Ledger'),
(2,'Jaws','1975','PG','3', 1,'Steven Spielberg', 8 + 9,'Roy Scheider' + 'Lorraine Gary'),
(3,'Star Wars: Revenge of the Sith','2005','PG-13','3',3,'George Lucas', 3 + 5,'Hayden Christensen' + 'Natalie Portman'),
(4,'Full Metal Jacket','1987','R','3', 2,'Stanley Kubrick', 1 + 2 ,'Stanley Kubrick' + 'Dorian Harewood');

Create table MovieType
(
MovieGenre Varchar(3) NOT NULL Primary Key,
MovieID Varchar(2) NOT NULL Foreign Key References Movies(MovieID),
MovieGenreDes Varchar(20) NOT NULL,
);
Go
Insert Into MovieType
Values
('Act', 1, 'Action'),
('Thr', 2, 'Thriller'),
('Sci', 3, 'Science Fiction'),
('Dra', 4, 'Drama');

Create table MovieAwards
(
MovieNom Varchar(3) NOT NULL Primary Key,
MovieID Varchar(2) NOT NULL Foreign Key References Movies(MovieID),
MovieWon Varchar(3) NOT NULL,
);
Go
Insert Into MovieAwards
Values
('97', 1, '127'),
('14', 2, '10'),
('35', 3, '16'),
('7', 4, '9');

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-11 : 16:50:25

Here:

Insert Into Movies
Values
(1,'The Dark Knight','2008','PG-13','4', 4,'Christopher Nolan', 4 + 6,'Christian Bale' + 'Heath Ledger'),

You are attempting to insert an actor with id 4+6 = 10, but there is no actor with that id in your actors table. Perhaps you meant to insert two rows for that movie, one with Bale and the other with Ledger.

Mattx020
Starting Member

3 Posts

Posted - 2014-11-11 : 17:22:43

quote:
Originally posted by gbritton

Here:

Insert Into Movies
Values
(1,'The Dark Knight','2008','PG-13','4', 4,'Christopher Nolan', 4 + 6,'Christian Bale' + 'Heath Ledger'),

You are attempting to insert an actor with id 4+6 = 10, but there is no actor with that id in your actors table. Perhaps you meant to insert two rows for that movie, one with Bale and the other with Ledger.

I see, what I'm trying to do is put both does actors on the same column, can you tell me how?

Mattx020
Starting Member

3 Posts

Posted - 2014-11-11 : 17:35:10

quote:
Originally posted by Mattx020

quote:
Originally posted by gbritton

Here:

Insert Into Movies
Values
(1,'The Dark Knight','2008','PG-13','4', 4,'Christopher Nolan', 4 + 6,'Christian Bale' + 'Heath Ledger'),

You are attempting to insert an actor with id 4+6 = 10, but there is no actor with that id in your actors table. Perhaps you meant to insert two rows for that movie, one with Bale and the other with Ledger.

I see, what I'm trying to do is put both actors on the same column, can you tell me how?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-11 : 18:19:39

quote:
Originally posted by Mattx020

quote:
Originally posted by gbritton

Here:

Insert Into Movies
Values
(1,'The Dark Knight','2008','PG-13','4', 4,'Christopher Nolan', 4 + 6,'Christian Bale' + 'Heath Ledger'),

You are attempting to insert an actor with id 4+6 = 10, but there is no actor with that id in your actors table. Perhaps you meant to insert two rows for that movie, one with Bale and the other with Ledger.

I see, what I'm trying to do is put both does actors on the same column, can you tell me how?

You can't do that with your current schema. Add 2 rows instead. You can then output the data so that it's comma separated.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources