dropping a relationship (i.e. constraint?)

D

David Mueller

I'm trying to drop a relationship using SQL DDL but I get a "CHECK constraint
.... does not exist" message.

ALTER TABLE mytable DROP CONSTRAINT [relation-name-goes-here]

I can't seem to indentify the name of the constraint(relationship) I want to
drop. I checked the MSysRelationships table and I see a registry-key-like
name in there (szRelationship field). That same value is spit out when I
loop through using ... debug.print currentdb.relationship(x).name .... so I'm
thinking that would be the right name to use in the SQL statement.

I tried w/wo braces, single/double quotes, ... I even put in a dirty word
but I can't seem to drop that constraint.

The bigger picture ...
What I really need to do is modify the length of the field, but the field is
part of the relationship. So, I'm trying to drop the relationship, alter the
column, and then I'll put the relationship back. I need to use SQL DDL
because this update will be applied to many MDB files via a VBA routine.

1. I type SQL DDL into a version control table.
2. VBA code executes the SQL DDL against databases that haven't recieved
the update.

Thanks,
David
 
A

Allen Browne

The code below should help you identify the names of each relationship in
your database, the tables, fields, and a description of its attributes.

Public Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable,
RelationAttributes(rel.Attributes)
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function
Private Function RelationAttributes(lngAttrib As Long) As String
Dim strOut As String
Dim lngLen As Long
Const dbRelationCascadeNull As Long = &H2000

If (lngAttrib And dbRelationUnique) <> 0& Then
strOut = strOut & "unique, "
End If
If (lngAttrib And dbRelationDontEnforce) <> 0& Then
strOut = strOut & "unenforced, "
End If
If (lngAttrib And dbRelationInherited) <> 0& Then
strOut = strOut & "inherited, "
End If
If (lngAttrib And dbRelationUpdateCascade) <> 0& Then
strOut = strOut & "cascade update, "
End If
If (lngAttrib And dbRelationDeleteCascade) <> 0& Then
strOut = strOut & "cascade delete, "
End If
If (lngAttrib And dbRelationCascadeNull) <> 0& Then
strOut = strOut & "cascade to null, "
End If
If (lngAttrib And dbRelationLeft) <> 0& Then
strOut = strOut & "left join, "
End If
If (lngAttrib And dbRelationRight) <> 0& Then
strOut = strOut & "right join, "
End If
lngLen = Len(strOut) - 2& 'Without trailing comma and space.
If lngLen > 0& Then
RelationAttributes = Left$(strOut, lngLen)
End If
End Function
Function HasDeleteCascade(strTable As String) As Boolean
Dim db As DAO.Database
Dim rel As DAO.Relation

Set db = CurrentDb()
For Each rel In db.Relations
If rel.Table = strTable Then
If (rel.Attributes And dbRelationDeleteCascade) > 0 Then
Debug.Print rel.Name, rel.ForeignTable
HasDeleteCascade = True
Exit For
End If
End If
Next

Set rel = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
David Mueller said:
I'm trying to drop a relationship using SQL DDL but I get a "CHECK
constraint
... does not exist" message.

ALTER TABLE mytable DROP CONSTRAINT [relation-name-goes-here]

I can't seem to indentify the name of the constraint(relationship) I want
to
drop. I checked the MSysRelationships table and I see a registry-key-like
name in there (szRelationship field). That same value is spit out when I
loop through using ... debug.print currentdb.relationship(x).name .... so
I'm
thinking that would be the right name to use in the SQL statement.

I tried w/wo braces, single/double quotes, ... I even put in a dirty word
but I can't seem to drop that constraint.

The bigger picture ...
What I really need to do is modify the length of the field, but the field
is
part of the relationship. So, I'm trying to drop the relationship, alter
the
column, and then I'll put the relationship back. I need to use SQL DDL
because this update will be applied to many MDB files via a VBA routine.

1. I type SQL DDL into a version control table.
2. VBA code executes the SQL DDL against databases that haven't recieved
the update.

Thanks,
David
Click to expand...
 
C

Chris O'C via AccessMonster.com

David said:
I'm trying to drop a relationship using SQL DDL but I get a "CHECK constraint
... does not exist" message.

ALTER TABLE mytable DROP CONSTRAINT [relation-name-goes-here]

