First, there is a design rule of thumb that a table models either a single entity type or a relationship between entity types but not both. Therefore, I envision three tables, Media (entity), Idea (entity) and IdeasMedia (relationship). p.s. you know the singular of 'media' is 'medium', right? :)
Here's some Standard SQL-92 DDL that focuses on keys only:
CREATE TABLE Media (MediaID INTEGER NOT NULL UNIQUE);
CREATE TABLE Idea (IdeaID INTEGER NOT NULL UNIQUE);
CREATE TABLE IdeasMedia
(
MediaID INTEGER NOT NULL REFERENCES Media (MediaID),
IdeaID INTEGER NOT NULL REFERENCES Idea (IdeaID)
);
CREATE ASSERTION Idea_must_have_media DEFERRABLE
CHECK (
NOT EXISTS (
SELECT *
FROM Idea AS i
WHERE NOT EXISTS (
SELECT *
FROM IdeasMedia AS im
WHERE im.MediaID = i.IdeaID
)
)
);
There is a 'chicken and egg' scenario here: can't create an idea with without a referencing IdeasMedia but can't create an IdeasMedia without creating an Idea!
The ideal (set-based) solution would be for SQL Standard to support multiple assignment e.g.
INSERT INTO Media (MediaID) VALUES (22),
INSERT INTO Idea (IdeaID) VALUES (55),
INSERT INTO IdeasMedia (MediaID, IdeaID) VALUES (22, 55);
where the semicolon indicates the SQL statement boundary at which point constraints are checked and the commas denoting the sub-statements.
Sadly, there are no plans to add this set-based paradigm to the SQL Standard.
The SQL-92 (procedural) solution to this is as follows:
BEGIN TRANSACTION;
INSERT INTO Media (MediaID) VALUES (22);
SET CONSTRAINTS Idea_must_have_media DEFERRED;
-- omit the above if the constraint was declared as INITIALLY DEFERRED.
INSERT INTO Idea (IdeaID) VALUES (55);
INSERT INTO IdeasMedia (MediaID, IdeaID) VALUES (55, 22);
SET CONSTRAINTS Idea_must_have_media IMMEDIATE;
-- above may be omitted: constraints are checked at commit anyhow.
COMMIT TRANSACTION;
Sadly, SQL Server doesn't support CREATE ASSERTION nor CHECK constraints that can refer to other tables nor deferrable constraints!
Personally, I would handle this in SQL Server as follows:
- Create 'helper' stored procs to add, amend and remove
Ideas and their respective
IdeasMedia relationships.
- Remove update privileges from the tables to force users to use the
procs.
- Possibly use triggers to handle scenarios when deleting
Media and
Idea entities.
Certainly, this (again procedural) implementation is far removed from the ideal set-based approach, which probably explains why most SQL coders turn a blind eye to a requirement for a 1:1..N relationship and instead assume the designer meant 1:0..N !!
