November 2003 Blog Posts

  • 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...