I can't seem to indentify the name of the constraint(relationship) I want to
drop. I checked the MSysRelationships table and I see a registry-key-like
name in there (szRelationship field). That same value is spit out when I
loop through using ... debug.print currentdb.relationship(x).name .... so I'm
thinking that would be the right name to use in the SQL statement.
Click to expand...

You're using the correct syntax and you appear to be using the correct
foreign key name in the szRelationship column, but make sure the table name
matches what's in the szObject column.

ALTER TABLE <szObject name>
DROP CONSTRAINT <szRelationship name>;
 
A

Allen Browne

Once you know the relationship's name, you can delete it with:
CurrentDb.Relations.Delete "WhateverItsCalled"

There's an example of how to delete all relations in your database here:
http://allenbrowne.com/DelRel.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

David Mueller said:
Thanks, Allen. Great code! I'm still not able to drop a constraint, tho.

Tried This :: alter table [LEO_18_PARTICIPATION] drop constraint
"{44BC5CFE-B4AD-4D2F-943E-42C76EDAAB50}"

Tried This too :: alter table [LEO_18_PARTICIPATION] drop constraint
"[I:\Leo\204108.mdb].{44BC5CFE-B4AD-4D2F-943E-42C76EDAAB50}"

As Chris O'C suggested, I made sure the table name matched the szobject.
I'm still getting "CHECK constraint .... does not exist"

I've tried a bunch of variations in the SQL, I did compact/repair, I can
delete/re-create the relationship using the GUI, and I tried the DDL SQL
on
other relationships with the same result.

Thanks again.
David

Allen Browne said:
The code below should help you identify the names of each relationship in
your database, the tables, fields, and a description of its attributes.

Public Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable,
RelationAttributes(rel.Attributes)
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function
Private Function RelationAttributes(lngAttrib As Long) As String
Dim strOut As String
Dim lngLen As Long
Const dbRelationCascadeNull As Long = &H2000

If (lngAttrib And dbRelationUnique) <> 0& Then
strOut = strOut & "unique, "
End If
If (lngAttrib And dbRelationDontEnforce) <> 0& Then
strOut = strOut & "unenforced, "
End If
If (lngAttrib And dbRelationInherited) <> 0& Then
strOut = strOut & "inherited, "
End If
If (lngAttrib And dbRelationUpdateCascade) <> 0& Then
strOut = strOut & "cascade update, "
End If
If (lngAttrib And dbRelationDeleteCascade) <> 0& Then
strOut = strOut & "cascade delete, "
End If
If (lngAttrib And dbRelationCascadeNull) <> 0& Then
strOut = strOut & "cascade to null, "
End If
If (lngAttrib And dbRelationLeft) <> 0& Then
strOut = strOut & "left join, "
End If
If (lngAttrib And dbRelationRight) <> 0& Then
strOut = strOut & "right join, "
End If
lngLen = Len(strOut) - 2& 'Without trailing comma and space.
If lngLen > 0& Then
RelationAttributes = Left$(strOut, lngLen)
End If
End Function
Function HasDeleteCascade(strTable As String) As Boolean
Dim db As DAO.Database
Dim rel As DAO.Relation

Set db = CurrentDb()
For Each rel In db.Relations
If rel.Table = strTable Then
If (rel.Attributes And dbRelationDeleteCascade) > 0 Then
Debug.Print rel.Name, rel.ForeignTable
HasDeleteCascade = True
Exit For
End If
End If
Next

Set rel = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
David Mueller said:
I'm trying to drop a relationship using SQL DDL but I get a "CHECK
constraint
... does not exist" message.

ALTER TABLE mytable DROP CONSTRAINT [relation-name-goes-here]

I can't seem to indentify the name of the constraint(relationship) I
want
to
drop. I checked the MSysRelationships table and I see a
registry-key-like
name in there (szRelationship field). That same value is spit out when
I
loop through using ... debug.print currentdb.relationship(x).name ....
so
I'm
thinking that would be the right name to use in the SQL statement.

I tried w/wo braces, single/double quotes, ... I even put in a dirty
word
but I can't seem to drop that constraint.

The bigger picture ...
What I really need to do is modify the length of the field, but the
field
is
part of the relationship. So, I'm trying to drop the relationship,
alter
the
column, and then I'll put the relationship back. I need to use SQL DDL
because this update will be applied to many MDB files via a VBA
routine.

