All we need is an easy explanation of the problem, so here it is.
Among Codd’s 12 rules for Relational Database Management Systems is Rule 1, which states the following:
Rule 1: Information rule
All information(including metadata) is to be
represented as stored data in cells of tables. The rows and columns
have to be strictly unordered.
Why is it important that metadata be stored in tables?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Metadata isn’t uniquely more important than non-metadata in this context. Rather, his rule just simply wants the reader to realize that "All Information" is inclusive of even the metadata regarding the database itself. This is useful for understanding the structure of the database and the relational model behind it. This is why the Information Schema is part of the ANSI SQL standards.
None of Codd’s rules live in isolation. They all work together to give you that wonderful experience you have today with modern RDBMS, which we all take for granted today. Because before Codd’s rules, the world was a horrible horrible place that we "youngsters" didn’t know and the "elders" desperately try to forget.
Just imagine that horrible world where for every little task, you had to read a different user manual, use a different programming language, apply a different mindset, paradigm, etc. etc.
I think about it every time I’m surprised I can’t do some simple administrative task from my favourite SQL editor (Dbeaver in my case, you can use whatever other tool you want, another side effect of Codd’s rules!). Like
- Creating a user.
- Giving that user some grants.
- Creating a database.
- Dropping a database.
- Importing data
- Renaming things.
- Migrating things.
- Updating things.
- Reading things. Including meta data things.
I can do all of these things using a single tool (if I have the privileges, of course). I don’t have to go to some weird command line and learn some funky commands. I don’t have to write a script in a language that only 3 people on the planet understand. No. It’s all SQL (Rule 5 "The comprehensive data sublanguage rule").
And with respect to Rule 1, like all rules, it doesn’t live in isolation. Just imagine, in order to check whether that column that you want to add already exists. Imagine you had to go outside of that wonderful realm of SQL for the check and come back later to add the column. No. You don’t. You run a quick:
SELECT * FROM information_schema.columns WHERE table_schema = :table_schema AND table_name = :table_name AND column_name = :column_name
And you can script that, again, in SQL or a procedural language that is an extension to SQL. Never leaving that single ecosystem where you can do almost every task you need to do in your RDBMS.
By the way, Codd’s rules don’t have to be strictly about RDBMS even if that’s what they’re designed for. If the politics is allowed to be removed from the rules, they could apply to all DBMS.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