delete constraint

how can i implement delete constraint? i mean i don't want the rows of the primary key table to be deleted if they are used as foreign key in some other table. so i want to check if that PK is used as foreign key in other tables before deleting.INSTEAD OF DELETE triggers|||so u mean using triggers instead of delete cascade? i can check if that record exists in other tables using triggers and take necessary action but i want to return relevant message to user if it couldn't be deleted. how can i do that?|||Have a look at RAISERROR|||i was able to manage the following code but couldn't get the message displayed when user tries to delete the record. if i run the stored proc from query analyzer, i get the following error message but not in my application. i have tried catching any exceptions using ex.Message but can't get the error. so how can i get the error displayed when user violates the delete constraint? i have used ExecuteNonQuery command.

IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR ('Cannot delete this record. Make sure that this record is not used in other tables',9,1)
RETURN
END

|||Try ON DELETE {NO ACTION and ON UPDATE {NO ACTION, this will not allow Deletes and Updates. Hope this helps.

Kind regards,
Gift Peddie|||but i want the message to be returned if it couldn't be deleted so that relevant message can be displayed to the user.|||CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON UPDATE NO ACTION,
qty_ordered int)
GO

Run a search in the BOL(books online) for Cascade Delete, the following code is from the BOL it means deletes or updates will fail with an error message. Hope this helps.

Kind regards,
Gift Peddie|||but how can we be sure about the source of error this way? with raiserror as i have done, i can get the message if there are other errors as well. else i don't get any error message. isn't that suupposed to return error message?|||The error from NO ACTION is ANSI SQL from DRI(Declarative Referential Integrity) rules but if you prefer Raise Error you can use it run a search for raise error in the BOL(books online). Hope this helps.

Kind regards,
Gift Peddie|||

I do it this way, for now. Probably better way would be to make Delete function that returns an error message rather then raising new exception. Maybe even make a some kind of class that interprets these error numbers and returns standard error message.

In SqlDataProvider for module

PublicOverridesSub DeleteClientDepartmentsItem(ByVal itemIDAsInteger)

Try

SqlHelper.ExecuteNonQuery(_connectionString, _databaseOwner & _objectQualifier & _

"esr_ClientDepartments_Delete", itemID)

Catch exAs SqlException

If ex.Number = 547Then

ThrowNew Exception("This record cannot be deleted due to its association with other records.")

EndIf

EndTry

EndSub

'in the page:

Try

Dim cdcAsNew ClientDepartmentsController

Dim departmentIdAsInteger =CType(dgDepartments.DataKeys(e.Item.ItemIndex),Integer)

cdc.Delete(departmentId)

BinddgDepartments()

Catch exAs Exception

DotNetNuke.UI.Skins.Skin.AddModuleMessage(Me, ex.Message, Skins.Controls.ModuleMessage.ModuleMessageType.YellowWarning)

EndTry

Delete Cluster Index With PK and FK Constraints

I have a SQL Server 2000 database that I would like to delete the Cluster
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)
Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)
|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)

Delete Cluster Index With PK and FK Constraints

I have a SQL Server 2000 database that I would like to delete the Cluster
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
--
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)

Delete Cluster Index With PK and FK Constraints

I have a SQL Server 2000 database that I would like to delete the Cluster
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)

Defining Foreign keys in Management Studio

Hi,
I am using SQL Server 2005.
I have created the tables for my Database through the Management Studio
front end tool.
I can apply a primary key to the tables easily enough. What I am having
trouble with is assigning multiple columns as primary keys and also how to
define foreign keys between tables.
Does anyone have suggesstions how I can achieve this through management
studio?
Thanks In Advance
MaccaXref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:433758
On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:

>Hi,
>I am using SQL Server 2005.
>I have created the tables for my Database through the Management Studio
>front end tool.
>I can apply a primary key to the tables easily enough. What I am having
>trouble with is assigning multiple columns as primary keys
Hi Macca,
Easiest: open a new query window, type
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable -- Or any other name
PRIMARY KEY (Col1, Col2, Col3)
Then, click the Execute button.
But if you prefer to use point and cllick, just hold down the
Ctrl-button on your keyboard while selecting key columns, than
right-click and choose "Set Primary Key".

