I'm new to working with databases but I have some qustions about primary and foreign keys. I was reading a book that said "You can search for information in a relational database by using information in one table's promary key to find information in another table. A foreign key is a column in one table that corresponds to a primary key in another table. In order for you to search for information in a database, the primary key of one table must match a foreign key in another table" Ok but can someone explain to me whats going on behind the scenes why is this happening.
Database Theory
- Thread starter watts3000
- Start date
Primary keys must be 2 things - Not null (meaning noy empty) and unique in the table. For example, let's say you are designing a database for a hardware store inventory.
First, you'd want a table with perhaps the part number/stock number, item description, wholesale price, and retail price. The part number would be your primary key, since no two items would have the same part number.
So now let's say you want to track how many items are sold each month. You'd want a table to track those sales of items. In this table you'd want the same part number field, and perhaps the date and time of the transaction. So as the cashier scans or keys in the item number in the register, you can have it keep track of these items.
So for the month of January you sold 100 hammers and 50 screwdrivers. Your transaction sales table would contain 150 rows or entries for those items. Although this table is only storing the part number and date of what was sold, it is easy to generate a report with the full item descriptions since the part number is linked back to your main part table. This case is called a one-to-many relationship.
So what's the advantage of foreign keys then? Well they are handy as you can set up constraints on them to insure data integrity. Perhaps the hammer isn't scanning properly, so the cashier keys in the part number. But if she fat fingers it and mistypes the number, the database can check the foreign key against the parts table and not allow that row to be inserted into the table since that part number doesn't exist.
Also, depending on the database software, you can perform cascade updates and deletes on relationships. Say you want to update the part number for the hammer. But you've already recorded 100 hammers sold in the transaction table. With a cascade update, if you change it in the parts table it will automatically change all the corresponding entries in the transaction table to match. Very handy. However, not all database software does this (including some big iron ones) and you need to rely on triggers. But that is way out there.
Also, all tables should have a primary key. In the example above, it may seem the transaction table would pose a problem for choosing a primary key. However, there is a handy feature called an Identity or autonumber field type. You can set them to start out at any number and increment automatically. That way, you can let the database software do the work of assigning a unique id to each row in the table.
All this was probably as clear as mud
.
First, you'd want a table with perhaps the part number/stock number, item description, wholesale price, and retail price. The part number would be your primary key, since no two items would have the same part number.
So now let's say you want to track how many items are sold each month. You'd want a table to track those sales of items. In this table you'd want the same part number field, and perhaps the date and time of the transaction. So as the cashier scans or keys in the item number in the register, you can have it keep track of these items.
So for the month of January you sold 100 hammers and 50 screwdrivers. Your transaction sales table would contain 150 rows or entries for those items. Although this table is only storing the part number and date of what was sold, it is easy to generate a report with the full item descriptions since the part number is linked back to your main part table. This case is called a one-to-many relationship.
So what's the advantage of foreign keys then? Well they are handy as you can set up constraints on them to insure data integrity. Perhaps the hammer isn't scanning properly, so the cashier keys in the part number. But if she fat fingers it and mistypes the number, the database can check the foreign key against the parts table and not allow that row to be inserted into the table since that part number doesn't exist.
Also, depending on the database software, you can perform cascade updates and deletes on relationships. Say you want to update the part number for the hammer. But you've already recorded 100 hammers sold in the transaction table. With a cascade update, if you change it in the parts table it will automatically change all the corresponding entries in the transaction table to match. Very handy. However, not all database software does this (including some big iron ones) and you need to rely on triggers. But that is way out there.
Also, all tables should have a primary key. In the example above, it may seem the transaction table would pose a problem for choosing a primary key. However, there is a handy feature called an Identity or autonumber field type. You can set them to start out at any number and increment automatically. That way, you can let the database software do the work of assigning a unique id to each row in the table.
All this was probably as clear as mud
.Does sql server 2000 have an auto number and how do you set the incrementer. Also a little off subject I was wondering have you ever work with a oracle database.
The wording from your book is misleading (backwards). It should state that a foreign key value in one table (the child table) must match an existing key value in the other table (parent table).
Transact-SQL, the dialect used in Sybase and M$ SQL Server, both support an auto-increment data type.
In T-SQL, it's called an IDENTITY.
It's somewhat cleaner than in Oracle/PostgreSQL where you declare a sequence. I've used Oracle sparingly because it's the market leader, but it can be a pain to work with.
Transact-SQL, the dialect used in Sybase and M$ SQL Server, both support an auto-increment data type.
In T-SQL, it's called an IDENTITY.
It's somewhat cleaner than in Oracle/PostgreSQL where you declare a sequence. I've used Oracle sparingly because it's the market leader, but it can be a pain to work with.
Can one of you guys tell me what is a good book that gives overall understanding of databse theory and explains sql server 2000 I'm looking for a beginner book
Also can I work on a sql server database it home and take it to class with me. Basically I would like to know how do I transport a database I don't see a save as option. I have a few computers and one runs as a database server I'm assuming the files I want are saving to that computer. But I don't ecactly know what files to save to zip disk.
Also can I work on a sql server database it home and take it to class with me. Basically I would like to know how do I transport a database I don't see a save as option. I have a few computers and one runs as a database server I'm assuming the files I want are saving to that computer. But I don't ecactly know what files to save to zip disk.
<< Can one of you guys tell me what is a good book that gives overall understanding of databse theory and explains sql server 2000 I'm looking for a beginner book >>
Professional SQL Server 2000 Programming. This is a very extensive book. As with most Wrox books, even the "professional" ones still cover all the basics first.
The Guru's Guide to Transact-SQL. An excellent book to both learn, and reference, T-SQL (as manly said, the dialect that SQL Server uses).
<< Also can I work on a sql server database it home and take it to class with me. Basically I would like to know how do I transport a database I don't see a save as option. I have a few computers and one runs as a database server I'm assuming the files I want are saving to that computer. But I don't ecactly know what files to save to zip disk. >>
What do you mean take it to class with you? Do you take a laptop to class with you? If so, are you installing the sql server on this laptop? If not, you can't really "bring the database." You could, however, simply export the data (by way of DTS' or bcp -- look in Books Online for more info) to another format, like an Excel spreadsheet, an Access database, delimited textfile, etc..
well have sql servers at school. I mean is there a way to export your database that you've been working on at home and import it to the sql server at school.
TRENDING THREADS
-
Discussion Zen 5 Speculation (EPYC Turin and Strix Point/Granite Ridge - Ryzen 9000)- Started by DisEnchantment
- Replies: 25K
-
Discussion Intel Meteor, Arrow, Lunar & Panther Lakes + WCL Discussion Threads
- Started by Tigerick
- Replies: 24K
-
Discussion Intel current and future Lakes & Rapids thread- Started by TheF34RChannel
- Replies: 23K
-
-
Facebook
Twitter