In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.
In SQL, functional dependencies appear whenever there is a unique constraint (e.g. a primary key constraint). Let’s assume the following:
| 1
2 3 4 5 | CREATE TABLE actor (
actor_id BIGINT NOT NULL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL ); |
It can be said that both FIRST_NAME and LAST_NAME each have a functional dependency on the ACTOR_ID column.
Nice. So what?
This isn’t just some mathematical statement that can be applied to unique constraints. It’s extremely useful for SQL. It means that for every ACTOR_ID value, there can be only one (functionally dependent) FIRST_NAME and LAST_NAME value. The other way round, this isn’t true. For any given FIRST_NAME and/or LAST_NAME value, we can have multiple ACTOR_IDvalues, as we can have multiple actors by the same names.
Because there can be only one corresponding FIRST_NAME and LAST_NAME value for any given ACTOR_ID value, we can omit those columns in the GROUP BY clause. Let’s assume also:
| 1
2 3 4 5 6 7 8 | CREATE TABLE film_actor (
actor_id BIGINT NOT NULL, film_id BIGINT NOT NULL,
PRIMARY KEY (actor_id, film_id), FOREIGN KEY (actor_id) REFERENCS actor (actor_id), FOREIGN KEY (film_id) REFERENCS film (film_id) ); |
Now, if we want to count the number of films per actor, we can write:
| 1
2 3 4 5 6 | SELECT
actor_id, first_name, last_name, COUNT(*) FROM actor JOIN film_actor USING (actor_id) GROUP BY actor_id ORDER BY COUNT(*) DESC |
This is extremely useful as it saves us from a lot of typing. In fact, the way GROUP BYsemantics is defined, we can put all sorts of column references in the SELECT clause, which are any of:
- Column expressions that appear in the GROUP BYclause
- Column expressions that are functionally dependent on the set of column expressions in the GROUP BYclause
- Aggregate functions
3 thoughts on “Functional Dependencies”