-1

Why do we define key relationships in SQL Management Studio?. I am a student and teacher wanted this. In fact I think that It is not necessary, because I can connect tables with sql queries. For example: We have EMPLOYEE and MANAGER table. EMPLOYEE has ID, Name, SuperID(supervisorID in MANAGER) MANAGER has ID, Name In fact teacher said that "you should connect two tables in the SQL Server Management, for Employee's SuperID is foreign key of MANAGER's ID and " I can connect(i.e I can see connection of two tables) two tables with sql query like:

SELECT e.*,m.Name as 'Manager's Name' FROM EMPLOYEE e, MANAGER m 
WHERE e.SuperTC = m.TC

It is ok but why do we connect two table in SQL Management Studio?(Primary Key, Foreign Key section in SQL Management Studio) If we don't connect what will be happen? Is it necessary when we work in professional company?

DLeh
  • 23,806
  • 16
  • 84
  • 128
user1937692
  • 35
  • 1
  • 9
  • 1
    possible duplicate of [Are foreign keys really necessary in a database design?](http://stackoverflow.com/questions/18717/are-foreign-keys-really-necessary-in-a-database-design) – D Stanley Jan 06 '15 at 21:18
  • The primary reason is to enable referential integrity without writing custom queries in triggers. Some designers will also use foreign keys to relate tables for you. The SQL ends up the same but it saves you the trouble and risk of typing it yourself. – D Stanley Jan 06 '15 at 21:19
  • And you know with absolute certainty that the queries you write will be the only way that data is accessed... ever!? – Dean Kuga Jan 06 '15 at 21:21
  • have your teacher show you current (not 20 year old) join syntax. – KM. Jan 06 '15 at 21:32
  • IMO, this really depends on the size of the database, and the amount of data intended on being stored. If nothing else, using foreign keys, as many others have pointed out, will prevent orphan records (records sitting out in the database that don't have correct data tied to them). If it's a simple database, then this might not be as big of a concern though. That being said, the PROPER way to do it is to use foreign keys for all of your relationships. – user2366842 Jan 06 '15 at 21:33

2 Answers2

2

To enforce referential integrity and prevent orphaned records. It tells the database what it needs to know to make it impossible to store data that is not valid according to your business rules. If you make it impossible to store invalid data, then your data will be valid. Valid data makes everybody happy.

  • If each EMPLOYEE must have a MANAGER, then tell the database that and it won't let you create an EMPLOYEE record without a MANAGER reference.

  • If a MANAGER needs to be deleted but you still have an EMPLOYEE that lists that id as a MANAGER, the database won't let you delete the MANAGER (or can be told to also delete the EMPLOYEE with cascaded deletes).

  • If the key of a MANAGER needs to change, a cascaded update rule on a foreign key will automatically update any associated EMPLOYEE records automatically.

If we don't connect what will be happen?

@DeanKuga raises an important point. Most large customers of major applications will want or need direct database access. This may be to use third party reporting applications, mass data manipulation your front-end doesn't provide, or imports or exports to other systems they own.

Here's something a lot of developers don't think about: Even though you're only licencing the application to your customers and you will still own the application, the customer owns the data. It's their data in every sense. Your rights as an independent vendor end with copyrights and patents on the program code. If the customer wants to connect to the RDBMS they have a license for on the server they own to read the data they own, that is their right and they absolutely will do that. That is not reverse engineering. Indeed, this capability is one of the primary and express purposes of using a general RDBMS and it's one reason why customers love them. They can always get their data out. Customers do not want giant information silos that prevent them from moving data to where they need it. Whatever the application is that you sold them, it almost certainly will not do everything that the business needs. They will have multiple applications and they all need to communicate to work together.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
0

Yes it is necesarry when going into pro. In fact, when you specify a foreign key field in a table, that refers to a primary key to an other one, that field will be indexed. And Indexes in SQL Server presents a large gain in performance. Indexes in SQL Server will improve the fetching performance for the SQL Server Engine. Adding Indexes is like putting your field values into a dictionnary and we all know that finding a word in a dictionnary is a lot more esaier than finding it in a list of non-ordered words. The performance is not remarkable for small data, but it will be necessary for big data, that's why when going pro, foreign keys will be necessary since we are dealing with large scale databases. Referential integrity is the second reason, because your database tables are the concrete representation of a certain design that is meant to be preserving your data integrity. If you design a something and you do not put it in place, why will we be designing databases? With foreign keys, you are sure that your design in conform to your tables implementation.

Hamdi Baligh
  • 874
  • 1
  • 11
  • 30
  • Foreign keys get indexed by default, however nothing prevents someone from manually creating their own index if they need it. – user2366842 Jan 06 '15 at 21:38
  • @user2366842, SQL Server will not automatically create an index on a foreign key. http://stackoverflow.com/questions/278982/are-foreign-keys-indexed-automatically-in-sql-server – KM. Jan 07 '15 at 13:34