locked
Consequences of not creating relationships between tables? RRS feed

  • Question

  • I have recently started a web company in China using MySQL and PHP as the development environment. Apparently the common practice here is to not create relationships between tables. They would draw out the relationship in the logical ERD, but when it came to the actual DDL, they would only create the table without defining foreign keys. Obviously I found this to be very strange. My staff explained that checks were done on the front end to ensure that there were no data integrity errors. For example, we have 2 tables: State (State_ID, State_Name) and City (City_ID, City_Name, State_Name). These are independent tables with no relationship. To ensure a City is entered with a corresponding State that exists, they would just populate the combo box with the State names from the state table. And also have another check to ensure that the State entered (that is going to be saved to the City table) exists in the State table. It seems that this is a viable way to do it, and the end result would be the same. But are there any consequences to proceeding this way, such as speed or other issues?
    Friday, April 22, 2011 11:09 AM

Answers

  • It seems that this is a viable way to do it, and the end result would be the same. But are there any consequences to proceeding this way, such as speed or other issues?


    Enforcing data integrity in the application code will provide the same result, but only if the application is bug-free for the lifetime of the application.  Database enforced referential integrity will protect you against such code defects and also guarantee data are consistent when modified via other means.

    The SQL Server optimizer can use constraints to optimize performance.  For example, the referenced Parent table will not be touched at all in the SELECT below, assuming the foreign key constraint is trusted.

     

    CREATE TABLE dbo.Parent(
    	ParentID int NOT NULL
    		CONSTRAINT PK_Parent PRIMARY KEY
    	,ParentName varchar(100) NOT NULL
    	);
    
    CREATE TABLE dbo.Child(
    	ChildID int NOT NULL
    		CONSTRAINT PK_ChildID PRIMARY KEY
    	,ChildName varchar(100) NOT NULL		
    	,ParentID int NOT NULL
    		CONSTRAINT PK_Child_Parent FOREIGN KEY
    		REFERENCES dbo.Parent(ParentID)
    	);
    	
    SELECT ChildID, ChildName
    FROM dbo.Child
    WHERE EXISTS(
    	SELECT *
    	FROM dbo.Parent
    	WHERE Parent.ParentID = Child.ParentID
    	);
    
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, April 22, 2011 2:57 PM
    Answerer
  • Dan

    >>>the referenced Parent table will not be touched

    Yes, if ...FROM sys.foreign_keys

    WHERE

    is_not_trusted = 0


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, April 27, 2011 5:18 AM

All replies

  • Call me old fashioned, but I think of the relationships and constraints built into the database itself as a developer's safety net. It's not about either/or, but about layers of redundant protection levels for the data itself.

    When I read your description, I see removal of one important level of data protection and complete reliance on developers to *never* make a mistake. What value do you place on your data?

     

    Friday, April 22, 2011 1:33 PM
  • It seems that this is a viable way to do it, and the end result would be the same. But are there any consequences to proceeding this way, such as speed or other issues?


    Enforcing data integrity in the application code will provide the same result, but only if the application is bug-free for the lifetime of the application.  Database enforced referential integrity will protect you against such code defects and also guarantee data are consistent when modified via other means.

    The SQL Server optimizer can use constraints to optimize performance.  For example, the referenced Parent table will not be touched at all in the SELECT below, assuming the foreign key constraint is trusted.

     

    CREATE TABLE dbo.Parent(
    	ParentID int NOT NULL
    		CONSTRAINT PK_Parent PRIMARY KEY
    	,ParentName varchar(100) NOT NULL
    	);
    
    CREATE TABLE dbo.Child(
    	ChildID int NOT NULL
    		CONSTRAINT PK_ChildID PRIMARY KEY
    	,ChildName varchar(100) NOT NULL		
    	,ParentID int NOT NULL
    		CONSTRAINT PK_Child_Parent FOREIGN KEY
    		REFERENCES dbo.Parent(ParentID)
    	);
    	
    SELECT ChildID, ChildName
    FROM dbo.Child
    WHERE EXISTS(
    	SELECT *
    	FROM dbo.Parent
    	WHERE Parent.ParentID = Child.ParentID
    	);
    
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, April 22, 2011 2:57 PM
    Answerer
  • Dan

    >>>the referenced Parent table will not be touched

    Yes, if ...FROM sys.foreign_keys

    WHERE

    is_not_trusted = 0


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, April 27, 2011 5:18 AM
  • Hi The purpose of creating relationship between tables is just data integrity(consistency and validity)? Am i right? Best Regard Mehran

    Mehran

    Monday, April 14, 2014 10:32 AM
  • Hi The purpose of creating relationship between tables is just data integrity(consistency and validity)? Am i right?

    The main purpose of constraints is to ensure data integrity.  A side effect of trusted constraints in SQL Server is that they also provide additional information the optimizer can use to produce a more optimal query plan.  In the query I posted earlier, SQL Server does not even touch the related table because it knows the NOT EXISTS condition cannot be true due to the trusted foreign key constraint.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, April 14, 2014 12:05 PM
    Answerer
  • There is a hierarchy of implementing RDBMS solutions:

    1. Constraints

    2. Stored Procedures (views, udf-s)

    3. Triggers

    4. Client applications

    Solutions should be implemented at the lowest level at the above hierarchy.

    Modern databases (SS, ORACLE, DB2) are designed to be optimal when PRIMARY KEY, UNIQUE KEY, FOREIGN KEY CHECK and default constraints applied in database design.

    The above constraints are used for:

    1. Data integrity enforcement

    2. Programming - JOIN PK to FK for example

    3. Supporting applications - MS and 3rd party software; example GUI diagram tool uses FK-PK constraints:

    https://www.youtube.com/watch?v=DMnV7vKQXwY


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, April 14, 2014 1:59 PM