1. I type SQL DDL into a version control table.
2. VBA code executes the SQL DDL against databases that haven't
recieved
the update.

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
C

Chris O'C via AccessMonster.com

Tried This :: alter table [LEO_18_PARTICIPATION] drop constraint
"{44BC5CFE-B4AD-4D2F-943E-42C76EDAAB50}"
Click to expand...

Yikes! You used a GUID to name your constraint? You're braver than I am!
See this article on why you may want to avoid GUID's:

http://www.trigeminal.com/usenet/usenet011.asp?1033

As an alternative, why not use Jet's naming convention for foreign key
constraints? ParentTableNameChildTableName.
 
A

Allen Browne

Chris, I doubt the OP generated the GUID intentionally.

As you said, JET uses Table1Table2 type names for the index if the name is
available. If it's not available, JET uses a GUID. The GUIDs are unsightly,
but don't really have the data-matching issues michka refers to when
discussing fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Chris O'C via AccessMonster.com said:
Tried This :: alter table [LEO_18_PARTICIPATION] drop constraint
"{44BC5CFE-B4AD-4D2F-943E-42C76EDAAB50}"
Click to expand...

Yikes! You used a GUID to name your constraint? You're braver than I am!
See this article on why you may want to avoid GUID's:

http://www.trigeminal.com/usenet/usenet011.asp?1033

As an alternative, why not use Jet's naming convention for foreign key
constraints? ParentTableNameChildTableName.
Click to expand...
 
C

Chris O'C via AccessMonster.com

Allen said:
As you said, JET uses Table1Table2 type names for the index if the name is
available. If it's not available, JET uses a GUID.
Click to expand...

I can't test this out in earlier versions, but I'll show you what I'm seeing
in Access 2003. If you create the foreign key constraint using SQL and don't
name this constraint, the resulting constraint name is a different pattern
from what you'd get if you generated a GUID (Replication ID) and put that
into the query. For example:

CREATE TABLE tblParent
(
RName Text (40) NOT NULL,
Team Text (40) NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (RName)
);

CREATE TABLE tblChild
(
ID Long NOT NULL,
Region Text (40) NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (ID),
FOREIGN KEY (Region) REFERENCES tblParent (RName)
);

The resulting constraint name in MSysRelationships is Rel_390B6EE5_5801_4C13.
Notice there's no quotes or braces. If I drop the table tblChild and
recreate it and name the constraint this time:

CREATE TABLE tblChild
(
ID Long NOT NULL,
Region Text (40) NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (ID),
CONSTRAINT "{44BC5CFE-B4AD-4D2F-943E-42C76EDAAB50}"
FOREIGN KEY (Region) REFERENCES tblParent (RName)
);

The resulting constraint name in MSysRelationships is "{44BC5CFE-B4AD-4D2F-
943E-42C76EDAAB50}". Notice the quotation marks and the braces. I have to
use quotation marks and braces in my SQL to get it to work without a syntax
error, and this name appears in MSysRelationships *exactly* as I've typed it
in the SQL pane. The pattern is very different from Rel_390B6EE5_5801_4C13.
If it's not available, JET uses a GUID. The GUIDs are unsightly,
but don't really have the data-matching issues michka refers to when
discussing fields.
Click to expand...

He mentioned that in different versions of Access, comparing GUID's changes.
He didn't list "{44BC5CFE-B4AD-4D2F-943E-42C76EDAAB50}" as one of the valid
syntaxes, which makes me think David is issuing his drop statement in an
earlier version. Maybe {GUID{44BC5CFE-B4AD-4D2F-943E-42C76EDAAB50}} would
work.
 
A

Allen Browne

Okay, so they may have been something in that SQL statement that JET was not
able to execute through DAO.

There are some other side effects of enabling the ANSI 92 format, such as
using % as the wildcard with the Like operator, instead of *.

An alternative might have been to try executing the DDL SQL statement under
ADO in code, e.g.:
strSql = "ALTER ...
CurrentProject.Connection.Execute strSql
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Droping constraint 8
Using DDL to Create Constraint - Syntax Error 4
Display Relationship Name so I can drop it 4
Add constraint returns syntax error on constrain clause 7
Dynamic Naming of Form Button 3
Drop primary key without the Constraint name 2
Deleting a foreign key relationship in SQL 2
Display Relationship Names How 1

Top