Foreign key constraint - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Foreign key constraint

Author

Topic

cipi20052000
Starting Member

5 Posts

Posted - 2009-11-22 : 18:25:44

Hi! I have two tables in a database which I've created like this:


use SHOOTER 
create table Account (id_player int not null identity(1,1),
first_name nvarchar(20) not null, last_name nvarchar(20) not null, 
address nvarchar(50) not null, city nvarchar(20) not null, district nvarchar(20) not null, 
constraint pk_id_player_A primary key(id_player))	
	
use SHOOTER
create table Player (id_player int not null identity(1,1), 
username nvarchar(30) not null, password nvarchar(20) not null, 
security_word nvarchar(15) not null, 
constraint pk_id_player_P primary key(id_player), 
constraint fk_id_player_P foreign key(id_player) references Account(id_player))

My database name is SHOOTER. I have a problem when I try to make an INSERT into the Player table. I get the following error: <The INSERT statement conflicted with the FOREIGN KEY constraint "fk_id_player_I"
The conflict occurred in database "SHOOTER", table "dbo.Player", column 'id_player'>

CAN ANYONE PLEASE HELP ME?

monty

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-22 : 19:45:21

The id_player that you're trying to insert into the Player table needs to exist in the Account table first.

Also, id_player shouldn't be an identity column in the Player table.

cipi20052000
Starting Member

5 Posts

Posted - 2009-11-23 : 00:26:12

It worked. Thanks a lot! And I got another question..... I want using ADO.NET to insert a row into this two tables. A way is to insert a row into Account table, and then into the Player table. But when I insert a row into the Player table, I have to enter a value for id_player, because it isn't identity any more. Is there any way to insert a row into the Account table and then to insert values into Player table without specifying a value for id_player? Can I create a view which contains all the columns of these two tables and then to use the INSERT statement on it? If I can, should my view contain the column "id_player" twice?

monty

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-23 : 00:28:55

no, because it's a foreign key to the account table

cipi20052000
Starting Member

5 Posts

Posted - 2009-11-23 : 00:30:34

Can I create a view which contains all the columns of these two tables and then to use the INSERT statement on it? If I can, should my view contain the column "id_player" twice?

monty

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-23 : 00:35:53

The purpose of a Foreign Key is to ENFORCE data integrity. In this case it is to be certain that no Player can exist unless it is already in the Account table.

I don't know your business rules, but this makes sense generally.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources