INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@.@.ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

The error message is pretty clear. You have specified key values that doesn't exist in the parent table. The message indicates the constraint and table so you can find out which tables are involved. I think your problem is the following:

You are using IDENTITY column as primary key. And you have another column that references the identity column in the same table. When you do a DELETE it doesn't reset the seed for identity column. So if you had performed data manipulations before you are going to get new identity value based on the previous generated values and it will not start at 1 or whatever your seed is. TRUNCATE TABLE on the other hand will reset the identity seed to the original value. Or you can also use DBCC CHECKIDENT. Typically when you store this sort of parent child relationship you should have the root's parent as NULL to simplify your operations rather than the root id itself.

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

|||DBCC CHECKIDENT is the only way if you have references. Could you post some sample code that demonstrates the problem and also mention the version of SQL Server? I haven't had any issues with DBCC CHECKIDENT to reseed identity values. And I don't know of any bug in SQL Server related to the DBCC CHECKIDENT command.|||I need to drop the constraint, do my inserts, then recreate the constraint at the end. I am working on the syntax for that drop and re-create..|||I've had this error msg and simply deleted the relationship, and in Enterprise Manager redefined the relationship by clicking FIRST on the primary table key and then linking it to the other table's key.|||

I get this problem as well and have the following table and with constraints:

CREATE TABLE [BudgetDetail] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,

[BudgetHeaderID] [int] NULL ,
[SupersededDetailID] [int] NULL

CONSTRAINT [PK_BudgetDetail] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY] ,
CONSTRAINT [FK_BudgetDetail_BudgetDetail_SupersededDetailID] FOREIGN KEY
(
[SupersededDetailID]
) REFERENCES [BudgetDetail] (
[RecordID]
)
) ON [PRIMARY]
END

GO

The delete fails with the conbstraint error even though the [SupersededDetailID] is set to NULL.

DELETE FROM BudgetDetail WHERE BudgetHeaderID = 1885 AND RecordID = 269673

DELETE statement conflicted with COLUMN SAME TABLE REFERENCE constraint 'FK_BudgetDetail_BudgetDetail_SupersededDetailID'. The conflict occurred in database 'SitestreamTest', table 'BudgetDetail', column 'SupersededDetailID'.
The statement has been terminated.

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@.@.ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

The error message is pretty clear. You have specified key values that doesn't exist in the parent table. The message indicates the constraint and table so you can find out which tables are involved. I think your problem is the following:

You are using IDENTITY column as primary key. And you have another column that references the identity column in the same table. When you do a DELETE it doesn't reset the seed for identity column. So if you had performed data manipulations before you are going to get new identity value based on the previous generated values and it will not start at 1 or whatever your seed is. TRUNCATE TABLE on the other hand will reset the identity seed to the original value. Or you can also use DBCC CHECKIDENT. Typically when you store this sort of parent child relationship you should have the root's parent as NULL to simplify your operations rather than the root id itself.

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

|||DBCC CHECKIDENT is the only way if you have references. Could you post some sample code that demonstrates the problem and also mention the version of SQL Server? I haven't had any issues with DBCC CHECKIDENT to reseed identity values. And I don't know of any bug in SQL Server related to the DBCC CHECKIDENT command.|||I need to drop the constraint, do my inserts, then recreate the constraint at the end. I am working on the syntax for that drop and re-create..|||

I've had this error msg and simply deleted the relationship, and in Enterprise Manager redefined the relationship by clicking FIRST on the primary table key and then linking it to the other table's key.|||

I get this problem as well and have the following table and with constraints:

CREATE TABLE [BudgetDetail] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,

[BudgetHeaderID] [int] NULL ,
[SupersededDetailID] [int] NULL

CONSTRAINT [PK_BudgetDetail] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY] ,
CONSTRAINT [FK_BudgetDetail_BudgetDetail_SupersededDetailID] FOREIGN KEY
(
[SupersededDetailID]
) REFERENCES [BudgetDetail] (
[RecordID]
)
) ON [PRIMARY]
END

GO

The delete fails with the conbstraint error even though the [SupersededDetailID] is set to NULL.

DELETE FROM BudgetDetail WHERE BudgetHeaderID = 1885 AND RecordID = 269673

DELETE statement conflicted with COLUMN SAME TABLE REFERENCE constraint 'FK_BudgetDetail_BudgetDetail_SupersededDetailID'. The conflict occurred in database 'SitestreamTest', table 'BudgetDetail', column 'SupersededDetailID'.
The statement has been terminated.

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@.@.ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

