|
|
Posted by raylopez99 on 07/15/07 11:32
On Jul 15, 2:32 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> raylopez99(raylope...@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?
To prove me wrong Erland! LOL.
> 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.
No, just dump your data into a flat file, use a hash table to index
every word in said file, and use hardware (CAM memory) to do a fast
search. Simple really. Probably what Google does right now. You
think they are using SQL "queries" to do fast searches? No way Jose.
>
> 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.)
Correct. You are "right as rain" on this point. I do think the C#
feature of VS2005 vis-a-vis the SQL dB aspect of it is broken--maybe
because I'm using the SQL Server 2005 Express (free) edition (just
upgraded for a mere $50 to the Developer's edition--I'll repost here
if it solves this small problem).
> 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.
But it should (I would think) be irrelevant to the particular bug
here, since the columns are identical. I even tried making both
columns "primary keys" but this did not solve the bug, which proves my
point. Sorry if I was not clear but talking about computer problems
in any language is ackward. [UPDATE: see my "revelation" below]
>
> 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.
Yes, this is theory. I agree.
>
> 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.
Yes, but it seems you are advocating a THIRD table "instead there is a
table AuthorBooks". Why, just to link the two tables Authors and
Books? Why not simply add a foreign key in Books called "AuthorID",
that is the primary key in Authors? (This is exactly what my Frasier
book proposes). But I suppose you can have a third "intermediate"
table, to tie two tables together--no reason why you cannot--but it
just doesn't sound "simple". No big deal though.
>
> 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.
OK. Tell me the book to buy then! Here is what I've already bought:
Andrew Brust et al "Programming SQL Server 2005" (too specific on T-
SQL enhancements, on ADO.NET, on XML data, etc); Frasier book
(excellent) Pro Visual C++ (has a chapter on SQL programing via VS--
but a mere dozen pages--however his examples whetted my appetite for
SQL); Alan Beaulieu "Learning SQL" (O'Reilly Press--too focused on
MySQL, rather than T-SQL which Microsoft uses, so I can't 'compile'
his examples, but I do read his book and try and replicate his
examples in VS2005); Morrison "Creating SQL Server 2005 Applications
with Visual Studio" (book is just like the title says--the authors use
Visual Basic as the glue language, which I don't like, but you can
follow along. There's an emphasis on using the VS IDE to create
queries, etc). I also just ordered these books: Beginning C# 2005
Databases (Programmer to Programmer)[probably the same as the Morrison
et al book on VB, but using C#]; Sams Teach Yourself SQL in 10
Minutes, Third Edition [might be the most useful book?]
[My REVELATION] BTW, as I typed this, I just realised that creating a
"relationship", as you said above, is simply creating primary and
foreign keys and having a foreign key in one table that is a primary
key in another. Since I've done this already using the "Properties"
tab of tables "Author" and "Content", then I don't really need the GUI
"Wizard" that seems to be broke in C#/VS2005, do I? So the "bug" I
describe in this thread is a minor bug, a trivial bug, for those
people too lazy to designate a "primary" and "foreign" key, manually.
Thanks Erland--your name sounds familiar... ah yes, you helped me with
a connection problem I had earlier this year (when I was using C++ as
the language of choice): http://tinyurl.com/384rjt
Cheers,
Ray
Navigation:
[Reply to this message]
|