Primary and foreign key issues
Hello
I'm using C# 2005 as a front end to SQL Server Express 2005.
I have two tables tblFSE and tblStatus. I want the tblFSE to be the main table for my form and tblStatus to be bound with a combobox on the main form. The combobox is to have 3 choices - Opened, In Progress and closed. They will be stored in the description field of tblStatus. On the form I want the combobox tied to pkStatus field.
So far I have...
tblFSE
IDFSE int primary key
FSEName varchar(50)
Customer varchar(50)
Date date/time
pkStatus varchar(50)
Comment varchar(150)
tblStatus
IDStatus int primary key
description varchar(50)
So I'm trying to set up the relationship and am using
Primary key table Foreign key table
tblFSE tblStatus
IDFSE IDStatus
All are type int but when I try to save it Im getting the error
'tblFSE' table saved successfully
'tblStatus' table
- Unable to create relationship 'FK_tblStatus_tblFSE'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblStatus_tblFSE". The conflict occurred in database "dbFSE", table "dbo.tblFSE", column 'IDFSE'.
Any ideas?
Ive tried to match data types.
Please help
Regards,
Steve
[1390 byte] By [
codeUp] at [2007-11-20 9:29:35]
# 1 Re: Primary and foreign key issues
A foreign key is a column referencing another column with the exact same values (with NULL as an exception). You're trying to link two primary keys. IDFSE is unique for each row in the table tblFSE. Making a reference from IDStatus to IDFSE would mean that you would have to have one row (or none) in tblStatus for each row in tblFSE.
Instead, what you want to do is to make a foreign key that links from the column tblFSE.pkStatus (which should be an int) to tblStatus.IDStatus. tblStatus is the primary key table because it's the table that holds the actual values for the pkStatus column.
# 2 Re: Primary and foreign key issues
Hello
After reviewing your suggestions I've got some problems to revise
IDFSE int primary key
FSEName varchar(50)
Customer varchar(50)
Date date/time
pkStatus varchar(50) - this should be int
Comment varchar(150)
tblStatus
IDStatus int primary key
fkStatus int - this is unnecessary
description varchar(50)
So I'm trying to set up the relationship and am using
Primary key table Foreign key table
tblStatus tblFSE
IDStatus pkStatus
This looks fine, no errors upon saving but the cell tblFSE.pkStatus doesnt accept values other than 1. There is an error in the cell <unable to read data> if I use a value other than 1.
Steve
codeUp at 2007-11-10 3:31:03 >
# 3 Re: Primary and foreign key issues
Check the data of your status table - does it contain any rows with an ID other than 1? And if it does, what are the IDs? These IDs must match exactly with the ID you're trying to use in your FSE table.
If they really do match and you still get an error, have a look at this and tell me if you did anything different:
CREATE TABLE FSE
(
FSEID int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
StatusID tinyint NOT NULL,
Name varchar(50) NOT NULL,
Customer varchar(50) NOT NULL,
Date datetime NOT NULL,
Comment varchar(255) NOT NULL
)
GO
CREATE TABLE Status
(
StatusID tinyint NOT NULL IDENTITY (1, 1) PRIMARY KEY,
Description varchar(50) NOT NULL
)
GO
ALTER TABLE FSE
ADD CONSTRAINT FK_FSE_Status FOREIGN KEY
(StatusID) REFERENCES Status (StatusID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
I tested the above by first creating three rows in the Status table. They automatically got the IDs 1, 2 and 3 (because of the IDENTITY property). Then, I inserted a row to the FSE table with a StatusID of 1. This worked. I tried changing the ID to 2, which worked, and to 3 which also worked. When I tried to change it to 4 I got an error telling me that the value was invalid. This behavior is correct (Status did not contain any row with the ID 4).