> and also how to
>define foreign keys between tables.
Again, the easiest is to just type and execute the SQL command:
ALTER TABLE ReferingTable
ADD CONSTRAINT LogicalNameGoesHere
FOREIGN KEY (Col1, Col2, Col3)
REFERENCES ReferedTable (Col1, Col2, Col3)
Optionally, add an ON UPDATE and/or ON DELETE clause.
Using point and click: rightclick refering table and choose "Modify".
Click menu-item "Table Designer" / "Relationships". Click "Add". Under
"(General)", find the entry for "Tables and Columns Specification",
click in the emppty field next to it, then click the smalll ellipsis
button. Enter a name for the foreign key constraint. Then, on the right
hand side, use the drop down lists to select the column(s) that form the
relationship. Move to the left-hand side, choose the refered table and
choose the columns that are refered to. Click "OK" to save.
Regardless of whether you use SQL or point and click to set the
relationship, the column(s) in the refered table MUST be set as either a
PRIMARY KEY or a UNIQUE constraint.
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks for the reply. I have a question with the SQl that creates a foreign
key.
If I have Table1 and Table 2 and Table 1 has a foreign key which is the
Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQ
L
query?
Thanks
Macca
"Hugo Kornelis" wrote:

> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>
> Hi Macca,
> Easiest: open a new query window, type
> ALTER TABLE MyTable
> ADD CONSTRAINT PK_MyTable -- Or any other name
> PRIMARY KEY (Col1, Col2, Col3)
> Then, click the Execute button.
> But if you prefer to use point and cllick, just hold down the
> Ctrl-button on your keyboard while selecting key columns, than
> right-click and choose "Set Primary Key".
>
> Again, the easiest is to just type and execute the SQL command:
> ALTER TABLE ReferingTable
> ADD CONSTRAINT LogicalNameGoesHere
> FOREIGN KEY (Col1, Col2, Col3)
> REFERENCES ReferedTable (Col1, Col2, Col3)
> Optionally, add an ON UPDATE and/or ON DELETE clause.
> Using point and click: rightclick refering table and choose "Modify".
> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
> "(General)", find the entry for "Tables and Columns Specification",
> click in the emppty field next to it, then click the smalll ellipsis
> button. Enter a name for the foreign key constraint. Then, on the right
> hand side, use the drop down lists to select the column(s) that form the
> relationship. Move to the left-hand side, choose the refered table and
> choose the columns that are refered to. Click "OK" to save.
> Regardless of whether you use SQL or point and click to set the
> relationship, the column(s) in the refered table MUST be set as either a
> PRIMARY KEY or a UNIQUE constraint.
> --
> Hugo Kornelis, SQL Server MVP
>|||> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your
SQL
> query?
It is not the query that describes which table is the referencing or the ref
erenced table. It is the
data model. In the data model you describe, Table2 is the referenced table a
nd Table 1 is the
referencing table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Macca" <Macca@.discussions.microsoft.com> wrote in message
news:F44BB119-F24C-4146-91CB-0586510C1589@.microsoft.com...[vbcol=seagreen]
> Hi Hugo,
> Thanks for the reply. I have a question with the SQl that creates a foreig
n
> key.
> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your
SQL
> query?
> Thanks
> Macca
> "Hugo Kornelis" wrote:
>

Defining Foreign keys in Management Studio

