delete data

Hi,

I have two tables.

CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO

CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO

(I fact i created Primary & Foreign keys after inserting data in both of these tables.)

I want to delete data from these two tables.
How do i do that...Any Ideas?Originally posted by naveen_mehta
Hi,

I have two tables.

CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO

CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO

(I fact i created Primary & Foreign keys after inserting data in both of these tables.)

I want to delete data from these two tables.
How do i do that...Any Ideas?

ALTER TABLE ONE NOCHECK CONSTRAINT FK_one_two
ALTER TABLE TWO NOCHECK CONSTRAINT FK_two_one
DELETE ONE
DELETE TWO
ALTER TABLE ONE CHECK CONSTRAINT FK_one_two
ALTER TABLE TWO CHECK CONSTRAINT FK_two_one|||That really works...Thanks a ton...|||You could similarly use the alter statements while inserting data if you do not want to check for constraints

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)

Delete any records in table 1 that are not in table 2 (tables joined on 2 columns)

I have two tables. The primary key for both tables is comprised of 2 columns.

How do I delete any records in table 1 that are not in table 2.

e.g.

if this was the data in the 2 tables

Table 1

col1 col2 col 3

0 0 0

0 1 0

Table 2

col1 col2 col3

0 0 0

I would like to delete the records containg the values 0, 1, 0 from table 1 because the is no record in table 2 with the values 0, 1 as the primary key.

Hi,

You can do this as follow (by suggesting that COL1 and COL2 are the key columns):

DELETE FROM table1
FROM table1
LEFT OUTER JOIN table2
ON table1.COL1 = table2.col1
AND table1.COL2 = table2.col2
WHERE table2.COL1 IS NULL

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Thanks a million. It seems strange to me the way the From caluse is included twice but it works!|||

the following query will do..

Delete from table1 Where Not Exists(Select 1 From table2 Where table1.Col1=Table2.Col1 and Table1.Col2=Table2.Col2)

Delete across tables

hi,

I have two tables that are related, ie I created them with;

create table cm_message (
msgid varchar(40) not null primary key,
location varchar(240),
ts timestamp default 'now' not null,
lastsent timestamp
);

create table cm_data (
pkey integer not null primary key,
subdata varchar(255),
msgid varchar(40),
foreign key (msgid) references cm_message(msgid)
);

basically for each entry in cm_message there can be several cm_data entries and they're linked using the msgid fields.

I'm trying to write a purge script that will delete entries (in cm_message and cm_data) that have a cm_message.ts timestamp older than n hours. Can I do a 'delete from ... where cm_message.ts > n' which does some kind of union between the two tables and delete entries from both tables at one stroke?

At the moment I'm looking at selecting all old entries from cm_message and deleting all in cm_data for each msgid, but there must be a more efficient way of using the relational stuff...

thanks,
nikUse the ON DELETE CASCADE option:
...foreign key (msgid) references cm_message(msgid) ON DELETE CASCADE :rolleyes:|||which database system is this? because i don't know of any that will support this --timestamp default 'now'|||thanks LKBrwn_DBA.

rudy, the database is firebird - I think it also accepts TODAY, TOMORROW and YESTERDAY which is nice and handy...

nik|||wow, ya learn sumpin new every day ;)

thanks nik|||Hi,

Does it work on a MySQL database?

//M|||Does it work on a MySQL database?the ON DELETE CASCADE? only for InnoDB tables

delete a primary key with T-SQL

Does anyone know how to delete a primary key with T-SQL?
triocchuReplied in microsoft.public.sqlserver.programming.
Please don't multi-post.
--
David Portas
--
Please reply only to the newsgroup
--
"triocchu" <triocchu@.netvigator.com> wrote in message
news:brmna2$4gq28@.imsp212.netvigator.com...
> Does anyone know how to delete a primary key with T-SQL?
> triocchu
>|||triocchu,
ALTER TABLE tablename DROP CONSTRAINT PrimaryKeyName
---
PETER WARD
WARDY Inc. - www.wardyinc.com
SQL Server Solutions
---
"triocchu" <triocchu@.netvigator.com> wrote in message
news:brmna2$4gq28@.imsp212.netvigator.com...
> Does anyone know how to delete a primary key with T-SQL?
> triocchu
>

