help - Delete Relationship
-
Hi there, Well I am starting to wade into the world of SQL however, I am stuck with a problem. At the moment I have two tables, product table and supplier table. In the product table I have a foreign key called supplierID which is the primary key of the supplier table. Using MS Access I have created a relationship between these two tables graphically, i.e. one to many relationship. My problem is that I am trying to delete the supplierID column from the product table using queries.My problem is that I am not being allowed to delete the column supplierID from the product table due to an error: "Cannont delete field 'supplierID'. It is part of one or more relationships". My deletion query: ALTER TABLE product DROP COLUMN supplierID My question is how do i delete the existing relationship between the two tables I had created graphically ? Any help will be highly appreciated. thanks EDIT: I am interested in finding out how to delete the relationship programmatically :)
-
Hi there, Well I am starting to wade into the world of SQL however, I am stuck with a problem. At the moment I have two tables, product table and supplier table. In the product table I have a foreign key called supplierID which is the primary key of the supplier table. Using MS Access I have created a relationship between these two tables graphically, i.e. one to many relationship. My problem is that I am trying to delete the supplierID column from the product table using queries.My problem is that I am not being allowed to delete the column supplierID from the product table due to an error: "Cannont delete field 'supplierID'. It is part of one or more relationships". My deletion query: ALTER TABLE product DROP COLUMN supplierID My question is how do i delete the existing relationship between the two tables I had created graphically ? Any help will be highly appreciated. thanks EDIT: I am interested in finding out how to delete the relationship programmatically :)
Try ALTER TABLE tblname DROP CONSTRAINT constraintname
topcoderjax - Remember, Google is your friend.
-
Try ALTER TABLE tblname DROP CONSTRAINT constraintname
topcoderjax - Remember, Google is your friend.
thanks for your reply. After looking at your response I realised I made Homer mistake in writing up initial post, doh!!!. The query you stated I already know. What I meant to ask was how do you determine the name of the relationship between two tables programmatically?
-
Hi there, Well I am starting to wade into the world of SQL however, I am stuck with a problem. At the moment I have two tables, product table and supplier table. In the product table I have a foreign key called supplierID which is the primary key of the supplier table. Using MS Access I have created a relationship between these two tables graphically, i.e. one to many relationship. My problem is that I am trying to delete the supplierID column from the product table using queries.My problem is that I am not being allowed to delete the column supplierID from the product table due to an error: "Cannont delete field 'supplierID'. It is part of one or more relationships". My deletion query: ALTER TABLE product DROP COLUMN supplierID My question is how do i delete the existing relationship between the two tables I had created graphically ? Any help will be highly appreciated. thanks EDIT: I am interested in finding out how to delete the relationship programmatically :)
Hallo You can't delete the column because as you said it is part of a foreign key constraint. In MS SQL 2K you can drop this column programmatically by first dropping the foreign key constraint. Access, however, does not appear to give the relationship constraint a name and therefore you can't drop it. As far as I can tell your best option is to use MS SQL 2K instead.
There are 10 types of people in the world, those who understand binary and those who dont.
-
Hallo You can't delete the column because as you said it is part of a foreign key constraint. In MS SQL 2K you can drop this column programmatically by first dropping the foreign key constraint. Access, however, does not appear to give the relationship constraint a name and therefore you can't drop it. As far as I can tell your best option is to use MS SQL 2K instead.
There are 10 types of people in the world, those who understand binary and those who dont.
well I managed to workout a dirty hack that does the job. To remove the foreign key constraint from the table we need to determine the name of the relationship this is achieved by viewing the msysrelationship table. If you are in MS Access and enable the ability to view hidden objects and tables you will be able to view the msysrelationship table. The msysrelationship table maintains relationship names as nameoftable1nameoftable2 i.e. if one table was product and the other was supplier then then relationship will productsupplier. As long as you can access the msysrelationship table you are pretty much set. Then delete the relationship then drop the column easy!!!