The error message is pretty clear. You have specified key values that doesn't exist in the parent table. The message indicates the constraint and table so you can find out which tables are involved. I think your problem is the following:

You are using IDENTITY column as primary key. And you have another column that references the identity column in the same table. When you do a DELETE it doesn't reset the seed for identity column. So if you had performed data manipulations before you are going to get new identity value based on the previous generated values and it will not start at 1 or whatever your seed is. TRUNCATE TABLE on the other hand will reset the identity seed to the original value. Or you can also use DBCC CHECKIDENT. Typically when you store this sort of parent child relationship you should have the root's parent as NULL to simplify your operations rather than the root id itself.

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

|||DBCC CHECKIDENT is the only way if you have references. Could you post some sample code that demonstrates the problem and also mention the version of SQL Server? I haven't had any issues with DBCC CHECKIDENT to reseed identity values. And I don't know of any bug in SQL Server related to the DBCC CHECKIDENT command.|||I need to drop the constraint, do my inserts, then recreate the constraint at the end. I am working on the syntax for that drop and re-create..|||

I've had this error msg and simply deleted the relationship, and in Enterprise Manager redefined the relationship by clicking FIRST on the primary table key and then linking it to the other table's key.

INSERT statement conflicted with COLUMN FOREIGN KEY constraint...

Hi there,

I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.

This is the error i get:

"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "

Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.

Thank you

Melanie

There is a conflict, you say that staffNo can be null, however, PropStaffFK says that it can not, since you have no staff member with an id of null.|||

Thanks for your reply Motley.

So does that mean if you have a foreign key referencing a primary key from another table that it can't be null?

That makes sence that it would be true, however, in one of my tables TblProperty i have the column staffNo and in this column i want to allow null values to indicate that the property hasn't been approved by a memeber of staff (if it has been approved then the member of staff that approved the property, thier staffNo will be inserted in there) . Do you know of any way to do this?

Thanks

Melanie

|||

Here's the problem. In order to set a FK contraint, you need to reference a primary key table (That has a primary key set). You can't set a primary key index on a column that is nullable. Since the Primary key table doesn't have a NULL-value as one of it's values, then the FK table can not have null as one of it's values either. (Actually, it wouldn't really make all that much sense anyhow, because even if there was a null vaue in the PK table, since null represents UNKNOWN, and UNKNOWN=UNKNOWN is always false, it still shouldn't work even if null was allowed in the PK table).

A workaround is to create an entry into the PK table for unassigned values, like "0" or "-1". Then make the column in the FK table, not-nullable, with a default constraint of "0" or "-1".

|||Another option is to remove the primary key on the primary key table, enter a null value in the old PK column, then apply a unique constraint on that column, then reestablish your FK relationship. Of course, if the old PK table column as of type identity, you'll have to remove that since identity columns can not contain null either.|||

Yeah thats the thing, i have the primary key column set as type Identity.

I like the first idea you mentioned, by putting a 0 or -1 in there replacing the null entry.

Could use the identity type below ( seed 0 and increment 1)

IDENTITY (0, 1)

then the null entry will be replaced by '0' by inserting a fake entry at the start.

Thanks for your help Motley i'll go and try this out.

Melanie

|||You can just remove the indentity from the column, insert your record, and add the identity back on the column too, that works too.|||

How would i carry that out?

I mean to add the identity back to the column after inserting a row of data, wouldn't this require me dropping the table which in turn removes the data entered previouly entered?

Could you tell me the way of doing this without dropping the table?

Thank you

Melanie

|||

I use management studio, so while technically you are correct, it hides it all in the background for me (Copying all the data), and if the table isn't too big, it's quick. But as far as I know, you are correct, you must create a new table to reinstate the identity.

|||

Thanks Motley

What do you suggest for me then?

Guess i'll have to go with '0' to replace the null entry.

Melanie

|||

You might try doing the insert by doing a SET IDENTITY_INSERT {Your table} ON then insert the record, then SET IDENTITY_INSERT {Your table} OFF

INSERT statement conflicted with COLUMN CHECK constraint.

