SQL question

I am about to make a database for a simple messaging system.

I'm using the awesome SQL Designer to define the tables. I notice that it is possible to define relationships between tables using the 'foreign key' constraint.

I fully understand the principle of relational databases, but I don't see why it is useful to 'tell' the DBMS which fields refer to entries in other tables? Why bother? Surely only my application need worry about cross referencing of tables?

 

Log in or Sign up to hide all adverts.

Probably don't need it. Constraints limit the values based on that foreign key.

I guess in your messaging system you could want to limit who is in what messaging group(if that were say a table) based on what Department they were in(HR,IT,Sciforum'sshitdisterbers---table.)

 

Log in or Sign up to hide all adverts.

That's a pretty slick little web app, that SQL Designer. Thanks for sharing.

Why is it useful to tell the DBMS how the different tables relate to each other? I suppose you're right. If you design your application well, the application should be enough to ensure the integrity of the data that go and out of your database. I guess you'd do it for redundancy. All that really matters in plain data terms is that the fields you have which relate to each other are of the same data type and other characteristics. By telling the DBMS what relates to what and how, I'm sure it would help you know whether there would be any data integrity issues between one field and another. It might make debugging simpler.

It's a good practice to do both, but if you're making a simple program it probably doesn't matter a whole lot.

 

Log in or Sign up to hide all adverts.

The foreign key constraints are for validation purposes. It's the same reason it needs to know what your primary key is. You can't insert 2 rows with the same primary key, because that doesn't make sense to do so. For that reason, the database won't let you do it. It will give you an error.

It's the exact same thing with telling it the foreign key. If you try to insert something into a table, and it violates the foreign key constraint, it will give you an error. So it's like a safety net to keep you from accidentally doing something stupid.

 

Thanks, everybody.

Yeah, I recommend checking out the link in the OP. The tables worked perfectly. You can also 'save' and 'load', but you share the same space as everyone else.