Contraints

Hi,
I have this (syntax not verified):
CREATE TABLE Colors (
Id INT PRIMARY KEY,
Color VARCHAR(20) NOT NULL
)
GO
INSERT INTO Colors VALUES (1, 'red')
GO
INSERT INTO Colors VALUES (2, 'green')
GO
INSERT INTO Colors VALUES (3, 'blue')
GO
CREATE TABLE Candy (
Id INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
ColorId INT REFERENCES Colors(Id)
)
GO
INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
GO
INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
GO
INSERT INTO Candy VALUES (3, 'SweetMama', 2)
GO
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT REFERENCES Candy(Id)
)
Now, you can put all kinds of candy in a pack:
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 3)
GO
This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag 1.
Now, I want to make sure that no bag contains more than 2 candies of the
same kind and same color.
So the following should return ZERO records:
SELECT * FROM
CandyPacks P1, Candy C1, Colors CLR1,
CandyPacks P2, Candy C2, Colors CLR2
WHERE
C1.Id = P1.CandyId
AND CLR1.Id = C1.ColorId
AND C2.Id = P2.CandyId
AND CLR2.Id = C2.ColorId
AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
Now, perhaps this query sucks.. please correct, but my point should be
clear.
This is only an example for similar situations. Perhaps I could use primary
key constraints or something, but in other situations I have some 'complex
query' which should not yield results..
How can I turn a query like the above into a constraint on the CandyPacks
table?
LisaHi Lisa
Please consider using INSTEAD OF INSERT Trigger on the Table.
If the condition is satisified, insert the data
hope the problem is solved? If there are any more issues, do not hesitate to
revert back
thanks and regards
Chandra
"Lisa Pearlson" wrote:

> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||I would think that the best way to accomplish this would be using a trigger.
I would also consider changing the structure of the tables such that a
CandyPacks record contians the packid, a colorid, and a candyid. You may
find it easier to enforce the constraint with the data structured this way.
If you leave it as you have it you will have to write a more complex query i
n
the trigger.
"Chandra" wrote:
> Hi Lisa
> Please consider using INSTEAD OF INSERT Trigger on the Table.
> If the condition is satisified, insert the data
> hope the problem is solved? If there are any more issues, do not hesitate
to
> revert back
> thanks and regards
> Chandra
>
> "Lisa Pearlson" wrote:
>|||Try,
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT not null REFERENCES Candy(Id),
quantity int not null default(1) check(quantity = 1 or quantity = 2),
constraint pk_CandyPacks primary key (PackId, CandyId)
)
AMB
"Lisa Pearlson" wrote:

> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||You could use a check constraint to see wheter how many candy are in the
package:
CHECK(Your Select statement)
Further help, just raise a hand.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
> 1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use
> primary key constraints or something, but in other situations I have some
> 'complex query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>|||*raises hand*
My actual table is:
CREATE TABLE MatchResults
(
ParentId INT NOT NULL REFERENCES Bedrijven(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES Bedrijven(Id),
PRIMARY KEY(ParentId,DatMatch,Id),
Updated DATETIME DEFAULT GETDATE(),
Deleted BIT DEFAULT 0
)
And no data may be added to this table that would return any records in this
query:
SELECT COUNT(*)
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
So the above query should always yield 0.
So I want to add a contraint to the above table so that the query below
always is 0.
I don't want to use INSTEAD OF INSERT trigger...
How do I turn it into a table constraint?
Thanks,
Lisa
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ePt3i5%23SFHA.3980@.TK2MSFTNGP12.phx.gbl...
> You could use a check constraint to see wheter how many candy are in the
> package:
> CHECK(Your Select statement)
> Further help, just raise a hand.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
> news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
>|||On Wed, 4 May 2005 02:50:10 +0200, Lisa Pearlson wrote:

>*raises hand*
>My actual table is:
>CREATE TABLE MatchResults
>(
>ParentId INT NOT NULL REFERENCES Bedrijven(Id),
>DatMatch DATETIME NOT NULL,
>Id INT NOT NULL REFERENCES Bedrijven(Id),
> PRIMARY KEY(ParentId,DatMatch,Id),
>Updated DATETIME DEFAULT GETDATE(),
>Deleted BIT DEFAULT 0
> )
>
>And no data may be added to this table that would return any records in thi
s
>query:
>SELECT COUNT(*)
>FROM MatchResults M, Bedrijven B1, Bedrijven B2
>WHERE M.Deleted!=1
>AND B1.Id = M.ParentId
>AND B2.Id = M.Id
>AND (B1.ProfielId!=3 OR B2.ProfielId=3)
>So the above query should always yield 0.
>So I want to add a contraint to the above table so that the query below
>always is 0.
>I don't want to use INSTEAD OF INSERT trigger...
>How do I turn it into a table constraint?
>Thanks,
>Lisa
Hi Lisa,
Impossible with your current design, since a CHECK constraint can't use
any other data than the data in the same row.
The workaround is to replace the Bedrijven table with two tables: one
for the bedrijven with profiel equal to 3 and one for the bedrijven with
profiel unequal to 3:
CREATE TABLE BedrijvenType3
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId = 3)
)
CREATE TABLE BedrijvenOverig
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId <> 3)
)
CREATE TABLE MatchResults
(ParentId INT NOT NULL REFERENCES BedrijvenType3(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES BedrijvenOverig(Id),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0
)
Note that I also included a Deleted column in the two bedrijven tables,
because the referential integrity can't be made dependant of the Deleted
column in the MatchResults. If you do want to really remove rows from
the bedrijven tables (or if I somehow misunderstood your requirements),
you'll have to use a different technique: make a redundant copy of the
ProfielId for both Id and ParentId (and possibly add a redundant UNIQUE
constraint to the Bedrijven table, so that the data stays synch'ed):
ALTER TABLE Bedrijven
ADD UNIQUE (Id, ProfielId)
CREATE TABLE MatchResults
(ParentId INT NOT NULL,
ParentIdProfiel INT NOT NULL,
FOREIGN KEY (ParentId, ParentIdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL,
IdProfiel INT NOT NULL,
FOREIGN KEY (Id, IdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0,
CHECK (Deleted = 1 OR (ParentIdProfiel = 3 AND IdProfiel <> 3)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Why do you believe that every table has magical, universal column
called "id"? In an RDBMS, there are lots of kinds of identifiers,
not like a 1950's file system record number. And have you ever
researched for industry standards, like the Land color number or
Pantone numbers? Let's clean up the sample DDL:
CREATE TABLE Colors
(pantone_nbr INTEGER PRIMARY KEY,
pantone_description VARCHAR(20) NOT NULL);
CREATE TABLE Candies
(upc CHAR(13) NOT NULL PRIMARY KEY,
candy_name VARCHAR(20) NOT NULL,
pantone_nbr INTEGER REFERENCES Colors(pantone_nbr));
CREATE TABLE CandyPacks
(pack_upc CHAR(13) NOT NULL,
candy_upc CHAR(13) NOT NULL
REFERENCES Candies(upc),
PRIMARY KEY (pack_upc, candy_upc)
);
same kind AND same color. <<
The first condition is enforced by a PRIMARY KEY. In full SQL-92 you
can put this into a CHECK();
CHECK(1 = ALL (SELECT COUNT(C1.pantone_nbr)
FROM Candies AS C1
WHERE C1.upc = CandyPacks.candyupc))
In SQL server, you will need to use a trigger on the packages table.