delete a primary key with T-SQL

Does anyone know how to delete a primary key with T-SQL?

triocchuReplied in microsoft.public.sqlserver.programming.
Please don't multi-post.

--
David Portas
----
Please reply only to the newsgroup
--

"triocchu" <triocchu@.netvigator.com> wrote in message
news:brmnd9$4gd100@.imsp212.netvigator.com...
> Does anyone know how to delete a primary key with T-SQL?
> triocchu

Delete

Hey,
I got a table in which i want to delete a few rows. The
table has a primary key and withing the coloums it has
an "instanceid" which is pointed to another table
called "instance" and that table has many pk and fk. When
i try to delete a row from the 1 table mentioned, i get
the error:
Delete statement conflicted with COLUMN REFERENCE
constraint XXXX. The conflict occured in database yyyy,
table 1111, column 3333. The statement has been terminated.
Any ideas for a work arround?Essentially you are try to delete a parent record that has child records.
The FK is preventing you as it would cause orpahns.
You will need to delete all the child records before deleting the parent.
--
HTH
Ryan Waight, MCDBA, MCSE
"nefasha" <anonymous@.discussions.microsoft.com> wrote in message
news:081101c3a78e$7e9b2fc0$a601280a@.phx.gbl...
> Hey,
> I got a table in which i want to delete a few rows. The
> table has a primary key and withing the coloums it has
> an "instanceid" which is pointed to another table
> called "instance" and that table has many pk and fk. When
> i try to delete a row from the 1 table mentioned, i get
> the error:
> Delete statement conflicted with COLUMN REFERENCE
> constraint XXXX. The conflict occured in database yyyy,
> table 1111, column 3333. The statement has been terminated.
> Any ideas for a work arround?|||But it isnt a parent id!!! The table X has a column
instanceid which points to a table y where THAT table has
PK and FK with OTHER tables using the instance id... i
dont see why that affects table X?
>--Original Message--
>Essentially you are try to delete a parent record that
has child records.
>The FK is preventing you as it would cause orpahns.
>You will need to delete all the child records before
deleting the parent.
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"nefasha" <anonymous@.discussions.microsoft.com> wrote in
message
>news:081101c3a78e$7e9b2fc0$a601280a@.phx.gbl...
>> Hey,
>> I got a table in which i want to delete a few rows. The
>> table has a primary key and withing the coloums it has
>> an "instanceid" which is pointed to another table
>> called "instance" and that table has many pk and fk.
When
>> i try to delete a row from the 1 table mentioned, i get
>> the error:
>> Delete statement conflicted with COLUMN REFERENCE
>> constraint XXXX. The conflict occured in database yyyy,
>> table 1111, column 3333. The statement has been
terminated.
>> Any ideas for a work arround?
>
>.
>|||Nevermind!!!1 I figured what i was doing wrong!!! thanxs
for the help anyways!
>--Original Message--
>But it isnt a parent id!!! The table X has a column
>instanceid which points to a table y where THAT table has
>PK and FK with OTHER tables using the instance id... i
>dont see why that affects table X?
>>--Original Message--
>>Essentially you are try to delete a parent record that
>has child records.
>>The FK is preventing you as it would cause orpahns.
>>You will need to delete all the child records before
>deleting the parent.
>>--
>>HTH
>>Ryan Waight, MCDBA, MCSE
>>"nefasha" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:081101c3a78e$7e9b2fc0$a601280a@.phx.gbl...
>> Hey,
>> I got a table in which i want to delete a few rows. The
>> table has a primary key and withing the coloums it has
>> an "instanceid" which is pointed to another table
>> called "instance" and that table has many pk and fk.
>When
>> i try to delete a row from the 1 table mentioned, i get
>> the error:
>> Delete statement conflicted with COLUMN REFERENCE
>> constraint XXXX. The conflict occured in database yyyy,
>> table 1111, column 3333. The statement has been
>terminated.
>> Any ideas for a work arround?
>>
>>.
>.
>

Delay Insert

I am parsing a file where along the flow I use a conditional split. One path of the split is the primary table (with IDENTITY) values. The rest of the paths have a FOREIGN KEY to the primary table.

