Hello there I've got a short question about database design. I additionally attempted the search but aren't able to find things i am searching for. Here is my question:
I've two database tables Idea and Media (1:N). So essentially what this means is one idea might have none, one or more medias. However I requested myself if you can define the table that every idea should have a minumum of one media. If this sounds like possible how do i accomplish this with MS SQL Server 2008?
I really hope somebody can assist me.
Thx alot for the help
UPDATE: this is exactly what it appears like right now:
First, there's a design guideline that the table models whether single entity type or perhaps a relationship between entity types although not both. Therefore, I invisage three tables,
Idea (entity) and
IdeasMedia (relationship). p.s. you realize the singular of 'media' is 'medium', right? :)
Here's some Standard SQL-92 DDL that concentrates on secrets 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's a 'chicken and egg' scenario here: can't create a concept with with no referencing
IdeasMedia but can't create an
IdeasMedia without creating an
The perfect (set-based) solution could be for SQL Standard to aid multiple assignment e.g.
INSERT INTO Media (MediaID) VALUES (22), INSERT INTO Idea (IdeaID) VALUES (55), INSERT INTO IdeasMedia (MediaID, IdeaID) VALUES (22, 55);
in which the semicolon signifies the SQL statement boundary after which constraints are checked and also the commas denoting the sub-claims.
Sadly, you will find no intends to add this set-based paradym towards the SQL Standard.
The SQL-92 (procedural) means to fix this really is the following:
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 does not support
CREATE ASSERTION nor
CHECK constraints that may make reference to other tables nor deferrable constraints!
Personally, I'd handle this in SQL Server the following:
- Create 'helper' saved procs to include, amend and take away
Ideasas well as their particular
- Remove update rights in the tables to pressure customers to make use of the procs.
- Possibly use triggers to deal with situations when removing
Certainly, this (again procedural) implementation is way taken off the perfect set-based approach, which most likely describes why most SQL programmers turn a blind eye to some requirement of single:1..N relationship and rather assume the designer meant 1:..N !!