|
|
Posted by David Portas on 07/15/07 09:40
On 15 Jul, 01:05, raylopez99 <raylope...@yahoo.com> wrote:
> Problem:
>
> I replicated, using Frasier "Pro Visual C++/CLI" (2006), Chap 12, a
> database that has a relationship between two tables, called Content
> and Author, using a common column, called "AuthorID". I used the
> Server Explorer insider Visual Studio 2005 to do this. It worked.
>
> Now I tried the exact same thing from Visual Studio 2005 but this time
> using C# not C++ as my language of choice. The interface is slightly
> different, but I made sure everything was done as before. I used the
> Server Explorer 'GUI' to do this, clicking and following the 'wizards'
> as before.
>
> But somehow, when using C# rather than C++, I get this error when
> attempting to create a relationship between tables when using two
> identical columns called "AuthorID": "the columns in table Authors do
> not match an existing primary key or UNIQUE constraint" Why? The
> columns are the same. I even tried (and this made no difference)
> copying and pasting columns from one table to the other, but still I
> get this error.
>
> I tried everything, even deleting columns and rebuilding them, which
> eventually created a new error that fatally compromised the database
> (if you're curious, and as an aside, it created this error: "'Authors'
> table - Unable to modify table. Cannot insert the value NULL into
> column 'AuthorID', able 'DCV3_CSharp.dbo.Tmp_Authors'; column does
> not allow nulls. INSERT fails.
> The statement has been terminated.")
>
> One thing (that shouldn't matter, since it didn't matter when I used C+
> + rather than C#): the column in table "Author" is a primary key,
> while it is a "foreign key" in table "Content". But that is
> irrelevant when creating a relationship, no? Also the checkbox "Allow
> Nulls" is checked "NO" for all columns. This did not matter when
> successfully compiling /building the relationship when using C++, but
> I wonder if perhaps C# is less forgiving, and maybe I should check
> "YES" for allowing Nulls?
>
Keys are certainly not irrelevant to your question. They are
essential. Unfortunately it's hard to be sure what happened based on a
description of what you did in the GUI. I would advise you to use
either a decdicated data modelling tool or SQL itself to make these
sort of changes. I would not recommend VS as a design tool.
The change you want to make should correspond to something like this:
CREATE TABLE Author (AuthorID INT NOT NULL PRIMARY KEY);
CREATE TABLE Content (AuthorID INT NOT NULL /* PRIMARY KEY NOT
SPECIFIED! */);
ALTER TABLE Content
ADD CONSTRAINT fk01 FOREIGN KEY (AuthorID)
REFERENCES Author (AuthorID);
In other words, AuthorID MUST be a PRIMARY/UNIQUE key in the target
table being referenced.
> I think SQL
> is a dinosaur language and should be scraped
I agree that SQL is well overdue for replacement. Unfortunately it
can't happen overnight.
> RDBMS are obsolete
> in view of cheap memory, faster processors, and managed pointers, but
> that's another matter.
That's not a coherent line of reasoning at all. An RDBMS implements a
logical model of data that is independent of the hardware used. If
processors and memory are faster and cheaper then that makes RDBMS
faster and cheaper too. What do you propose as an alternative?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|