Implementing a One-To-One Business rule.
SQL Server can only implement a One-To-Zero Or One relationship.
When we attempt a One-To-One declaritevly, it turns out to be useless. Strange RI
Unfortunately, most real world cases need a true One-To-One Constraint.
As an example I'm going to use the Customer to Individual example.
That is, a Customer can be an Individual or some other "type".
But the Customer entity MUST have a corresponding row in the Individuals entity.
Usually we have other "types" of customers such as businesses or charities, but to be succinct I am only going to use the Individual.
I am hoping Jeff Smith will post an extension to this...
I am been mucking around with declarative constraints lately and have come across a few weird ones..
This first schema is the only possible way to implement a true one-to-one relationship in SQL Server declaratively
It is such a pity it is impossible to actual add any rows, without issuing a NOCHECK CONSTRAINT.
I will post a solution on how to implement a true one-to-one using security and instead of triggers later
The second is a Self Reference to the same column!
CREATE SCHEMA AUTHORIZATION DBO
CREATE TABLE A (Letter CHAR(1) NOT NULL PRIMARY KEY,
CONSTRAINT FK_A FOREIGN KEY (Letter) REFERENCES B(Letter))
CREATE TABLE B (Letter...
What is wrong with this Schema?
CREATE TABLE IdentityTable (ParentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY)
CREATE TABLE ChildOfIdentity(ChildID INT NOT NULL PRIMARY KEY, ParentID INT NOT NULL
CONSTRAINT FK_ForeignKey FOREIGN KEY (ParentID) REFERENCES IdentityTable(ParentID) ON UPDATE CASCADE)
Using UDF's for n Cardinality relationships.
SQL Server can only represent 3 types of "relationship" constraints (FK) declaratively
1) One to One - (One to (0 or 1))
2) One to Many - (One to (0 or n))
3) Many to Many
This serverly limits the practical implementation of a particular business model that requires a definate
cardinality on one side.
The following T/SQL shows how to implement a One to <=5 Constraint and also how to modify the constraint to meet changing business requirements.
The example below will enforce the rule that a Cop can only have 5 or less Donut types.
Thanks to late checking we can build this schema as neatly...
For some unknown reason I seem to be getting a lot of SQL questions regarding maths functions.
An earlier post was concerned with Prime and Perfect numbers ( Post)
but this time they have asked for Combinations and Permutations
Just quickly the formulaes for them are:
Combinations = N!/(K!*(N-K)!)
Permutations = N!/(N-K)!)
where N is the number of elements and K is the size of the permutation/combination.
and ! is for factorial...
First thing to do is to create the Factorial function...
CREATE FUNCTION dbo.Factorial
DECLARE @Result BIGINT
SET @Result = 1
WHILE @N > 1
SET @Result = @Result * @N
SET @N = @N-1
The maximum allowed value...