constraint????

[Guest]

Hi All,

I could use some help in creating constraints.
I have created a db (access 2003) containing an overview of all cd's in my
collection. Within the db I have created a table Storage (StoragID,
StorageDesc) and a table Disc (DiscID, DiscName).
Within a form I am able to enter all required information (drop down list)
for cd and storage location.
So finally I create an overview like "Programname - Programtype - DiscName -
StorageDesc" >> ("Winamp - Musicprogram - CD2 - Box1")
However it is still possible to enter a second program present on the same
CD but place the cd in another storage fe (Audiograbber - Musicprogram - CD2
- BOX2) in other words CD2 can be present in Box1 but also in Box2.
Now I would like to avoid this by "telling" the form that if there is a
relation between CD1 and Box1, CD1 can not be within two boxes at the same
time. So the form should correct the entry to Box1.

How to do this?

 

[Jamie Collins]

I could use some help in creating constraints.
I have created a db (access 2003) containing an overview of all cd's in my
collection. Within the db I have created a table Storage (StoragID,
StorageDesc) and a table Disc (DiscID, DiscName).
Within a form I am able to enter all required information (drop down list)
for cd and storage location.
So finally I create an overview like "Programname - Programtype - DiscName -
StorageDesc" >> ("Winamp - Musicprogram - CD2 - Box1")
However it is still possible to enter a second program present on the same
CD but place the cd in another storage fe (Audiograbber - Musicprogram - CD2
- BOX2) in other words CD2 can be present in Box1 but also in Box2.
Now I would like to avoid this by "telling" the form that if there is a
relation between CD1 and Box1, CD1 can not be within two boxes at the same
time.

Sounds like you want a table to model the one-to-many relationship
between Disk (one) and Storage. (many). To enforce the 'one' side, put
a unique constraint on the key column from your Disk table (your
columns named -ID seem to be redundant). Consider the following
structure (ANSI-92 Query Mode SQL DDL syntax):

Create TABLE Storage
(
StorageDesc VARCHAR(15) NOT NULL UNIQUE
)
;
Create TABLE Disc
(
DiscName VARCHAR(15) NOT NULL UNIQUE
)
;
Create TABLE DiskStore
(
DiscName VARCHAR(15) NOT NULL UNIQUE
REFERENCES Disc (DiscName)
ON DELETE CASCADE
ON UPDATE CASCADE,
StorageDesc VARCHAR(15) NOT NULL
REFERENCES Storage (StorageDesc)
ON DELETE CASCADE
ON UPDATE CASCADE
)
;
CREATE TABLE ProgramTypes
(
Programtype VARCHAR(20) NOT NULL UNIQUE
)
;
CREATE TABLE Programs
(
Programname VARCHAR(35) NOT NULL UNIQUE,
Programtype VARCHAR(15) NOT NULL
REFERENCES ProgramTypes (Programtype)
ON DELETE NO ACTION
ON UPDATE CASCADE
)
;
CREATE TABLE ProgramDisks
(
Programname VARCHAR(35) NOT NULL UNIQUE,
DiscName VARCHAR(15) NOT NULL
REFERENCES Disc (DiscName)
ON DELETE CASCADE
ON UPDATE CASCADE
)
;
CREATE VIEW Overview (
Programname, Programtype, DiscName,
StorageDesc)
AS
SELECT P1.Programname, P1.Programtype, PD1.DiscName, DS1.StorageDesc
FROM
(
Programs AS P1
INNER JOIN ProgramDisks AS PD1
ON P1.Programname = PD1.Programname
)
INNER JOIN DiskStore AS DS1
ON PD1.DiscName = DS1.DiscName
;

Test with some data (SQL DML):

INSERT INTO Disc (DiscName) VALUES ('CD1')
;
INSERT INTO Disc (DiscName) VALUES ('CD2')
;
INSERT INTO Storage (StorageDesc) VALUES ('Box1')
;
INSERT INTO Storage (StorageDesc) VALUES ('Box2')
;
INSERT INTO DiskStore (DiscName, StorageDesc)
VALUES ('CD1', 'Box1')
;
INSERT INTO DiskStore (DiscName, StorageDesc)
VALUES ('CD1', 'Box2')
; -- this insert should fail the unique constraint, as desired

INSERT INTO DiskStore (DiscName, StorageDesc)
VALUES ('CD2', 'Box1')
;
INSERT INTO DiskStore (DiscName, StorageDesc)
VALUES ('CD2', 'Box2')
;
INSERT INTO ProgramTypes (Programtype)
VALUES ('Musicprogram')
;
INSERT INTO Programs (Programname, Programtype)
VALUES ('Audiograbber', 'Musicprogram')
;
INSERT INTO Programs (Programname, Programtype)
VALUES ('Winamp', 'Musicprogram')
;
INSERT INTO ProgramDisks (Programname, DiscName)
VALUES ('Winamp', 'CD2')
;
INSERT INTO ProgramDisks (Programname, DiscName)
VALUES ('Audiograbber', 'CD2')
;
SELECT Programname, Programtype, DiscName, StorageDesc
FROM Overview
;

Jamie.

--

 

[Guest]

Hi Jamie,

Thanks for the complete and extensive reply.

I have tried to run the sql statements however it keeps failing on the
contstraints.
The parts ON DELETE CASCADE and ON UPDATE CASCADE will not be accepted.
(I do know the functions as within a Oracle DB) however Access2003 is not
accepting it.

Any idea?

Greetings,
Harry

 

[Jamie Collins]

Thanks for the complete and extensive reply.

I have tried to run the sql statements however it keeps failing on the
contstraints.
The parts ON DELETE CASCADE and ON UPDATE CASCADE will not be accepted.
(I do know the functions as within a Oracle DB) however Access2003 is not
accepting it.

