|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to check existing data with a relationship in code. How to drop a relationship in code.I have two tables called Student1 and Class. Both have Class column to set a relationship. Here is the code and it works. ALTER TABLE Student1 ADD CONSTRAINT FK_Student1_class FOREIGN KEY (Class) REFERENCES Class(Class) Now I want to make it to check existing data with this relationship. How do I modify it? The next questing is how to drop the relationship in code. I tried this code. ALTER TABLE Student1 drop CONSTRAINT FK_Student1_class But there is an error saying FK_Student1_class is not a constriant. Thanks a lot. When you add the constraint, SQL Server does the data validation for
you. If there were any FK violations, the constraint would have failed at creation time. For example: Your drop appears to be correct. Possibly, you are specifying the incorrect name. When I run your code it works fine. To check the cosntraint name, run sp_helpconstraint on the table. "§Chrissi§" <anubisofthyde***@hotmail.com> wrote in message I have two tables called Student1news:%23tZmiDlVFHA.3704@TK2MSFTNGP14.phx.gbl... Hi, and Class. Both have Class column to set a relationship. Here is the code and it works. ALTER TABLE Student1 ADD CONSTRAINT FK_Student1_class FOREIGN KEY (Class) REFERENCES Class(Class) Now I want to make it to check existing data with this relationship. How do I modify it? The next questing is how to drop the relationship in code. I tried this code. ALTER TABLE Student1 drop CONSTRAINT FK_Student1_class But there is an error saying FK_Student1_class is not a constriant. Thanks a lot. Thanks. The DROP works now. I don't know why it did not work before.
After I add the constraint, why is the box "Check existing data on creation" is still unchecked? Show quote "David Gugick" <davidg-nospam@imceda.com> wrote in message news:eWu5CnlVFHA.3716@TK2MSFTNGP12.phx.gbl... > When you add the constraint, SQL Server does the data validation for you. > If there were any FK violations, the constraint would have failed at > creation time. For example: > > Your drop appears to be correct. Possibly, you are specifying the > incorrect name. When I run your code it works fine. To check the > cosntraint name, run sp_helpconstraint on the table. > > > -- > David Gugick > Imceda Software > www.imceda.com > > > "§Chrissi§" <anubisofthyde***@hotmail.com> wrote in message > news:%23tZmiDlVFHA.3704@TK2MSFTNGP14.phx.gbl... > Hi, > > I have two tables called Student1 > and Class. Both have Class column to set a relationship. Here is the > code and it works. > > ALTER TABLE Student1 > ADD CONSTRAINT FK_Student1_class FOREIGN KEY > (Class) > REFERENCES Class(Class) > > Now I want to make it to check existing data with this relationship. How > do I modify it? > > The next questing is how to drop the relationship in code. I tried this > code. > > ALTER TABLE Student1 > drop CONSTRAINT FK_Student1_class > > But there is an error saying FK_Student1_class is not a constriant. > > Thanks a lot. §Chrissi§ wrote:
> Thanks. The DROP works now. I don't know why it did not work before. In order to avoid having SQL Server validate existing data when > > After I add the constraint, why is the box "Check existing data on > creation" is still unchecked? declaring the foreign key constraint, you need to use the "WITH NOCHECK" option. It is not recommended, however. Leaving that option off or specifying "WITH CHECK" causes SQL Server to validate the data. Apparently, SQL Enterprise Manager is not respecting the value of the CHECK/NOCHECK. OTOH, it may just be that since the the flag only affects data validation at create time and has no effect on checking data subsequently, the value of the check/nocheck is not saved in the system tables. In that case, SQL EM is just keeping whatever value you set last time, which is more in line with data modeling as opposed to reverse engineering a database. SQL EM is a rather lacking tool when it comes to database modeling. I would suggest you use a more robust tool for such tasks.  
Other interesting topics
|
|||||||||||||||||||||||