Hi,
I'm attempting to insert a new row into an SQL table using ADO written
with c# and stored procedures.
The ADO code is running OK, and i know it should work as i have used
equivilent code succeffully for other tables. However i am getting the
following error:
{"INSERT statement conflicted with COLUMN CHECK constraint 'CK
tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
table 'tblPatient', column 'pntStage'.\r\nThe statement has been
terminated." }
pntStage has data type NVarChar, and maximum length 8. The values i am
attempting to input do not violate these criteria. I have deleted the
complete row and added it again incase there was some hidden input
mask, this has not solved the problem.
Any ideas what the problem might be? Here's my stored procedure if
taht's any help.
CREATE PROCEDURE proc_InsertPatient
(@.patientNo int output,
@.pntUnitID nvarchar(15),
@.pntTitle nvarchar(4),
@.pntFName nvarchar(20),
@.pntLName nvarchar(30),
@.pntDOB nvarchar(8),
@.pntSex nvarchar(1),
@.pntAddress1 nvarchar(150),
@.pntAddress2 nvarchar(150),
@.pntAddress3 nvarchar(150),
@.pntCountryNo int output,
@.pntPostcode nvarchar (10),
@.pntHPhone nvarchar (14),
@.pntWPhone nvarchar (14),
@.pntMobPhone nvarchar (14),
@.pntEmail nvarchar (50),
@.pntStage nvarchar (8),
@.pntT tinyint,
@.pntN tinyint,
@.pntM tinyint,
@.pntPreviousTreatments char (1000),
@.pntFurtherNotes char (1000)
)
AS
INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
pntDOB, pntSex, pntAddress1,
pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
pntWPhone,
pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
pntPreviousTreatments, pntFurtherNotes)
VALUES
(@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
@.pntAddress1,
@.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
@.pntWPhone,
@.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
@.pntPreviousTreatments, @.pntFurtherNotes)
SELECT @.patientNo=@.@.IDENTITY
GO
Thanks.Hi
pntStage might have a check constraint that specifics that the values can
only be in a certain range or of a certain patters. Look at the Column's
check constrains though EM to see what has been setup.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122817569.583719.19100@.g43g2000cwa.googlegroups.com...
> Hi,
> I'm attempting to insert a new row into an SQL table using ADO written
> with c# and stored procedures.
> The ADO code is running OK, and i know it should work as i have used
> equivilent code succeffully for other tables. However i am getting the
> following error:
> {"INSERT statement conflicted with COLUMN CHECK constraint 'CK
> tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
> table 'tblPatient', column 'pntStage'.\r\nThe statement has been
> terminated." }
> pntStage has data type NVarChar, and maximum length 8. The values i am
> attempting to input do not violate these criteria. I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
> Any ideas what the problem might be? Here's my stored procedure if
> taht's any help.
> CREATE PROCEDURE proc_InsertPatient
> (@.patientNo int output,
> @.pntUnitID nvarchar(15),
> @.pntTitle nvarchar(4),
> @.pntFName nvarchar(20),
> @.pntLName nvarchar(30),
> @.pntDOB nvarchar(8),
> @.pntSex nvarchar(1),
> @.pntAddress1 nvarchar(150),
> @.pntAddress2 nvarchar(150),
> @.pntAddress3 nvarchar(150),
> @.pntCountryNo int output,
> @.pntPostcode nvarchar (10),
> @.pntHPhone nvarchar (14),
> @.pntWPhone nvarchar (14),
> @.pntMobPhone nvarchar (14),
> @.pntEmail nvarchar (50),
> @.pntStage nvarchar (8),
> @.pntT tinyint,
> @.pntN tinyint,
> @.pntM tinyint,
> @.pntPreviousTreatments char (1000),
> @.pntFurtherNotes char (1000)
> )
> AS
> INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
> pntDOB, pntSex, pntAddress1,
> pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
> pntWPhone,
> pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
> pntPreviousTreatments, pntFurtherNotes)
> VALUES
> (@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
> @.pntAddress1,
> @.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
> @.pntWPhone,
> @.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
> @.pntPreviousTreatments, @.pntFurtherNotes)
> SELECT @.patientNo=@.@.IDENTITY
> GO
>
> Thanks.
>|||Please post the table DDL (including the CHECK constraint) and the @.pntStage
value you are trying to insert. This will help us identify the cause of
your problem.

