Hi Guys

Was hoping someone could help me out with a wee problem im having...

I have two tables: one called "fixtures" and one called "teams". The "fixtures" table has 2 fields called "homeTeamID" and "awayTeamID" which are set as foreign keys to the "teamID" field within the "teams" table.

Now i would like to delete an entry from the "fixtures" table but I am getting the error "Cannot delete or update a parent row: a foreign key constraint fails". As i understand it i should be able to delete a row from the fixtures table without deleting anything from the teams table, but i dont seem to be allowed.

Is it the way i have set up the foreign keys? or is this normal?

The lines i used for creating the foreign keys are:

  ADD CONSTRAINT `fixtures_ibfk_2` FOREIGN KEY (`homeTeamID`) REFERENCES `team` (`teamID`),
  ADD CONSTRAINT `fixtures_ibfk_3` FOREIGN KEY (`awayTeamID`) REFERENCES `team` (`teamID`);

Cheers
Stoot

Hi Stoot

Do you have the full DML for the tables as I do not believe what you have outlined above would cause the problem you are having.

For instance this should mock what you have said but works fine:

CREATETABLE [dbo].[teams](
[teamID] [int] IDENTITY(1,1)NOTNULL,
[name] [nvarchar](50)COLLATE Latin1_General_CI_AS NOTNULL,
CONSTRAINT [PK_teams] PRIMARYKEYCLUSTERED(
[teamID] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY]
)ON [PRIMARY]
GO
CREATETABLE [dbo].[fixtures](
[id] [int] IDENTITY(1,1)NOTNULL,
[homeTeamID] [int] NOTNULL,
[awayTeamID] [int] NOTNULL,
CONSTRAINT [PK_fixtures] PRIMARYKEYCLUSTERED(
[id] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY]
)ON [PRIMARY]
GO
ALTERTABLE [dbo].[fixtures] WITHCHECKADDCONSTRAINT [FK_fixtures_awayteams] FOREIGNKEY([awayTeamID])
REFERENCES [dbo].[teams] ([teamID])
GO
ALTERTABLE [dbo].[fixtures] CHECKCONSTRAINT [FK_fixtures_awayteams]
GO
ALTERTABLE [dbo].[fixtures] WITHCHECKADDCONSTRAINT [FK_fixtures_hometeams] FOREIGNKEY([homeTeamID])
REFERENCES [dbo].[teams] ([teamID])
GO
ALTERTABLE [dbo].[fixtures] CHECKCONSTRAINT [FK_fixtures_hometeams]
INSERTINTO [dbo].[teams] ([Name])VALUES('Team A')
INSERTINTO [dbo].[teams] ([Name])VALUES('Team B')
INSERTINTO [dbo].[fixtures] (homeTeamID, awayTeamID)VALUES(1, 2)
INSERTINTO [dbo].[fixtures] (homeTeamID, awayTeamID)VALUES(2, 1)
DELETEFROM [dbo].[fixtures] WHERE ID = 1

v

Sorry DDL not DML