# Relationships in MySQL \[help and/or listen to me rant\] **URL:** \ **Category:** Factual Questions\ **Created:** [January 18, 2009, 2:07am UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813 "2009-01-18T02:07:44Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\ **Post date:** [January 18, 2009, 2:07am UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/1 "2009-01-18T02:07:44Z")
So I’m futzing around seeing to what extent I can get a handle on MySQL from a manual (Vaswani’s _How to do Everything with PHP & MySQL_) as recommended by **Lobsang** [in this thread](http://boards.straightdope.com/sdmb/showthread.php?t=501180), and drawing upon my own FileMaker experience. I selected a vintage-1996 FileMaker db, my first serious FmPro db in fact, figuring it was “real world” (at one time) and yet pretty simple compared to stuff I’ve done since then… I am now officially confused, folks. And I do not like it when I don’t understand things. I do not like feeling stupid. This manual sort of skips over how the heck you _SET UP A FREAKING RELATIONSHIP_, which is odd because it provides specific walk-thrus and example command-line syntax for the creation of databases, creation of table, and defining of fields. But of relationships it sort of glosses past, explaining only “this is how you define a field as your primary key” and then mentioning that this will be used in conjunction with foreign keys, e.g., [QUOTE=Vikram Vaswani] With a relational database system like MySQL it’s also possible to link information in one table to information in another \* . When you begin to do this, the true power of an RDBMS becomes evident. [/quote] (p 152) - Gee, Ya THINK!??:smack: [QUOTE=Vikram Vaswani] You can specify a foreign key for a table with the FOREIGN KEY modifier. The FOREIGN KEY modifier links a field in one table to a field (usually a primary key) in another table, setting up a base for relationships. [/quote] (p. 170) Yeah yeah yadda yadda… so where the hell is the syntax for create relationship, dammit? The example given makes it sound like simply by creating some fields in different tables that happen to have the same field names, some magic occurs presto connecto wowzy woo woo, suddenly there’s a relationship here!? Why isn’t there any walk-thru? OK, if you were Mr. Vikram Vaswani, I would want to be asking you to explain the following: **a)** Let’s say I have TableOne which has a defined primary key field “employeeserial” an integer field not null and auto incrementing; and I have TableTwo which consists of records each of which will be associated with one and only one record from TableOne. I know what I’d do in FileMaker, I’d define a field in TableTwo, let’s say it’s also called “employeeserial” and it’s just a plain ol’ number field (we FileMaker folks do not have to wave any flags and say THAT THERE is a FOREIGN KEY; I gather that in MySQL I would do so, though?) and then define a relationship between the two like so: TableOne::employeeserial = TableTwo::employeeserial OK walk me through how I do that part in MySQL please? Just saying “Yo, you there, you’re a freaking FOREIGN KEY” doesn’t tell it what field in what table it is supposed to hook up with, right? I mean how the heck could it? Especially since… well let’s just continue… **b)** For kicks, let’s assume that there exists a field in TableTwo called “status”, varchar (16) which you’d do in FileMaker simply by creating a text field “Status”; and this field may contain several different text values one of which is “Inactive”; and that I wish to establish a _second relationship_ between TableOne and TableTwo, such that employeeserial in TableOne equals employeeserial in TableTwo AND the field status in TableTwo does NOT equal “Inactive”. I can do that in several easy ways in FileMaker: I can create a calcfield in TableTwo of result type “number” defined as Case (Status≠“Inactive”, employeeserial) and then define my new relationship like so: TableOne::employeeserial = TableTwo::newcalcfield OR, instead I can define a calcfield in TableOne result type “text” defined simply as the constant “Inactive” and then define my relationship this way instead: TableOne::employeeserial = TableTwo::employeeserial AND TableOne::inactiveconstant ≠ TableTwo::status (in modern FileMaker that would result in a second table _occurrence_ of TableTwo on the relationship graph) OK walk me through how I create a second relationship between two already-related tables in MySQL? I guess if I knew how to explicitly create the FIRST relationship I’d know this as well? **c)** Also of interest, the selfjoin relationship, if you don’t mind. If I wish that all records in TableTwo that share the same status field value be related to each other, how do I tell MySQL to relate the table to itself in that fashion? In FileMaker, I’d make a rel between TableTwo::status and itself, which would once again prompt a new table occurrence which I’d give some useful name like TableTwoSameStatus. ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [January 18, 2009, 3:44am UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/2 "2009-01-18T03:44:06Z")
In an SQL database, relationships are basically what we refer to as [syntactic sugar](http://en.wikipedia.org/wiki/Syntactic_sugar), meaning that they’re nice to have, but you can get by without them just fine. What you’re looking for is the [inner join](http://en.wikipedia.org/wiki/Join_(SQL)#Inner_join) operation. If you don’t know SQL, joins can be a little tough to wrap your head around at first, so take some time to read through that article. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [January 18, 2009, 4:07am UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/3 "2009-01-18T04:07:08Z")
> [@](#): > > c) Also of interest, the selfjoin relationship, if you don’t mind. If I wish that all records in TableTwo that share the same status field value be related to each other, how do I tell MySQL to relate the table to itself in that fashion? In FileMaker, I’d make a rel between TableTwo::status and itself, which would once again prompt a new table occurrence which I’d give some useful name like TableTwoSameStatus. select t1.\* ,t2.\* from TableTwo t1 inner join TableTwo t2 on t1.status=t2.status That’s going to give you a bunch of stuff \* 2 (one for each pair of rows such that any row from TableTwo occupies t1 and another where that same row occupies t2), is status really the only field you would want joined on? What is the goal? Knowing that might help fine-tune the above sql. ---
**Author:** ![ZipperJJ](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zipperjj/32/211_2.png) [@ZipperJJ](https://boards.straightdope.com/u/ZipperJJ)\ **Post date:** [January 18, 2009, 4:27am UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/4 "2009-01-18T04:27:32Z")
To re-iterate what the others said - at this stage in the game you do not need to specifically imply anywhere that anything is a “foreign key.” The idea of “foreign key” can just be in your head. A good rule of thumb is to make the PK in a table an auto-increment int-type field called ID. Then, when you reference that field in another table, call the field [othertablename]ID. So you can have a table called Customers with a field called ID, and a table called Invoices with a field called ID and a table called Salespeople with a field called ID. Then in your Invoices table, have CustomerID and SalespeopleID (even tho SalespersonID makes more sense, stick with the table name anyway). Now you have two “foreign keys” in your Invoices table but they only exist as “foreign keys” in a logical sense and there’s no actual constraints. And, one more rule of thumb - the most confusing thing for beginners about inner joins is that when you do an inner join you only get records returned where each of the joined tables have data. So if you want to get all customers don’t inner join Customers with Invoices because if a customer doesn’t have an invoice, you’ll get no record. The key to foreign keys is to not have to duplicate data across tables. All customer info is in the Customers table and all Invoice data (no customer phone, address, name, etc) is in the Invoice table etc. This saves you time and resources by not having to update data in a bunch of places when data changes. ---
**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\ **Post date:** [January 18, 2009, 4:37am UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/5 "2009-01-18T04:37:26Z")
So “relationships” in MySQL are not even nouns, they only exist when you get around to doing stuff “as verbs” like when you run QUERIES or INSERT statements & whatnot? I had already sort of picked up on the idea that there is no such thing as a “calculation field” in the FileMaker sense of the word: a field whose contents is dynamic and is the result of a formula that references (or at least CAN reference) related data. Are there also no auto-enter (default) options in which you enter a formula rahter than a hardwired value (i.e., fieldX has a default value of 13 times sum ( Relatedtable::somefield) + SomeotherRelatedTable::someotherfield, that kind of thing? This thing doesn’t even seem like a database to me, let alone a relational database. There’s no _there there_ aside from rows and columns! It could all just be spreadsheets! The PHP side of this endeavor had better be some kind of fantastic, as it looks like it’s going to be doing nearly all the heavy lifting. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [January 18, 2009, 5:08am UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/6 "2009-01-18T05:08:55Z")
Re: calculated fields: You can create a view that has multiple tables, calculated columns, etc. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [January 18, 2009, 5:14am UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/7 "2009-01-18T05:14:27Z")
> [@](#): > > There’s no there there aside from rows and columns! It could all just be spreadsheets! What’s a “there there”? ---
**Author:** ![ZipperJJ](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zipperjj/32/211_2.png) [@ZipperJJ](https://boards.straightdope.com/u/ZipperJJ)\ **Post date:** [January 18, 2009, 6:46am UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/8 "2009-01-18T06:46:08Z")
Real databases are kind of hard to wrap your head around at the beginning, especially if you’re used to using a GUI-for-data like Excel or FileMaker, or even Access. In those you are using a program that gives you the ability to do stuff like “make forms” or run calculations using tools built right into the application. Databases like MS SQL or MySQL are just places to store data in tabular form. Then, you take whatever programming language you want and build whatever sort of application you need that does nothing but retrieve and manipulate data from your database. vBulletin (this board’s software) is an example of such a program. In fact, PHPMySQL and MS’s SQL Server Management Studio are also such programs. They are pre-built GUIs for databases. You can also, with the right drivers, access and manipulate Excel and Access data without ever touching the GUI, all through code. So, learning to create a well-structured database is one thing. Learning what commands you can run on said database is another. Learning how to create the code that runs the commands is yet another thing. Anyway, once you learn all this and become comfortable with what a database is and how it relates to real-world projects, you see everything in a completely different light. It will come. ---
**Author:** ![Do\_Not\_Taunt](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/do_not_taunt/32/2968_2.png) [@Do\_Not\_Taunt](https://boards.straightdope.com/u/Do_Not_Taunt)\ **Post date:** [January 18, 2009, 9:46am UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/9 "2009-01-18T09:46:59Z")
> [@ultrafilter](#): > > In an SQL database, relationships are basically what we refer to as [syntactic sugar](http://en.wikipedia.org/wiki/Syntactic_sugar), meaning that they’re nice to have, but you can get by without them just fine. What you’re looking for is the [inner join](http://en.wikipedia.org/wiki/Join_(SQL)#Inner_join) operation. If you don’t know SQL, joins can be a little tough to wrap your head around at first, so take some time to read through that article. I don’t think this is quite right - making your foreign keys explicit has a purpose beyond syntactic sugar: specifically, you can enforce referential integrity checks and automate cascading operations. For an example of the former, if you have a customers table with an id, and an invoices table with a customer\_id, having an explicit foreign key prevents you from setting the value of customer\_id of an invoice to, say, 1000, if there is no row in the customers table with id=1000. As an example of the latter, if you delete the row from customers with id=500, you can have the database automatically delete all the invoices with customer\_id=500. This could be helpful, or completely disastrous, depending of the sematics of your situation. ---
**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\ **Post date:** [January 18, 2009, 1:19pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/10 "2009-01-18T13:19:40Z")
> [@AHunter3](#): > > So “relationships” in MySQL are not even nouns, they only exist when you get around to doing stuff “as verbs” like when you run QUERIES or INSERT statements & whatnot? Relationships can be part of “verbs” when doing SQL SUDI ( **S** ELECT, **U** PDATE, **D** ELETE, **I** NSERT). But it can also be defined as “nouns” when using options such as [CONSTRAINT FOREIGN KEY REFERENCES](http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html) clauses like this: ```auto ALTER TABLE (table_name) ADD **CONSTRAINT** (foreign key constraint name) **FOREIGN KEY** (field name) **REFERENCES** primary_table_name (primary_table_primary_index_field); ``` Keep in mind that explicitly defining relationships CONSTRAINT FOREIGN KEY REFERENCES are _not_ necessary. For example, multi-billion dollar [SAP business software](http://www.sap.com) has 20,000+ tables defined and their programmers don’t bother with creating “noun” relationships via CONSTRAINT FOREIGN KEY. All of their millions of line of source code just joins the tables as needed via SELECT. They certainly _describe/document_ the relationship of their 20,000 tables in pictures (entity-relationship diagrams) but they don’t _rely_ on the database engine to enforce parent-child relationships. There is some wisdom in why they deliberately do this which I won’t go into here. However, databases with “data warehouse” applications often explicitly define “noun” relationships so the db engine can create “star indexes” for faster filtered retrieval. > [@](#): > > This thing doesn’t even seem like a database to me, let alone a relational database. There’s no _there there_ aside from rows and columns! It could all just be spreadsheets! I guess thinking about “what makes a database a _real_ database” can get into philosophical disagreement but I suggest thinking about them in a different way. For example, a desktop database like Microsoft Access or FileMaker will have built-in reports & GUI forms to interact with the database. Someone coming from that world will look at MySQL, MS SQL Server, Oracle and be bewildered why they don’t come with those features. Oracle costs $200,000 and it doesn’t have native report capability?! (I know the big database guys now all include add-on “packages” for reports such as Crystal Reports, but that’s not quite the same native integration as MS Access / FileMaker.) The real differentiation of enterprise databases is the **storage engine** : the complex engine to handle hundreds/thousands simultaneous users and the intelligent allocation of data blocks to manage terabytes/petabytes of millions/billions of rows. This is what spreadsheets/FileMaker cannot do. The “relationship” syntax is really minor in the big scheme of things. ---
**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\ **Post date:** [January 18, 2009, 4:46pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/11 "2009-01-18T16:46:53Z")
I know it was mostly a very well-written storage-and-retrieve engine, but I was expecting a little bit more structure than this! Yes, I would benefit greatly if there were s website or a good book about SQL system written explicitly for people coming in from FileMaker. They do that for folks switching from Windows to Mac, which is not as big a leap. ---
**Author:** ![ZipperJJ](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zipperjj/32/211_2.png) [@ZipperJJ](https://boards.straightdope.com/u/ZipperJJ)\ **Post date:** [January 18, 2009, 5:22pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/12 "2009-01-18T17:22:49Z")
> [@AHunter3](#): > > Yes, I would benefit greatly if there were s website or a good book about SQL system written explicitly for people coming in from FileMaker. They do that for folks switching from Windows to Mac, which is not as big a leap. Honestly, since you’re such a pro at FileMaker, you might consider starting a blog that documents your journey through learning how to work with MySQL when coming from the FileMaker world. In fact, that might be a good first project for you for learning - build the blog. Blogs are good for having nice simple databases with some relationships (authors, posts, categories, etc) and lookups (by date, by category, by author, etc). Of course, you’d also have to learn a language that would enable you to present the data on the Web (PHP) but you might find that your entire experience is very limited if you are just working with a database alone. I find that a project with a goal is a great way to force yourself into learning. Reading books is nice but very dissatisfying in the end. ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [January 18, 2009, 5:22pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/13 "2009-01-18T17:22:50Z")
> [@Do\_Not\_Taunt](#): > > I don’t think this is quite right - making your foreign keys explicit has a purpose beyond syntactic sugar: specifically, you can enforce referential integrity checks and automate cascading operations. Definitely, but relationships in Filemaker or Access are something above and beyond foreign keys. ---
**Author:** ![Do\_Not\_Taunt](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/do_not_taunt/32/2968_2.png) [@Do\_Not\_Taunt](https://boards.straightdope.com/u/Do_Not_Taunt)\ **Post date:** [January 18, 2009, 5:32pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/14 "2009-01-18T17:32:52Z")
> [@ultrafilter](#): > > Definitely, but relationships in Filemaker or Access are something above and beyond foreign keys. Color me confused - first, wasn’t the OP a question about how to do something in MySQL, not Filemaker or Access? And secondly, as a non-filemaker/access user, what do you mean by this? If relationships in these products are “above and beyond” foreign keys, wouldn’t that mean they can be used for everything FKs are used for and more? Which would mean that my statement about how FKs are not merely syntactic sugar would apply equally or moreso to relationships? ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [January 18, 2009, 6:06pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/15 "2009-01-18T18:06:52Z")
Relationships are above and beyond foreign keys exactly in the sense that they are syntactic sugar. In Access, you set up relationships by laying out graphics representing your tables in a window and drawing lines between primary keys and foreign keys. But it doesn’t do anything that an SQL CREATE TABLE statement with appropriate foreign key syntax doesn’t do. ---
**Author:** ![pilzner](https://avatars.discourse-cdn.com/v4/letter/p/f17d59/32.png) [@pilzner](https://boards.straightdope.com/u/pilzner)\ **Post date:** [January 18, 2009, 6:55pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/16 "2009-01-18T18:55:44Z")
> [@ultrafilter](#): > > Relationships are above and beyond foreign keys exactly in the sense that they are syntactic sugar. In Access, you set up relationships by laying out graphics representing your tables in a window and drawing lines between primary keys and foreign keys. But it doesn’t do anything that an SQL CREATE TABLE statement with appropriate foreign key syntax doesn’t do. Not working w/ access at all, I find this statement very confusing. I think your def of a relationship (correct me if wrong, but the graphic query designer that is just a shortcut for writing a query in text, and possibly the JOIN statement included in that text) is a little different than an actual data relation between two fields. It is definitely more than syntactic sugar and has very little to do with writing a query. Generally, a relation: - Maps a foreign key to a primary key in another table - Only has foreign key values from the available primary keys - Can enforce the above bullet with a CONSTRAINT, though it doesn’t necessarily have to. - Typically is marked as the two fields having the same name, or having a constraint, or both, so as to allow for easy automated documentation. Also ensures that other developers don’t have to pepper you with questions on what your intent was. I can go into Access, or even SQL Server and write a query that joins completely unrelated fields, say IDCustomer to IDGearAssemlyLine. I may even get rows back. But on a data level, this does not make a relation. ---
**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\ **Post date:** [January 18, 2009, 6:56pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/17 "2009-01-18T18:56:05Z")
> [@AHunter3](#): > > but I was expecting a little bit more structure than this! What additional “structure” are you looking for? Let’s consider your 2 scenarios: > [@](#): > > **a)**…and then define a relationship between the two like so: > > TableOne::employeeserial = TableTwo::employeeserial > > OK walk me through how I do that part in MySQL please? As previously mentioned, the MySQL database has explicit syntax of CONSTRAINT FOREIGN KEY REFERENCES which is equivalent to your scenario **a**. But as I mentioned before, you really don’t even need to bother with this unless you want the database to enforce referential integrity or cascading deletes/updates. You can “unlearn” the desire to explicitly define a relationship – it’s just not necessary in the SQL world. > [@](#): > > \*\*b)\*\*I wish to establish a _second relationship_ between TableOne and TableTwo, such that employeeserial in TableOne equals employeeserial in TableTwo AND the field status in TableTwo does NOT equal “Inactive”. There is no exact equivalence of this in MySQL or any other enterprise database. To approximate this functionality, you can write the logic of this “conditional” relationship in MySQL stored procedures (database triggers) and/or the frontend code (e.g. PHP) or just do it “on-the-fly” via SQL SELECT JOIN clauses. > [@](#): > > Yes, I would benefit greatly if there were s website or a good book about SQL system written explicitly for people coming in from FileMaker. They do that for folks switching from Windows to Mac, which is not as big a leap. IMO, the mental conversion from FileMaker to MySQL is actually a smaller leap than Win-Mac if you only consider the _database engine_ component of FileMaker. Mental mapping of tasks is difficult because FileMaker is an out-of-the-box integration of database\_engine+GUI\_forms+report\_writer. If you look at the [strict definition](http://en.wikipedia.org/wiki/Database_management_system) of a database by computer scientists such as E.F. Codd, you’ll see no mention of fancy conditional relationships or GUI forms or reports. This “pure database” is the type of product MySQL is so it will look artificially handicapped compared to FileMaker. The FileMaker learning map looks something more like this: [ul] [li]FileMaker scripting for GUI –\> PHP[/li][li]FileMaker scripting for database operations –\> MySQL stored procedure syntax, triggers[/li][li]FileMaker reports –\> Crystal Reports[/li][li]FileMaker drag-drop development coding environment –\> MS Visual Studio, SQL editor studio, or other fancy text editor[/li][/ul] I think you already know the conceptual breakdowns I’ve listed because you mentioned earlier that you anticipated PHP code doing a lot of the “heavy lifting.” FileMaker combines the functionality of half-a-dozen products into one. So what’s the tradeoff? The tradeoff is that FileMaker can’t handle 5 billion rows of data and its primitive locking mechanism doesn’t handle 1000 simultaneous web connections. To get the heavy duty “mission critical” database functionality, the tradeoff is to split all that integration into separate products and separate people with separate specialties (database admins, programmers, user-interface experts, etc). It does make learning it much more of a hassle. ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [January 18, 2009, 7:03pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/18 "2009-01-18T19:03:03Z")
> [@pilzner](#): > > Not working w/ access at all, I find this statement very confusing. I think your def of a relationship (correct me if wrong, but the graphic query designer that is just a shortcut for writing a query in text, and possibly the JOIN statement included in that text) is a little different than an actual data relation between two fields. It is definitely more than syntactic sugar and has very little to do with writing a query. Generally, a relation: > > - Maps a foreign key to a primary key in another table > - Only has foreign key values from the available primary keys > - Can enforce the above bullet with a CONSTRAINT, though it doesn’t necessarily have to. > - Typically is marked as the two fields having the same name, or having a constraint, or both, so as to allow for easy automated documentation. Also ensures that other developers don’t have to pepper you with questions on what your intent was. > > I can go into Access, or even SQL Server and write a query that joins completely unrelated fields, say IDCustomer to IDGearAssemlyLine. I may even get rows back. But on a data level, this does not make a relation. Really, I do understand what a relationship is in the ER sense of the term. That’s not what I’m talking about here–I’m talking about relationships as Access and apparently Filemaker use the word. ---
**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\ **Post date:** [January 18, 2009, 7:04pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/19 "2009-01-18T19:04:37Z")
> [@pilzner](#): > > Not working w/ access at all, I find this statement very confusing. To add to what **ultrafilter** said… in MS Access there is a GUI screen where you actually define and build the relationships by drawing lines from primary to foreign keys. This relationship is stored as metadata and is _independent_ of any queries or any SELECT statements. This would somewhat equivalent of explicit relationships defined via explicit SQL syntax of CONSTRAINT FOREIGN KEY REFERENCES. If one were to run the “database documentation wizard” in MS Access, it would know about these “relationships” and print them out. MS Access also has a similar GUI in Query Builder that lets you draw lines between primary and foreign keys that’s only active for that particular query. I’m guessing this is the scenario you’re thinking of. ---
**Author:** ![pilzner](https://avatars.discourse-cdn.com/v4/letter/p/f17d59/32.png) [@pilzner](https://boards.straightdope.com/u/pilzner)\ **Post date:** [January 18, 2009, 7:35pm UTC](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813/20 "2009-01-18T19:35:58Z")
> [@Ruminator](#): > > To add to what **ultrafilter** said… in MS Access there is a GUI screen where you actually define and build the relationships by drawing lines from primary to foreign keys. This relationship is stored as metadata and is _independent_ of any queries or any SELECT statements. This would somewhat equivalent of explicit relationships defined via explicit SQL syntax of CONSTRAINT FOREIGN KEY REFERENCES. If one were to run the “database documentation wizard” in MS Access, it would know about these “relationships” and print them out. > > MS Access also has a similar GUI in Query Builder that lets you draw lines between primary and foreign keys that’s only active for that particular query. I’m guessing this is the scenario you’re thinking of. Ahah, ok, this makes a lot more sense to me then. Apologies Ultrafilter, I think I got very confused as to which tool you were “drawing the lines in”. Sorry for the lecture! [Next page](https://boards.straightdope.com/t/relationships-in-mysql-help-and-or-listen-to-me-rant/481813.md?page=2)