|
|
Posted by Erland Sommarskog on 07/15/07 09:32
raylopez99 (raylopez99@yahoo.com) writes:
> Very bizarre. If anybody has any advice please offer it. I think SQL
> is a dinosaur language and should be scraped, and RDBMS are obsolete
> in view of cheap memory, faster processors, and managed pointers, but
> that's another matter.
In that case, should I even bother to answer?
Memory may be cheap, but 1TB of memory is still quite expensive. And
when power is turned off, not much remains of what once was there.
And if you think SQL should be scrapped, permit me to point out that
most SQL queries written in C++ or C# would require a lot more code.
And, what worse is, the code would not be able to adapt to changes in
data distribution or addition of new indexes. The point with SQL is
that you say what result you want - the optimizer finds out the best
way to compute that result.
As for your problem, you appear to be working with some graphical tools
that I have little experience of. (And I don't have much faith in.) But
there is an apparent misconception:
> 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.
>...
> 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?
No, that is the essence of a relationship. Or rather that is the
relationship.
In a relational database, all tables should have a primary key which
uniquely identifies the data. Ideally, the PK should be drawn from
the data itself. For instance, in a table of countries, the PK should
be the country code according to ISO 8601. Often, though, it is more
convenient to introduce a surrogate key, that is a system-assigned
number to each row. Care should still be take to avoid duplicates,
that is, Mark Twain should only appear once in a table. An important
thing to point out about primary keys is that a PK can consist of more
than one column.
Now, we have table called Authors. Somewhere else we also have a table
called Books, in which the PK would typically be the ISBN. Of course,
when we have a book, we want to know the author. So in the Books
table is there an Authors column? Nah, a book can have several authors
so that is not really a good idea. Instead there is a table AuthorBooks
which have two columns AuthorID, ISBN. This table states which authors
what wrote which books. The primary key of this table is (AuthorID, ISBN).
But the columns AuthorID and ISBN are also foreign keys in this table.
Not together, but on their own. AuthorID refers to Authors and ISBN
refers to Books. The foreign key is constraint: you cannot add an
AuthorID in AuthorBooks if this AuthorID does not exist in Authors.
The best way to learn SQL and relational databases is not through
graphical tools that do things behind the scenes, and far from always
have gotten things right. Instead learn the proper commands and get
a book which is focused on learning relational databases.
--
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
Navigation:
[Reply to this message]
|