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:
|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:
|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:
actor_id, first_name, last_name, COUNT(*)
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