Droping constraint

S

Shnizles

Hi , all
thank you for your time in advance ,
im trying to delete a constraint using sql query ,
i found that this should be the right syntax for that task:
ALTER TABLE Table2 DROP CONSTRAINT Relation1

the problem is i made the constraints using the access 2007 designer ,
and i got no idea what is the constraint index / name ("Relation1" in the
query) ,
i couldnt find it anywhere ,
please tell me how can i find the name of my tables relations , so i could
delete them if needed with that query above thanks.
 
S

Shnizles

hey ,
thank you for the reply ,
but i was looking for a simple sql query syntax not code or anything
like the query i mentioned in the first post ,
do you know anything about it?
 
S

Shnizles

Thanks for the help ,
im able to view all my db constraints now ,
i get a table looks like this:

ccolumn grbit icolumn szColumn szObject szReferencedColumn szReferencedObject szRelationship
1 4352 0 agentId AgentsConstraints agentNum Agent AgentAgentsConstraint
1 4352 0 agentId AgentsWeeklyConstraints agentNum Agent AgentAgentsWeeklyConstraints
1 4096 0 hId HolidayDates id Holiday HolidayHolidayDate
1 4352 0 acId AgentsWeeklyConstraints id ShiftsScheduling ShiftsSchedulingAgentsWeeklyConstraints
1 4352 0 teamNumber Agent teamNumber Team TeamAgent
1 4096 0 calendarId Holiday id Calendar CalendarHoliday


now i have tried to execute the following sql querys:
ALTER TABLE Holiday DROP CONSTRAINT CalendarHoliday
ALTER TABLE Calendar DROP CONSTRAINT CalendarHoliday


both cases ,i get a messege saying:
"object invalid or no longer set" which is not true i can still see the
relationship that im trying to drop.

whats wrong with my query?
 
S

Shnizles

Im using vb.net to excute the query ,
i get this error with OLEDB and ADODB connectors ,
they throw same error,
any other guess what it could be?

Josef Poetzl said:
hi,
ALTER TABLE Holiday
ADD CONSTRAINT
CalendarHoliday FOREIGN KEY ([calendarid]) REFERENCES Calendar ([id]) ON
DELETE CASCADE

and i get syntax error although i followed the exact syntax as suggested in
the ms access guides , how ever when i remove the " ON DELETE CASCADE"
part the relationship is being added ok ,

whats wrong in the query how can i enable cascade delete to my relationship

This is a problem with DAO.

dim strSQL as string
strSQL = "ALTER TABLE Holiday ...."

DAO:
currentdb.execute strSQL, dbfailonerror
=> error

ADODB:
currentproject.connection.execute strSQL
=> ok


kind regards,
Josef
 

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

Top