As I mentioned, the syntax is for ANSI-92 Query Mode; I would guess
your mdb is in ANSI-89 Query ('Traditional') Mode. For details, see
the Help e.g.

http://office.microsoft.com/en-gb/access/HP030704831033.aspx

(I recommend you click the 'Show All' link).

Perhaps the easiest way of using ANSI-92 Query Mode is to use an ADO
connection. Here's some VBA which creates a new mdb in your Temp
folder:

Sub Diskus()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim sql As String
sql = _
"Create TABLE Storage" & vbCr & "(" & vbCr & " StorageDesc" & _
" VARCHAR(15) NOT NULL UNIQUE" & vbCr & ")" & vbCr & ";"
..Execute sql
sql = _
"Create TABLE Disc" & vbCr & "(" & vbCr & " DiscName" & _
" VARCHAR(15) NOT NULL UNIQUE" & vbCr & ")" & vbCr & ";"
..Execute sql
sql = _
"Create TABLE DiskStore" & vbCr & "(" & vbCr & " DiscName" & _
" VARCHAR(15) NOT NULL UNIQUE" & vbCr & "" & _
" REFERENCES Disc (DiscName)" & vbCr & "" & _
" ON DELETE CASCADE" & vbCr & " " & _
" ON UPDATE CASCADE," & vbCr & " StorageDesc" & _
" VARCHAR(15) NOT NULL" & vbCr & " REFERENCES" & _
" Storage (StorageDesc)" & vbCr & " ON" & _
" DELETE CASCADE" & vbCr & " ON UPDATE" & _
" CASCADE" & vbCr & ")" & vbCr & ";"
..Execute sql
sql = _
"CREATE TABLE ProgramTypes" & vbCr & "(" & vbCr & " " & _
" Programtype VARCHAR(20) NOT" & _
" NULL UNIQUE" & vbCr & ")" & vbCr & ";"
..Execute sql
sql = _
"CREATE TABLE Programs" & vbCr & "(" & vbCr & " Programname" & _
" VARCHAR(35) NOT NULL UNIQUE," & vbCr & "" & _
" Programtype VARCHAR(15) NOT" & _
" NULL" & vbCr & " REFERENCES ProgramTypes" & _
" (Programtype)" & vbCr & " ON DELETE" & _
" NO ACTION" & vbCr & " ON UPDATE CASCADE" & vbCr & ")" & vbCr &
";"
..Execute sql
sql = _
"CREATE TABLE ProgramDisks" & vbCr & "(" & vbCr & " " & _
" Programname VARCHAR(35) NOT" & _
" NULL UNIQUE," & vbCr & " DiscName VARCHAR(15)" & _
" NOT NULL" & vbCr & " REFERENCES Disc" & _
" (DiscName)" & vbCr & " ON DELETE CASCADE" & vbCr & "" & _
" ON UPDATE CASCADE" & vbCr & ")" & vbCr & ";"
..Execute sql
sql = _
"CREATE VIEW Overview (" & vbCr & " Programname," & _
" Programtype, DiscName," & vbCr & " StorageDesc)" & vbCr & "AS" &
vbCr & "SELECT" & _
" P1.Programname, P1.Programtype," & _
" PD1.DiscName, DS1.StorageDesc" & vbCr & "FROM" & vbCr & "(" & vbCr &
"" & _
" Programs AS P1" & vbCr & " INNER JOIN" & _
" ProgramDisks AS PD1" & vbCr & " ON P1.Programname" & _
" = PD1.Programname" & vbCr & ")" & vbCr & "INNER JOIN" & _
" DiskStore AS DS1" & vbCr & "ON PD1.DiscName" & _
" = DS1.DiscName" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO Disc (DiscName) VALUES" & _
" ('CD1');"
..Execute sql
sql = _
"INSERT INTO Disc (DiscName) VALUES" & _
" ('CD2');"
..Execute sql
sql = _
"INSERT INTO Storage (StorageDesc)" & _
" VALUES ('Box1');"
..Execute sql
sql = _
"INSERT INTO Storage (StorageDesc)" & _
" VALUES ('Box2');"
..Execute sql
sql = _
"INSERT INTO DiskStore (DiscName," & _
" StorageDesc)" & vbCr & " VALUES ('CD1'," & _
" 'Box1');"
..Execute sql
sql = _
"INSERT INTO DiskStore (DiscName," & _
" StorageDesc)" & vbCr & " VALUES ('CD2'," & _
" 'Box1');"
..Execute sql
sql = _
"INSERT INTO ProgramTypes (Programtype)" & vbCr & "" & _
" VALUES ('Musicprogram');"
..Execute sql
sql = _
"INSERT INTO Programs (Programname," & _
" Programtype)" & vbCr & " VALUES ('Audiograbber'," & _
" 'Musicprogram');"
..Execute sql
sql = _
"INSERT INTO Programs (Programname," & _
" Programtype)" & vbCr & " VALUES ('Winamp'," & _
" 'Musicprogram');"
..Execute sql
sql = _
"INSERT INTO ProgramDisks (Programname," & _
" DiscName)" & vbCr & " VALUES ('Winamp'," & _
" 'CD2');"
..Execute sql
sql = _
"INSERT INTO ProgramDisks (Programname," & _
" DiscName)" & vbCr & " VALUES ('Audiograbber'," & _
" 'CD2');"
..Execute sql
sql = _
"SELECT Programname, Programtype," & _
" DiscName, StorageDesc" & vbCr & "FROM Overview;"
Dim rs
Set rs = .Execute(sql)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub