Foreign Key Contraints in Many-To-Many Relationships

Constraint for a many-to-many relationship table - both the related records need to reference the same dependent record?

You can do this without a trigger or special functions. The idea is to use foreign key relationships . . . by defining an extra set of keys and adding DepartmentId (redundantly) to the junction table:

create table Students (
    StudentId int primary key, 
    DepartmentId int not null references Department(Id), 
    -- ....
    unique (DepartmentId, StudentId)
);

create table Classes (
    ClassId int primary key, 
    DepartmentId int not null references Department(Id), 
    ....
    unique (DepartmentId, ClassId)
);

create table StudentClasses (
    DepartmentId int references Department(DepartmentId),
    StudentId int, 
    ClassId int, 
    -- ....
    primary key (StudentId, ClassId),
    foreign key (DepartmentId, StudentId) references (DepartmentId, StudentId),
    foreign key (DepartmentId, ClassId) references (DepartmentId, ClassId),
);

You may not want the redundancy, but it is possible without triggers or special functions.

How to generate a foreign key for a Many-to-one relationship (unidirectional)?

You can have OneToMany and ManyToOne, both in unidirectional and bidirectional way. Obviously, when you have a many-to-one relation from one side, you will have a one-to-many from the opposite side.
Also, you should note that only one foreign key in the many-side to one-side can handle this relation.

If you use @JoinColumn(name="some_column_name") just below one of the @OneToMany or @ManyToOne annotations, the hbm2ddl should be able to create the proper foreign key in your table.

However, try not to rely on hbm2ddl and maintain the database schema yourself.

Foreign keys in many-to-many relationship?

The minimum is two columns -- The Primary Key for each of the other two tables, perhaps you call them flight_id and company_id. Each of those might be an AUTO_INCREMENT in their tables, but there is no need for an auto_inc in the mapping table.

See this for a discussion of indexing: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Everything specific to the flight should be in the Flights table. Ditto for Companies. It should not be in the many:many table.

A few schema designers add another column for ordering or some kind of qualification on the relation.

Many to Many with Variable Foreign Key

A foreign key must be primary key on another table. So basically, your subscription table must have more than one foreign key. The correct structure to your subscription table should be something like:

id (PK)
user_id (FK referencing USER PK)
movie_id (FK referencing MOVIE PK)
tv_show_id (FK referencing TV SHOW PK)

This structure should handle your problem.

Enforcing common foreign key in many-to-many relationship

My assumption is that in your current model Location has 1 restaurant and Offer has 1 Restaurant.

You can solve your problem by making a compound key on Offer: (Restaurant_ID, Offer_ID) and use this key as a foreign key from Location_Offers to Offer.

You can do the same on Location: make a compound key (Restaurant_ID, Location_ID) and use this as a foreign key from Locations_Offer to Location.

This ensures that any record in Locations_Offer that links a Location and an Offer, only links those that have a relation with the same Restaurant.

Foreign key contraints in many-to-many relationships

We are using both SQLite3 (locally/testing) and PostgreSQL (deployment).

This is begging for trouble. You will keep running into minor incompatibilities. Or not even notice them until much later, when damage is done. Don't do it. Use PostgreSQL locally, too. It's freely available for most every OS. For someone involved in a "databases course project" this is a surprising folly. Related:

• Generic Ruby solution for SQLite3 "LIKE" or PostgreSQL "ILIKE"?

Other advice:

• As @Priidu mentioned in the comments, your foreign key constraints are backwards. This is not up for debate, they are simply wrong.

• In PostgreSQL use a serial or IDENTITY column (Postgres 10+) instead of SQLite AUTOINCREMENT. See:

  • Auto increment table column

• Use timestamp (or timestamptz) instead of datetime.

• Don't use mixed case identifiers.

  • PostgreSQL Trigger Exception

  • Are PostgreSQL column names case-sensitive?

• Don't use non-descriptive column names like id. Ever. That's an anti-pattern introduced by half-wit middleware and ORMs. When you join a couple of tables you end up with multiple columns of the name id. That's actively hurtful.

• There are many naming styles, but most agree it's better to have singular terms as table names. It's shorter and at least as intuitive / logical. label, not labels.

Everything put together, it could look like this:

CREATE TABLE IF NOT EXISTS post (
   post_id   serial PRIMARY KEY
 , author_id integer
 , title     text
 , content   text
 , image_url text
 , date      timestamp
);

CREATE TABLE IF NOT EXISTS label (
   label_id  serial PRIMARY KEY
 , name      text UNIQUE
);

CREATE TABLE IF NOT EXISTS label_post(
    post_id  integer REFERENCES post(post_id) ON UPDATE CASCADE ON DELETE CASCADE
  , label_id integer REFERENCES label(label_id) ON UPDATE CASCADE ON DELETE CASCADE
  , PRIMARY KEY (post_id, label_id)
);

Trigger

To delete unused labels, implement a trigger. I supply another version since I am not happy with the one provided by @Priidu:

CREATE OR REPLACE FUNCTION f_trg_kill_orphaned_label() 
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   DELETE FROM label l
   WHERE  l.label_id = OLD.label_id
   AND    NOT EXISTS (
      SELECT 1 FROM label_post lp
      WHERE  lp.label_id = OLD.label_id
      );
END
$func$;

• The trigger function must be created before the trigger.

• A simple DELETE command can do the job. No second query needed - in particular no count(*). EXISTS is cheaper.

• Single-quotes around the language name are tolerated, but it's an identifier really, so just omit the nonsense: LANGUAGE plpgsql

CREATE TRIGGER label_post_delaft_kill_orphaned_label
AFTER DELETE ON label_post
FOR EACH ROW EXECUTE PROCEDURE f_trg_kill_orphaned_label();

There is no CREATE OR REPLACE TRIGGER in PostgreSQL, yet. Just CREATE TRIGGER.

---

Related Topics