May 18, 2003 at 11:47 pm
[font=Tahoma]As you all know, we can create mutually dependent foreign key constraints with CREATE SCHEMA AUTHORIZATION like below.
CREATE SCHEMA AUTHORIZATION ross
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT REFERENCES t2(c1))
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT REFERENCES t1(c1))
Any idea how to insert records or drop these tables. Hopefully, it is impossible. But just want to know.
[/font=Verdana]
mcp mcse mcsd mcdba
mcp mcse mcsd mcdba
May 19, 2003 at 4:25 am
Please do not crosspost. However additional responses in http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=12299&FORUM_ID=65&CAT_ID=1&Topic_Title=Mutually+dependent+foreign+constraints&Forum_Title=Performance+Tuning
as for what the other post did the point is if someone has enough rights they can disable the constraint with the ALTER TABLE and NOCHECK statement. But by default you would be unable.
May 19, 2003 at 8:58 pm
Ok. let's say we created tables like below. Is it possible to do insertion.
font=Verdana]CREATE SCHEMA AUTHORIZATION dbo
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT REFERENCES t2(c1) NOT NULL)
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT REFERENCES t1(c1) NOT NULL)[/font=Verdana]
mcp mcse mcsd mcdba
May 20, 2003 at 3:41 am
First off the code won't run as is since Table t1 requires that table t2 exists and vice versa. So you have to create the table then alter the table and add the reference. Once done then you cannot insert into either table a value that does not exist in the other unless you run ALTER TABLE with NOCHECK to disable the reference. Then you can make changes to the table that was performed on without regard to values in the other table. Afterwards use ALTER TABLE with CHECK to reenable the reference and you can no longer insert data into the table that does not exist in the other. But now ou can add to the other table as long as you are using values that exist in the table you did the inserts to. But to answer your question, if tables built with interdependent relationships are built and they are empty with the setup you show the answer is by default barring other abilities you cannot. If data does exist then it you can only insert if the value for c2 is exists in the referenced table in c1.
See the other referenced post for ALTER TABLE example.
As for dropping you can do as long as you remove the reference first. Again using ALTER TABLE to drop the reference or using EM (see ALTER TABLE in SQL BOL).
Edited by - antares686 on 05/20/2003 03:42:06 AM
May 20, 2003 at 4:25 am
This code can be run without any problem because I have used CREATE SCHEMA AUTHORIZATION dbo. So, I do not need to create and alter tables.
My Question is by using same way, Can’t I do the insertion without using NOCHECK.
mcp mcse mcsd mcdba
May 20, 2003 at 4:31 am
Guess I should have looked that up myself first (ooops). Anyway the answer is still no.
I just tested it myself and got the following
"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__t1__c2__477199F1'. The conflict occurred in database 'tempx', table 't2', column 'c1'.
The statement has been terminated.
"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply