|
|
Posted by Erland Sommarskog on 04/01/07 10:22
raylopez99 (raylopez99@yahoo.com) writes:
> // error message:
>
> 'Authors' table saved successfully
> 'Content' table
> - Unable to create relationship 'FK_Content_Authors'.
> The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
> "FK_Content_Authors". The conflict occurred in database "DCV_DB",
> table "dbo.Authors", column 'AuthorID'.
>
> // due to the below no doubt!
>
> --
> CREATE PROCEDURE dbo.InsertAuthor /* THIS IS CORRECT (what I want)
> 'CREATE PROCEDURE' not 'ALTER PROCEDURE'*/
No! That has nothing to do with it! In the above you are try to add a
foreign key from the Content table to the Authors table, but there is
data that prevents this from happening. Maybe because you had already
entered data in Contents before you tried to apply the foreign key. This
is entirely unrelated to your procedure.
Here is a script that demontstrates:
CREATE TABLE Authors (
authorid int NOT NULL,
name nvarchar(50) NOT NULL,
CONSTRAINT pk_authors PRIMARY KEY (authorid))
go
-- Very simple book table. I ignore ISBN and the fact that
-- a book can have several authors.
CREATE TABLE Books (
bookid int NOT NULL,
title nvarchar(1000) NOT NULL,
authorid int NOT NULL,
CONSTRAINT pk_books PRIMARY KEY (bookid))
go
-- Being eager, we insert a book, before we add any authors.
INSERT Books (bookid, title, authorid)
VALUES(1, 'The Mouse Trap', 1)
go
-- Oops, someone told us that foreign keys is a good idea.
ALTER TABLE Books ADD
CONSTRAINT fk_books_authors FOREIGN KEY (authorid)
REFERENCES Authors(authorid)
go
-- It failed. We must add the author first:
INSERT Authors (authorid, name)
VALUES (1, 'Agatha Christie')
go
-- It should work now.
ALTER TABLE Books ADD
CONSTRAINT fk_books_authors FOREIGN KEY(authorid)
REFERENCES Authors(authorid)
go
-- Clean up.
DROP TABLE Books, Authors
Note by the way that this is all SQL commands. While point-and-click
GUIs for table design can make you work faster if you are unexperienced,
learning the SQL commands is absolutely essential if you plan for a
professional career in SQL programming. Once you know the commands well,
you can use GUI tools if you find that more convenient. But all the
tools do is to generate the commands like the above, and without knowledge
and understanding of that, you are pretty clueless when things go wrong.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|