It seems that SSIS is trying to insert the rows at the same time (which makes sense) but this is causing a problem with the secondary tables and their FK constraint since the primary table is not yet written.

Is there a way to delay the secondary tables until the primary table is done?

(I guess one way is to run through the file twice... once for the primary table and another for the rest but that seems wasteful to me...)

Thanks.

There is no way to delay paths inside a data flow, or set any precedence. One option I like is to stage the "secondary" data in a raw file. This is very efficient compared to most source and destination combinations. In your current Data Flow write the secondary data to a raw file then add another Data Flow task, with a raw file source and the your final destination.

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

define unique keys

I have a primary key (column name is emp_id) in employee table. Also,
I would like to make a combination of other two columns is unique.
(combination of officecode field and claimno field must be unique).
how can I implement this uniquess in ms sql 2000? thank you.On 17 Oct 2007 13:41:28 -0700, TGEAR wrote:

Quote:

Originally Posted by

>I have a primary key (column name is emp_id) in employee table. Also,
>I would like to make a combination of other two columns is unique.
>(combination of officecode field and claimno field must be unique).
>how can I implement this uniquess in ms sql 2000? thank you.

Hi TGEAR,

CREATE TABLE Demo
(PrimaryKeyColumn int NOT NULL
,HalfOfOtherKeyColumn int NOT NULL
,OtherHalfOfOtherKey int NOT NULL
,SomeOhterKeyForFun int NOT NULL
,CONSTRAINT pk_Demo PRIMARY KEY (PrimaryKeyColumn)
,CONSTRAINT uq_Demo UNIQUE (HalfOfOtherKeyColumn, OtherHalfOfOtherKey)
);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||TGEAR wrote:

Quote:

Originally Posted by

I have a primary key (column name is emp_id) in employee table. Also,
I would like to make a combination of other two columns is unique.
(combination of officecode field and claimno field must be unique).
how can I implement this uniquess in ms sql 2000? thank you.

alter table employee
add constraint officecode_claimno
unique (officecode, claimno)

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 Values for a Column

