When you create a foreign key in your database, you can specify what happens upon delete of the parent row. There are usually four possibilities: ON DELETE SET NULL ON DELETE CASCADE ON DELETE NO ACTION ON DELETE RESTRICT Today we’ll investigate the subtle difference between the last two options. In Some Databases There Is No Difference at All In Oracle, there is no RESTRICT keyword. The only option is NO ACTION.
Computer databases first emerged in the late 1960s after computers had become powerful enough to handle large amounts of financial, human resources and bank data. Let’s take a look at the history of how modern databases came about. 1960s: navigational databases, mostly hierarchical databases and network databases, were first used 1970: E.F. Codd from IBM introduces the concept of relational databases and the first normal form 1971: second and third normal form are introduced by Codd
Recently I was given a task which involved counting, with a single SQL query, all tables in each model version of Vertabelo. Vertabelo internally stores each model version as an XML file (download a sample XML file). So my task was to count all XML nodes satisfying a certain XPATH expression. Vertabelo runs on PostgreSQL 9.1, so PostgreSQL 9.1 tools were all I could use. To begin at the end, the final query looks like this:
A good data modeling exercise for beginners is to create a data model of an online store. Every time I give this exercise to my students, I’m surprised at how difficult it is for them. Find the Concepts... Let’s see how it can be done. We know we have to create a table for every concept in the domain. Think about the nouns and noun phrases you would use to describe the domain.
The newest Vertabelo feature is Reverse Engineering: a way to import an existing database into Vertabelo. The Reverse Engineering tool is a simple command-line Java application that you can download from our website. It connects to your database, reads the table and view details and stores the info in an XML file. Import the XML file into your database model and voila - your database is in Vertabelo! How to use the reverse engineering application Download the Reverse Engineering application from our website, then run the Reverse Engineering application.
Many people wonder why relational databases are called “relational.” Some think that it’s because of a logical entity-relationship model you often start your design with. Or, because you have tables and relationships (aka foreign keys) between them. But that’s not the case. The name comes from the mathematical notion of “relation.” It all started with E. F. Codd who in 1970 (in the article A Relational Model of Data for Large Shared Data Banks) proposed something now called relational algebra as the mathematical foundation of databases.
Recently a fellow database architect claimed that in Oracle the type VARCHAR2(255) means a string of 255 bytes, not characters. There is not much difference between the two in the English-speaking world. It matters though if you want to handle people with names like Kołłątaj. (Not that Hugo Kołłątaj – a famous Polish 18th century politician – would ever use any of our systems, but he became our byword for all non-pure-ASCII names).
The question: Why doesn’t my script create tables? The other day I was testing Oracle SQL scripts generated by Vertabelo. Roughly, this is the code that was generated: ... -- Table: book CREATE TABLE book ( id integer NOT NULL, title varchar2(120) NOT NULL, isbn varchar2(15) NOT NULL, PRIMARY KEY (id) ) ; ... I used sqlplus to execute my script and see if it’s correct. sqlplus (database-details) The script run without errors but the tables where NOT created.