> I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
I'm not sure I understand what you mean by 'deleted the complete row'.
Since the insert failed, I wouldn't expect you would find the row in
tblPatient.
Hope this helps.
Dan Guzman
SQL Server MVP
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122817569.583719.19100@.g43g2000cwa.googlegroups.com...
> Hi,
> I'm attempting to insert a new row into an SQL table using ADO written
> with c# and stored procedures.
> The ADO code is running OK, and i know it should work as i have used
> equivilent code succeffully for other tables. However i am getting the
> following error:
> {"INSERT statement conflicted with COLUMN CHECK constraint 'CK
> tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
> table 'tblPatient', column 'pntStage'.\r\nThe statement has been
> terminated." }
> pntStage has data type NVarChar, and maximum length 8. The values i am
> attempting to input do not violate these criteria. I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
> Any ideas what the problem might be? Here's my stored procedure if
> taht's any help.
> CREATE PROCEDURE proc_InsertPatient
> (@.patientNo int output,
> @.pntUnitID nvarchar(15),
> @.pntTitle nvarchar(4),
> @.pntFName nvarchar(20),
> @.pntLName nvarchar(30),
> @.pntDOB nvarchar(8),
> @.pntSex nvarchar(1),
> @.pntAddress1 nvarchar(150),
> @.pntAddress2 nvarchar(150),
> @.pntAddress3 nvarchar(150),
> @.pntCountryNo int output,
> @.pntPostcode nvarchar (10),
> @.pntHPhone nvarchar (14),
> @.pntWPhone nvarchar (14),
> @.pntMobPhone nvarchar (14),
> @.pntEmail nvarchar (50),
> @.pntStage nvarchar (8),
> @.pntT tinyint,
> @.pntN tinyint,
> @.pntM tinyint,
> @.pntPreviousTreatments char (1000),
> @.pntFurtherNotes char (1000)
> )
> AS
> INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
> pntDOB, pntSex, pntAddress1,
> pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
> pntWPhone,
> pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
> pntPreviousTreatments, pntFurtherNotes)
> VALUES
> (@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
> @.pntAddress1,
> @.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
> @.pntWPhone,
> @.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
> @.pntPreviousTreatments, @.pntFurtherNotes)
> SELECT @.patientNo=@.@.IDENTITY
> GO
>
> Thanks.
>|||Mike,
Thanks for the quick reply.
I've looked in EM at the tblPatient table. Within it's properties i can
only see row Name (pntStage), Data Type (nvarchar), Size (8), Nulls
(not allowed), Default (blank).
Is this what you mean? Is there another way to check more detailed
constraints?
Thanks again.|||Hi
In EM, when you are in the Table Edit screen, top left next to the save
button is the Properties button. On the check constraints tab, you can see
what columns have constraints and what they are.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122818859.232265.305760@.g14g2000cwa.googlegroups.com...
> Mike,
> Thanks for the quick reply.
> I've looked in EM at the tblPatient table. Within it's properties i can
> only see row Name (pntStage), Data Type (nvarchar), Size (8), Nulls
> (not allowed), Default (blank).
> Is this what you mean? Is there another way to check more detailed
> constraints?
> Thanks again.
>|||Dan,
Excuse my ignorance, but where do i find the table DDL?
As regards the input value, i have tried a few "Unknown", "I", "II",
"Ia", to name a few.
By the deleting row comment, i was just meaning that it didn't work, so
i completely removed that particular row from the table, then recreated
it. I thought perhaps i might have put some sort of input mask
constraint or something on it that i had forgotten about. By doing this
it would remove that possibility.
Thanks.|||Mike,
I've just done that, there were some constraints on it. not sure how
they got there, but i've altered them, problem solved! :)
Thank you very much!|||One method to generate the table DDL is to navigate to the table using the
Query Analyzer Object Browser and then right-click on the table and select
script to clipboard as create. You can then paste into your post.
Hope this helps.
Dan Guzman
SQL Server MVP
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122819272.357946.52430@.f14g2000cwb.googlegroups.com...
> Dan,
> Excuse my ignorance, but where do i find the table DDL?
> As regards the input value, i have tried a few "Unknown", "I", "II",
> "Ia", to name a few.
> By the deleting row comment, i was just meaning that it didn't work, so
> i completely removed that particular row from the table, then recreated
> it. I thought perhaps i might have put some sort of input mask
> constraint or something on it that i had forgotten about. By doing this
> it would remove that possibility.
> Thanks.
>|||You might want to find out WHY someone put constraints on the data.
Having a default of a blank on what should be a code is a sign that
someone did not do much design work. Of course we know that when we
saw the "tbl;" and "pnt-" prefixes that violation basic rules for
naming data elements. The "tbl-" prefix is silly in a language with
one data structure; the "pnt-" tells us the location f one occurence of
a data element, not what it is. When you wrote "pntSex" did you mean
"sex_code", "sex_frequency", "sex_preference", "sex_total"? Again,
name it for what it is, never for where it is.
And the use of NVARCHAR(n) in codes is usually a sign the nobody
designed the encodings; we prefer CHAR(n) so can add constraints and do
validation. Do you really use a lot of Chinese characters? If you
allow it, it will come.