I have table where I want one column (EquivNo) to default to the primary key
column (PartID) when the row is inserted. This value may be updated later,
but at the point where the row is created, I want the values to be the same.
The PartID column is an Identity column so I don't know the value in
advance.
As far as I understand it, you can't have a calculated value as the default
value for a column; can somebody confirm this?
So I have thought of two alternatives:
1) Create a trigger that takes the identity value for PartID and updates the
EquivNo column
[How do I know what the inserted PartID is within the trigger?]
2) The stored procedure inserts the row on the first pass, and uses
Scope_Identy() to update the EquivNo in an update statement.
I was just wondering about the relative merits of these two solutions. And
are there any better alternatives?
Thanks in advance
ChrisCJM
Second one seems to be good for you.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.phx.gbl...
>I have table where I want one column (EquivNo) to default to the primary
>key column (PartID) when the row is inserted. This value may be updated
>later, but at the point where the row is created, I want the values to be
>the same. The PartID column is an Identity column so I don't know the value
>in advance.
> As far as I understand it, you can't have a calculated value as the
> default value for a column; can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates
> the EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses
> Scope_Identy() to update the EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
> are there any better alternatives?
> Thanks in advance
> Chris
>|||An advantage of using 1 is that the modifications are performed in the same
transaction. The trigger
code is very straight forward:
USE tempdb
drop table t
GO
create table t(c1 int identity primary key, c2 int NULL)
GO
CREATE TRIGGER tr ON T FOR INSERT
AS
UPDATE t SET c2 = c1
WHERE EXISTS
(
SELECT *
FROM inserted AS i
WHERE i.c1 = t.c1
)
GO
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
SELECT * FROM t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.ph
x.gbl...
>I have table where I want one column (EquivNo) to default to the primary ke
y column (PartID) when
>the row is inserted. This value may be updated later, but at the point wher
e the row is created, I
>want the values to be the same. The PartID column is an Identity column so
I don't know the value
>in advance.
> As far as I understand it, you can't have a calculated value as the defaul
t value for a column;
> can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates t
he EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses Scope_
Identy() to update the
> EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
are there any better
> alternatives?
> Thanks in advance
> Chris
>|||CJM wrote:
> I have table where I want one column (EquivNo) to default to the primary k
ey
> column (PartID) when the row is inserted. This value may be updated later,
> but at the point where the row is created, I want the values to be the sam
e.
> The PartID column is an Identity column so I don't know the value in
> advance.
> As far as I understand it, you can't have a calculated value as the defaul
t
> value for a column; can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates t
he
> EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses
> Scope_Identy() to update the EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
> are there any better alternatives?
> Thanks in advance
> Chris
Is EquivNo a self-referencing foreign key? (please post DDL, then we
won't have to guess). I'm of the opinion that IDENTITY isn't a good
choice to use as a foreign key in the same table. Your problem is just
one of one of the reasons why.
If EquivNo is not a key then I'm not sure why you'd want it to be the
same as the PartID. It's a bad idea to expose IDENTITY columns to
users. From the business perspective it shouldn't matter what value is
assigned to PartID and therefore it shouldn't matter whether it's the
same as EquivNo. I think that your real problem is that you need a more
convenient alternative method to generate an incrementing key for
EquivNo. See the following article for suggestions:
http://www.sqlmag.com/Articles/Arti...8165/48165.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Tibor
> An advantage of using 1 is that the modifications are performed in the
> same transaction. The trigger code is very straight forward:
Just keep thinking if the user inserts a new row (one transaction) , the
trigger is fired and open another transaction, am I right?
If the trigger fails that an identity peroperty is already in the table , so
actually in my opinion that using
a stored procedure will be more useful in terms of performans as well as
more secure.
CREATE PROC myproc
AS
DECLARE @.idnt INT
BEGIN TRAN
INSERT INTO T1 VALUES (....)
SELECT @.idnt =SCOPE_IDENTITY()
INSERT INTO T2 SELECT @.idnt
--Error handler here
COMMIT
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23C2r4bWWGHA.5096@.TK2MSFTNGP03.phx.gbl...
> An advantage of using 1 is that the modifications are performed in the
> same transaction. The trigger code is very straight forward:
> USE tempdb
> drop table t
> GO
> create table t(c1 int identity primary key, c2 int NULL)
> GO
> CREATE TRIGGER tr ON T FOR INSERT
> AS
> UPDATE t SET c2 = c1
> WHERE EXISTS
> (
> SELECT *
> FROM inserted AS i
> WHERE i.c1 = t.c1
> )
> GO
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> SELECT * FROM t
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "CJM" <cjmnews04@.newsgroup.nospam> wrote in message
> news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.phx.gbl...
>|||> Just keep thinking if the user inserts a new row (one transaction) , the
> trigger is fired and open another transaction, am I right?
No, the code in a trigger is in the same transaction as the statement that f
ired the trigger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OxFvq8WWGHA.3800@.TK2MSFTNGP03.phx.gbl
..
> Tibor
> Just keep thinking if the user inserts a new row (one transaction) , the
> trigger is fired and open another transaction, am I right?
> If the trigger fails that an identity peroperty is already in the table ,
so
> actually in my opinion that using
> a stored procedure will be more useful in terms of performans as well as
> more secure.
>
> CREATE PROC myproc
> AS
> DECLARE @.idnt INT
> BEGIN TRAN
> INSERT INTO T1 VALUES (....)
> SELECT @.idnt =SCOPE_IDENTITY()
> INSERT INTO T2 SELECT @.idnt
> --Error handler here
> COMMIT
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23C2r4bWWGHA.5096@.TK2MSFTNGP03.phx.gbl...
>|||Surely the transaction issue is a red herring? That is, we can explicitly
define what is and isn't included in the transaction (Begin/Commit/Rollback)
anyway.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23E1lfUWWGHA.3800@.TK2MSFTNGP03.phx.gbl...
> CJM
> Second one seems to be good for you.
>
I'm erring this way, if only because it's a simple and uncomplicated
solution.|||> Surely the transaction issue is a red herring? That is, we can explicitly define what is
and isn't
> included in the transaction (Begin/Commit/Rollback) anyway.
Yes, of course we can. My point (perhaps not explicit enough) was that the t
ransaction handling will
be transparent for those who does INSERTs into the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:%23lbLUbXWGHA.924@.TK2MSFTNGP03.phx
.gbl...
> Surely the transaction issue is a red herring? That is, we can explicitly
define what is and isn't
> included in the transaction (Begin/Commit/Rollback) anyway.
>|||Did you look at the trigger code I posted? What part of that code did you fi
nd complicated? I'm not
trying to defend my proposal, I just want to make sure you see the simplicit
y of the trigger
alternative.
Oh, and I fully agree with David's point regarding IDENTITY should be comple
mented with a natural
key and identity not be exposed to users, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:u$0KGgXWGHA.1348@.TK2MSFTNGP05.phx.
gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23E1lfUWWGHA.3800@.T
K2MSFTNGP03.phx.gbl...
> I'm erring this way, if only because it's a simple and uncomplicated solut
ion.
>

Default value of a GUID field?!

I have a column named "ID" and its type is "uniqueidentifier" in SQL Server
2005 Express.
this column is the primary key and its default value is "newid()" in SQL
Server.
I have generated a DataSet in C# that has this column but its default value
is DBNull what can I do to make the default value be the return value of
this function ( or I want the Id be generated by default in the DataSet)?
System.Guid.NewGuid()
H? Leon
Have you cons?dered to create the guid from code and then insert it into the
table, in stead of letting the sql server generate it for you ?. Otherwise it
is possible to query the table for the inserted row, and reading the guid.
(if the guid isn't the primary key)
Hope this helps.
TIA
/Allan
"Leon_Amirreza" wrote:

> I have a column named "ID" and its type is "uniqueidentifier" in SQL Server
> 2005 Express.
> this column is the primary key and its default value is "newid()" in SQL
> Server.
> I have generated a DataSet in C# that has this column but its default value
> is DBNull what can I do to make the default value be the return value of
> this function ( or I want the Id be generated by default in the DataSet)?
> System.Guid.NewGuid()
>
>
|||Thanks but this gets very tricky
and sorry for the clock problem
"Allan Bentsen" <Allan.Bentsen@.discussions.microsoft.com> wrote in message
news:6FDCD5BC-DC5F-4FC9-A4E4-D23E855F651F@.microsoft.com...[vbcol=seagreen]
> H? Leon
> Have you cons?dered to create the guid from code and then insert it into
> the
> table, in stead of letting the sql server generate it for you ?. Otherwise
> it
> is possible to query the table for the inserted row, and reading the guid.
> (if the guid isn't the primary key)
> Hope this helps.
> --
> TIA
> /Allan
>
> "Leon_Amirreza" wrote:
|||becuase I am using offline Typed DataSets and the GUID field is the Primary
Key of the table
"Leon_Amirreza" <amirreza_rahmaty@.yahoo.com> wrote in message
news:uhK4HNz%23GHA.4472@.TK2MSFTNGP05.phx.gbl...
> Thanks but this gets very tricky
> and sorry for the clock problem
> "Allan Bentsen" <Allan.Bentsen@.discussions.microsoft.com> wrote in message
> news:6FDCD5BC-DC5F-4FC9-A4E4-D23E855F651F@.microsoft.com...
>
|||Ouch!! That's not a good idea.
GUID -Identity and Primary Keys
http://sqlteam.com/item.asp?ItemID=2599
GUID -Is not Always GOOD
http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx
GUID -The Cost of GUIDs as Primary Keys
http://www.informit.com/articles/article.asp?p=25862&rl=1
GUID -Uniqueidentifier vs. IDENTITY
http://sqlteam.com/item.asp?ItemID=283
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leon_Amirreza" <amirreza_rahmaty@.yahoo.com> wrote in message
news:O1BfFPz%23GHA.3352@.TK2MSFTNGP03.phx.gbl...
> becuase I am using offline Typed DataSets and the GUID field is the
> Primary Key of the table
> "Leon_Amirreza" <amirreza_rahmaty@.yahoo.com> wrote in message
> news:uhK4HNz%23GHA.4472@.TK2MSFTNGP05.phx.gbl...
>