What the hell are keys in SQL?

[wasssup]

I swear I have the worst textbook ever written...it's a joke, they don't explain anything correctly.

I'm working with an access db, and one of the problems i'm working on is "identify all primary keys for the tables. Use "create index" statements to create indices for each primary key. Identify all foreign keys in all the tables and the table which the foreign key refers to. Use "create index" statements to create indices on all the foreign keys. Identify primary and foreign keys and what table each foreign key refers to."

What would make it just a bit easier is knowing wtf a primary and foreign key is..

 

[labgeek]

A primary key is a unique identifier for a record in a given table. The customer id in a table of customers. A product id in a list of products. Etc.

A foreign key is a field which identifies a record in another table. For example, a table of invoices may have a customer id field that points to the primary key of the customers table. That way you store the customer information only 1 time (called normalization), when you change the customer information in the customers table, you don't have to update each invoice. Another example would be a supplier id in a products table. You don't want to have to enter the suppliers information each time. So inside products you identify the supplier by just the id (a foreign key) that points the the suppliers table record id (a primary key).

Edit -
BTW, A primary key IS unique. Often foreign keys are not. Multiple items have the same supplier. But there is only 1 supplier with a given supplier id. Think about that when you create indexes.

 

[MrChad]

Primary keys are used to enforce uniqueness and Foreign keys are used to enforce dependencies.

Let's say I have a table in my database called EMPLOYEES. I want to be sure that the same employee does not appear twice in the table. If I have a column called "SSN," I could make that column a primary key. Now, if I try to insert a record with an SSN that already exists in the table, the database will reject the new record.

Now let's say I have a table called WORKSTATIONS. Each workstation should be linked to an employee. I have a column called "SSN" that is a foreign key. Foreign keys reference primary keys in other tables. In this case, the WORKSTATION.SSN column references the EMPLOYEES.SSN column. If I try to insert a record in WORKSTATIONS with an SSN that does not exist in the EMPLOYEES table, the database will reject the new record.

EDIT: Labgeek beat me to it 😛

 

[wasssup]

thanks for the replies...i think i sort of get it. I'm gonna list a short example though, so i'm sure I know what's going on...

For example, we have a db with a table 'continent' consisting of:

id-------------------name
europe-----------------------Europe
asia----------------------------Asia

and another table 'encompassed_in' consisting of:

countryid-----------------------continentid
cid-cia-Russia-------------------------------europe
cid-cia-Spain--------------------------------europe
cid-cia-Japan---------------------------------asia
cid-cia-Taiwan-------------------------------asia

and the countryid from up above corresponds to another table, etc...

So would there be primary keys and foreign keys for each and every table? Or is there only one set of "true" primary keys (ie. like the continent.name from up above). From my understanding, there is only one "true" primary key, but the question I have to answer (as listed in the first post) seems to want the primary and foreign keys for each table...

 

[MrChad]

All tables should have a primary key. Some tables may have foreign keys.

In your example, id is the primary key for continent and the combination of country_id and continent_id is the primary key for encompassed_in. continent_id is a foreign key referencing continent.id.

 

[wasssup]

oh, and another question...how would one count the number of results from a result of querying the db?

for instance, one question asked to find the number of organizations with no european members...

finding the results of all the organizations and finding the results of organizations with members was easy enough (i figure i'll just take the total # and subtract the number of organizations with european members...i'm guessing there's an easier way, but I don't think that matters..).

And once I get the numbers, i'd have to create an index to hold the value obtained?

 

[labgeek]

The method for accessing the size of the recordset will vary with the language being used to access the database. More likely they want a SQL command that will return the number of records, something like -
SELECT COUNT(recid) FROM tablename WHERE field = value
That will return the number of records meeting that criteria. It will not however return the actual data itself. It is a single record recordset whose sole item returned is the count of the records. Does that make sense?

For the not european count, remember your WHERE clauses can use a negative equality as well.
SELECT COUNT(recid) FROM tablename WHERE continentid <> "europe"

I'm not sure what your asking about building the index with the numbers. What an index is going to do for you is make queries faster (and aid in keeping the data integrity - which I'm guessing you'll probably learn about next). The database won't have to look through every record to determine if it meets criteria that is based on the index. Indexes are built prior to the queries. Think of it this way... Think of a book as a table - a collection of records (pages). The index of a book contains very basic information about what is contained on various pages. You can look through this much smaller list that's sorted for easy reference, to find the pages (records) that you need.

Making sense?

 

[wasssup]

thanks for the help guys...i pretty much got this stuff worked out 🙂

(of course, after so much work doing all those problems my prof decides to extend the due date till tuesday night...oh well, more free time for me i guess 🙂)

 

[wasssup]

Another question -- can a table have two primary keys?

 

[labgeek]

No there can only be 1 primary key. Other candidate keys that are not the primary key can be alternate keys.

Edit for clarification - Like the primary key the alternate keys can uniquely identify a record. But some databases will physically sort records based on the primary key with the intention that most accesses will be based on the primary key. Especially in those situations, the primary key should be the most logical based on what is used most. If a telephone company looks customers up by telephone number (account #), then the customers social security number would not make a logical choice for a primary key, but it may be a worthwhile thing as an alternate key. (Actually since you could have more than 1 account, it may be a non-unique index).

 

[Drakkon]

No two primary keys...it can have to unique fields however...the only real identifying factor about a primary key is that is its unique...you can have as many unquie fields as you want though in a table....that way get a buncha crossreferencing goings on through tables too 😉

 

[wasssup]

Now i'm pretty sure I understand it...if there are no columns that are unique, a combination of columns create uniqueness and the combination forms a primary key.

And a foreign key is just the primary key of another table, right?

 

[labgeek]

Sound like you've got it.

 

[wasssup]

awesome..thanks again!