On Sat, 2002-03-02 at 22:49, Russell Hires wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Well, yeah, since I've got a few active databases already, and they are
> poorly designed: beginner's bad luck.
>
> The thing I have questions about lately are keys: primary and foreign. If
> you're going to make a primary key, does it always have to be a number? Can
> it be a combination of letters and numbers? And when you're using a primary
> key as a foreign key....well, that's not really a question (stream of
The PRIMARY KEY can be anything that the database supports as a Primary
Key constraint. Some databases may only support INT as primary key
constraints, some may support INT, CHAR, VARCHAR, etc. Most do not
support BLOB types as primary keys or indexes of any sort. And of
course, any given entity may have only one PRIMARY KEY. (Hence the
"PRIMARY" keyword. :)
FOREIGN KEY constraints just say, "You cannot enter data into this field
unless it exists as a key in this other entity at the column
specified." It makes sure that, f.ex., if TableA has PRIMARY KEYs "1,
2, and 3", and TableB has a column with a FOREIGN KEY constraint against
TableA's PRIMARY KEY column, you cannot enter anything but a "1, 2 or 3"
into that column on TableB. Likewise, if you have TableB with a "3" in
that FOREIGN KEY, you will not be able to delete PRIMARY KEY "3" from
TableA because TableB has FOREIGN KEYs referencing that particular
database object.
That sort of thing is called "Referential Integrity." It makes sure at
the database level, as in the above example, that entities that have
dependencies on each other cannot be added or removed unless those
dependencies are first added or removed. Referential Integrity can
often be, and often is, emulated at the application level, but it's
still nice in the database in case your application is buggy or simply
doesn't do enough checks.
> conciousness)....How do you determine where to split up information? Is there
> a such thing as a primary table? I guess I'm thinking of this in a
> heirarchical manner: there is a main table, with your most basic information,
> then there's the other stuff. But is that right? Then I've got a question
You will often have one or more entities that will depend on many other
entities, and possibly each other. That's the secret of database
normalization - determining how finely to divide up the data. (Like,
"If a 'Person' entity can have more than one phone number, should I just
make columns in the 'Person' table for 'Work Phone', 'Home Phone', etc,
or should I have a separate table called 'PhoneNumber' in which a
'Person' entity may have multiple rows?")
You will probably only ever have a "Main" entity in very small
databases, like an address book, where you may have 'Person', 'Phone',
'Address', 'ImportantDate' entities. Larger databases usually have many
"key" entities, which each may have multiple foreign keys on other
tables.
> about adding columns later on, after you've got so many records: how do you
> add them? I realize you probably don't want to put them in a new column in a
> primary table, but the whole key thing bugs me about creating the new table.
> Is this where a foreign key would be useful? (in addition to the other uses
> of foreign keys...)
To add columns to a table after the tables is already build, you
typically just issue an "ALTER TABLE" command and add it. The thing to
keep in mind: if you're using "ALTER TABLE" too much after the table has
been built, you did not build it correctly to begin with, either because
of poor design, or because the client did not give you complete
specifications. Occasionally though, you just need to add another
column because something new has popped up, but you have production data
in your table so you can't just recreate it. It's not a sin, but it's
generally something to avoid.
See above on foreign keys - they're not really related to adding columns
to a table, although an added column can be a foreign key if that's
what's required.
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
#!/usr/bin/perl -w
$_='while(read+STDIN,$_,2048){$a=29;$b=73;$c=142;$t=255;@t=map
{$_%16or$t^=$c^=($m=(11,10,116,100,11,122,20,100)[$_/16%8])&110;
$t^=(72,@z=(64,72,$a^=12*($_%16-2?0:$m&17)),$b^=$_%64?12:0,@z)
[$_%8]}(16..271);if((@a=unx"C*",$_)[20]&48){$h=5;$_=unxb24,join
"",@b=map{xB8,unxb8,chr($_^$a[--$h+84])}@ARGV;s/...$/1$&/;$d=
unxV,xb25,$_;$e=256|(ord$b[4])<<9|ord$b[3];$d=$d>>8^($f=$t&($d
>>12^$d>>4^$d^$d/8))<<17,$e=$e>>8^($t&($g=($q=$e>>14&7^$e)^$q*
8^$q<<6))<<9,$_=$t[$_]^(($h>>=8)+=$f+(~$g&$t))for@a[128..$#a]}
print+x"C*",@a}';s/x/pack+/g;eval
usage: qrpff 153 2 8 105 225 < /mnt/dvd/VOB_FILENAME \
| extract_mpeg2 | mpeg2dec -
http://www.cs.cmu.edu/~dst/DeCSS/Gallery/
http://www.eff.org/ http://www.anti-dmca.org/
This archive was generated by hypermail 2.1.3 : Fri Aug 01 2014 - 17:39:15 EDT