Insert Record - 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 2005 Forums

Transact-SQL (2005)

Insert Record

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-01-22 : 17:16:20

Hello,

I have two tables, FAQ and FAQLocalized, as follows:

[FAQ]
> FAQId, FAQType

[FAQLocalized]
> FAQLocalizedId, FAQId, FAQQuestion, FAQAnswer, FAQCulture

Both FAQId and FAQLocalizedId are GUID's.
FAQId is a Primary Key in FAQ and a Foreign Key in FAQLocalized.

I am inserting a record as follows:
EXECUTE dbo.MyPro NULL, 'Question_1', 'Answer_1', 'en-GB', 'Contact'

I get the error:
Cannot insert the value NULL into column 'FAQLocalizedId', table 'dbo.FAQLocalized'; column does not allow nulls. INSERT fails.

My code is as follows:

-- Begin of procedure code
ALTER PROCEDURE [dbo].[CreateFAQ]

-- Define the procedure parameters
@FAQId UNIQUEIDENTIFIER = NULL,
@FAQQuestion NVARCHAR(MAX),
@FAQAnswer NVARCHAR(MAX),
@FAQCulture NVARCHAR(5),
@FAQType NVARCHAR(100)

AS

SET NOCOUNT OFF;

-- Find record with given FAQId
SELECT @FAQId = FAQId FROM dbo.FAQ WHERE FAQId = @FAQId

-- Check if FAQId is Null
IF @FAQId IS NULL
BEGIN

-- Create a new FAQId by defining a new GUID
DECLARE @NewFAQId UNIQUEIDENTIFIER;
SET @NewFAQId = NEWID();

-- Insert new FAQ in FAQ
INSERT dbo.FAQ (FAQId, FAQType)
VALUES (@NewFAQId, @FAQType)

-- Insert new localized content in FAQLocalized
INSERT dbo.FAQLocalized (FAQId, FAQQuestion, FAQAnswer, FAQCulture)
VALUES (@NewFAQId, @FAQQuestion, @FAQAnswer, @FAQCulture)

END

Any idea what I am doing wrong?

Thanks,
Miguel

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-22 : 17:57:18

quote:
Originally posted by shapper
Any idea what I am doing wrong?

yes. you are trying to insert null into a column that doesn't allow it. The problem is this code:


  -- Insert new localized content in FAQLocalized 
  INSERT dbo.FAQLocalized (FAQId, FAQQuestion, FAQAnswer, FAQCulture) 
  VALUES (@NewFAQId, @FAQQuestion, @FAQAnswer, @FAQCulture)

try this instead:


  -- Insert new localized content in FAQLocalized 
  INSERT dbo.FAQLocalized (FAQLocalizedId, FAQId, FAQQuestion, FAQAnswer, FAQCulture) 
  VALUES (newid(), @NewFAQId, @FAQQuestion, @FAQAnswer, @FAQCulture)

alternatively, you could add a default value of newid() to the FAQLocalizedId column

www.elsasoft.org

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-01-23 : 07:53:50

Hi

I just compared with an older project of mine and I see a difference:

In my FAQ table the FAQID shows as (PK, FK, uniqueidentifier, not null)
There are also two constraints:
[FAQ] > DF_FAQ_FAQId
[FAQLocalized] > DF_FAQLocalized_FAQLocalizedId

In my new project I don't have those constraints and in my FAQ table the FAQID shows as (PK, uniqueidentifier, not null). The FK is missing.

I believe this is the problem.

What am I missing?

Thanks,
Miguel

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-23 : 17:01:52

did you try what I suggested?

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources