What happens when we try to drop a table that is being referenced by a view (it's also possible that we get an error if other references exist)? Check out the solution in the video TSQL: "Cannot DROP TABLE Table because it is being referenced by view View". As we can see, this may be an issue depending on how we've architected our schema on the database where we're unable to make changes or remove a table that is referenced. This design is intended for a reason: we wouldn't generally want to drop a table with data referenced by a view.
Some examples we see in the video:
- When creating references, what is a best practice?
- In some contexts with either replication or ETL, what are some cautions about minor derivatives involving changes over drops?
- What is a consideration for dynamic data?
Check out the highest-rated Automating ETL course on Udemy, if you're interested in data.
Referenced objects require thought on the initial creation if we want to apply automation techniques. In general, re-design should be rare. If we find ourselves frequently changing table structures, we may want to re-consider how we've designed our schema and data.