Hi,
I am using SQL Server 2005.
I have created the tables for my Database through the Management Studio
front end tool.
I can apply a primary key to the tables easily enough. What I am having
trouble with is assigning multiple columns as primary keys and also how to
define foreign keys between tables.
Does anyone have suggesstions how I can achieve this through management
studio?
Thanks In Advance
MaccaOn Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>Hi,
>I am using SQL Server 2005.
>I have created the tables for my Database through the Management Studio
>front end tool.
>I can apply a primary key to the tables easily enough. What I am having
>trouble with is assigning multiple columns as primary keys
Hi Macca,
Easiest: open a new query window, type
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable -- Or any other name
PRIMARY KEY (Col1, Col2, Col3)
Then, click the Execute button.
But if you prefer to use point and cllick, just hold down the
Ctrl-button on your keyboard while selecting key columns, than
right-click and choose "Set Primary Key".
> and also how to
>define foreign keys between tables.
Again, the easiest is to just type and execute the SQL command:
ALTER TABLE ReferingTable
ADD CONSTRAINT LogicalNameGoesHere
FOREIGN KEY (Col1, Col2, Col3)
REFERENCES ReferedTable (Col1, Col2, Col3)
Optionally, add an ON UPDATE and/or ON DELETE clause.
Using point and click: rightclick refering table and choose "Modify".
Click menu-item "Table Designer" / "Relationships". Click "Add". Under
"(General)", find the entry for "Tables and Columns Specification",
click in the emppty field next to it, then click the smalll ellipsis
button. Enter a name for the foreign key constraint. Then, on the right
hand side, use the drop down lists to select the column(s) that form the
relationship. Move to the left-hand side, choose the refered table and
choose the columns that are refered to. Click "OK" to save.
Regardless of whether you use SQL or point and click to set the
relationship, the column(s) in the refered table MUST be set as either a
PRIMARY KEY or a UNIQUE constraint.
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks for the reply. I have a question with the SQl that creates a foreign
key.
If I have Table1 and Table 2 and Table 1 has a foreign key which is the
Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
query?
Thanks
Macca
"Hugo Kornelis" wrote:
> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
> >Hi,
> >
> >I am using SQL Server 2005.
> >
> >I have created the tables for my Database through the Management Studio
> >front end tool.
> >
> >I can apply a primary key to the tables easily enough. What I am having
> >trouble with is assigning multiple columns as primary keys
> Hi Macca,
> Easiest: open a new query window, type
> ALTER TABLE MyTable
> ADD CONSTRAINT PK_MyTable -- Or any other name
> PRIMARY KEY (Col1, Col2, Col3)
> Then, click the Execute button.
> But if you prefer to use point and cllick, just hold down the
> Ctrl-button on your keyboard while selecting key columns, than
> right-click and choose "Set Primary Key".
> > and also how to
> >define foreign keys between tables.
> Again, the easiest is to just type and execute the SQL command:
> ALTER TABLE ReferingTable
> ADD CONSTRAINT LogicalNameGoesHere
> FOREIGN KEY (Col1, Col2, Col3)
> REFERENCES ReferedTable (Col1, Col2, Col3)
> Optionally, add an ON UPDATE and/or ON DELETE clause.
> Using point and click: rightclick refering table and choose "Modify".
> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
> "(General)", find the entry for "Tables and Columns Specification",
> click in the emppty field next to it, then click the smalll ellipsis
> button. Enter a name for the foreign key constraint. Then, on the right
> hand side, use the drop down lists to select the column(s) that form the
> relationship. Move to the left-hand side, choose the refered table and
> choose the columns that are refered to. Click "OK" to save.
> Regardless of whether you use SQL or point and click to set the
> relationship, the column(s) in the refered table MUST be set as either a
> PRIMARY KEY or a UNIQUE constraint.
> --
> Hugo Kornelis, SQL Server MVP
>|||> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
> query?
It is not the query that describes which table is the referencing or the referenced table. It is the
data model. In the data model you describe, Table2 is the referenced table and Table 1 is the
referencing table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Macca" <Macca@.discussions.microsoft.com> wrote in message
news:F44BB119-F24C-4146-91CB-0586510C1589@.microsoft.com...
> Hi Hugo,
> Thanks for the reply. I have a question with the SQl that creates a foreign
> key.
> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
> query?
> Thanks
> Macca
> "Hugo Kornelis" wrote:
>> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>> >Hi,
>> >
>> >I am using SQL Server 2005.
>> >
>> >I have created the tables for my Database through the Management Studio
>> >front end tool.
>> >
>> >I can apply a primary key to the tables easily enough. What I am having
>> >trouble with is assigning multiple columns as primary keys
>> Hi Macca,
>> Easiest: open a new query window, type
>> ALTER TABLE MyTable
>> ADD CONSTRAINT PK_MyTable -- Or any other name
>> PRIMARY KEY (Col1, Col2, Col3)
>> Then, click the Execute button.
>> But if you prefer to use point and cllick, just hold down the
>> Ctrl-button on your keyboard while selecting key columns, than
>> right-click and choose "Set Primary Key".
>> > and also how to
>> >define foreign keys between tables.
>> Again, the easiest is to just type and execute the SQL command:
>> ALTER TABLE ReferingTable
>> ADD CONSTRAINT LogicalNameGoesHere
>> FOREIGN KEY (Col1, Col2, Col3)
>> REFERENCES ReferedTable (Col1, Col2, Col3)
>> Optionally, add an ON UPDATE and/or ON DELETE clause.
>> Using point and click: rightclick refering table and choose "Modify".
>> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
>> "(General)", find the entry for "Tables and Columns Specification",
>> click in the emppty field next to it, then click the smalll ellipsis
>> button. Enter a name for the foreign key constraint. Then, on the right
>> hand side, use the drop down lists to select the column(s) that form the
>> relationship. Move to the left-hand side, choose the refered table and
>> choose the columns that are refered to. Click "OK" to save.
>> Regardless of whether you use SQL or point and click to set the
>> relationship, the column(s) in the refered table MUST be set as either a
>> PRIMARY KEY or a UNIQUE constraint.
>> --
>> Hugo Kornelis, SQL Server MVP

