-
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...
-
Strange RI
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!
--True One-to-One
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...
-
IDENTITY madness..
What is wrong with this Schema?
CREATE TABLE IdentityTable (ParentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY)
GO
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)
GO
-
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...
-
More Maths...
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...
--Factorial function
CREATE FUNCTION dbo.Factorial
(@N INT)
RETURNS BIGINT
AS
BEGIN
DECLARE @Result BIGINT
SET @Result = 1
WHILE @N > 1
BEGIN
SET @Result = @Result * @N
SET @N = @N-1
END
RETURN @Result
END
GO
SELECT dbo.Factorial(3)
GO
The maximum allowed value...