Defining a "partial" foreign Key..how could I ?

I have a table A, which has 3 fields:

- code, subcode and description (code and subcode are PK)

and a table B which has 2 fields:

- date, code, value (date and code are PK)

So far, no problem. It comes up when I try to define code in table B as foreign key pointing to code in table A, because table A has code and subcode as its PK, not only code...but, I need only code to be in table B. How could I overcome this?

Well this is kind of design issue...

You can't do it directly.. Your table design is wrong.

Try to use the following design:

CodeMaster :: code, date, value , codedescription
Here,
Code is Primary Key

SubCodeMaster :: code, subcode, subcodedescription
Here,
Code references CodeMaster::Code
Code, Subcode is Primary Key

|||

Well, unless the code column is unique in an of itself (thus making it the PK), you can't (and you shouldn't). If it is, you could apply a unique constraint to the code column, but that would be silly.

In all likelihood, you actually need a table for a code itself that relates to both tables. This would be the proper solution, since a code is a different thing than a subcode, and any code info would have to be repeate on each row

You can enforce this with triggers too, but I wouldn't suggest that as the "best" case.

Define more than one relationship per table?

Why is it not possible to define more than one relationship per table?

i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?

thank you::Why is it not possible to define more than one relationship per table?

This is possible. You are in error here. read the error message.

::i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2
::separate relationships.

THIS is not possible. You can only have one cascade.

::why can't i do this

Wrong location for this question. Ask the developers.

::and what are my alternatives?

Use a trigger.|||thank you for your response,

in my attempts to use a trigger, i can't seem to get around the error of a subquery returning more than one result. how can you implement a cascading delete with this restriction and/or how do your work around it?

thank you|||Triggers.

And get used to set based operation.

::i can't seem to get around the error of a subquery returning more than one result

What subquery?|||'subquery' refers to the DELETE query in my trigger.

more specifically, i have a DELETE trigger in a primary table. in this DELETE trigger, i have a DELETE query that deletes records in a foreign 'many' table. it is this DELETE query that is the 'subquery' in the error message as listed below;

error message
===============
Server: Msg 512, Level 16, State 1, Procedure triggerMyPrimaryTableDelete, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
===============

trigger code
===============
CREATE TRIGGER triggerMyPrimaryTableDelete
ON dbo.MyPrimaryTable FOR DELETE
AS
BEGIN
DELETE MyForeignManyTable
FROM MyForeignManyTable , deleted
WHERE MyForeignManyTable.ID = deleted.ID
END
===============

thank you

Define FK over DB boundaries

Hi,
is it possible to assign a foreign key from DB1 to a primary key from DB2?
Thanks
Christian
Nope, it's not supported. You'd have to do it with triggers.
HTH. Ryan
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:0FB2E334-A203-4BAF-BDA1-71A4462E8841@.microsoft.com...
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian
|||No, you have to implemt that with triggers.
BTW: Do not cross(-language) post.
HTH, jens Suessmeyer.
|||Hi
You could add a check constraint that calls a function, but this is likely
to be very slow.
John
"Christian Havel" wrote:

> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian
|||> You could add a check constraint that calls a function, but this is likely
> to be very slow.
Careful with this - real DRI works in two directions. This would only work
in one direction and only for inserts and updates.
|||Scott
Both the trigger solution and the check constraint require changes to both
databases if you want to cover everything.
John
"Scott Morris" wrote:

> Careful with this - real DRI works in two directions. This would only work
> in one direction and only for inserts and updates.
>
>
|||> Both the trigger solution and the check constraint require changes to both
> databases if you want to cover everything.
Nothing I wrote disagreed with this. But, as I mentioned, a check
constraint is not checked during deletion. A set of triggers, when properly
designed and written, can enforce the required relationship. As far as I
know, check constraints cannot wholely support the requirement.

Define FK over DB boundaries

Hi,
is it possible to assign a foreign key from DB1 to a primary key from DB2?
Thanks
ChristianNope, it's not supported. You'd have to do it with triggers.
HTH. Ryan
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:0FB2E334-A203-4BAF-BDA1-71A4462E8841@.microsoft.com...
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||No, you have to implemt that with triggers.
BTW: Do not cross(-language) post.
HTH, jens Suessmeyer.|||Hi
You could add a check constraint that calls a function, but this is likely
to be very slow.
John
"Christian Havel" wrote:

> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||> You could add a check constraint that calls a function, but this is likely
> to be very slow.
Careful with this - real DRI works in two directions. This would only work
in one direction and only for inserts and updates.|||Scott
Both the trigger solution and the check constraint require changes to both
databases if you want to cover everything.
John
"Scott Morris" wrote:

> Careful with this - real DRI works in two directions. This would only wor
k
> in one direction and only for inserts and updates.
>
>|||> Both the trigger solution and the check constraint require changes to both
> databases if you want to cover everything.
Nothing I wrote disagreed with this. But, as I mentioned, a check
constraint is not checked during deletion. A set of triggers, when properly
designed and written, can enforce the required relationship. As far as I
know, check constraints cannot wholely support the requirement.

Define FK over DB boundaries

Hi,
is it possible to assign a foreign key from DB1 to a primary key from DB2?
Thanks
ChristianNope, it's not supported. You'd have to do it with triggers.
--
HTH. Ryan
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:0FB2E334-A203-4BAF-BDA1-71A4462E8841@.microsoft.com...
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||No, you have to implemt that with triggers.
BTW: Do not cross(-language) post.
HTH, jens Suessmeyer.|||Hi
You could add a check constraint that calls a function, but this is likely
to be very slow.
John
"Christian Havel" wrote:
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||> You could add a check constraint that calls a function, but this is likely
> to be very slow.
Careful with this - real DRI works in two directions. This would only work
in one direction and only for inserts and updates.|||Scott
Both the trigger solution and the check constraint require changes to both
databases if you want to cover everything.
John
"Scott Morris" wrote:
> > You could add a check constraint that calls a function, but this is likely
> > to be very slow.
> Careful with this - real DRI works in two directions. This would only work
> in one direction and only for inserts and updates.
>
>|||> Both the trigger solution and the check constraint require changes to both
> databases if you want to cover everything.
Nothing I wrote disagreed with this. But, as I mentioned, a check
constraint is not checked during deletion. A set of triggers, when properly
designed and written, can enforce the required relationship. As far as I
know, check constraints cannot wholely support the requirement.

default value of the foreign key field in a child table

There are two tables, parent and child table.

Foreign key field of the child table is not a required field. If the
data is submitted w/o a value into the foreign key field, what should
I make it as a default value? Should it be "null"?reneeccwest@.hotmail.com (reneeccwest) wrote in message news:<9f9d6d21.0310081243.7e44194d@.posting.google.com>...
> There are two tables, parent and child table.
> Foreign key field of the child table is not a required field. If the
> data is submitted w/o a value into the foreign key field, what should
> I make it as a default value? Should it be "null"?

If the column is NULLable, then NULL should be acceptable; if you need
to enforce a default value, that suggests you should probably have a
NOT NULL column with a DEFAULT constraint. So it depends on your data
model and business rules.

Simon|||Hi,

it MUST be NULL, otherwise you get a constraint violation; you can
define a default value but it have to be inserted on the master table.

Bye.

reneeccwest@.hotmail.com (reneeccwest) wrote in message news:<9f9d6d21.0310081243.7e44194d@.posting.google.com>...
> There are two tables, parent and child table.
> Foreign key field of the child table is not a required field. If the
> data is submitted w/o a value into the foreign key field, what should
> I make it as a default value? Should it be "null"?