Best practice

Hi,
What is best, do relation direct or create id (IDENTITY) column?
for exemple: (direct)
---
CREATE TABLE Test
(
TestDes CHAR(20)
CONSTRAINT Test__TestDes __pk PRIMARY KEY(TestDes)
NOT NULL
)
CREATE TABLE Test2
(
TestDes CHAR(20)
CONSTRAINT Test2_TestDes__fk FOREIGN KEY(TestDes)
References Test(TestDes )
ON UPDATE CASCADE
ON DELETE CASCADE,
NOT NULL
)
for exemple: (id)
---
CREATE TABLE Test
(
id_num int IDENTITY(1,1),
CONSTRAINT Test__id_num __pk PRIMARY KEY(id_num),
TestDes CHAR(20)
NOT NULL
)
CREATE TABLE Test2
(
TestDes CHAR(20)
NOT NULL,
id_num int
CONSTRAINT Test2_num __fk FOREIGN KEY(num )
References Test(num )
ON UPDATE CASCADE
ON DELETE CASCADE,
NOT NULL
)ReTF
It's hard to suggest soemthing because I don't know your business
requirements.
An IDENITY property is the best candidate for artificial keys.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:eXF%23nF2lFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> What is best, do relation direct or create id (IDENTITY) column?
> for exemple: (direct)
> ---
> CREATE TABLE Test
> (
> TestDes CHAR(20)
> CONSTRAINT Test__TestDes __pk PRIMARY KEY(TestDes)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> CONSTRAINT Test2_TestDes__fk FOREIGN KEY(TestDes)
> References Test(TestDes )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
> for exemple: (id)
> ---
> CREATE TABLE Test
> (
> id_num int IDENTITY(1,1),
> CONSTRAINT Test__id_num __pk PRIMARY KEY(id_num),
> TestDes CHAR(20)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> NOT NULL,
> id_num int
> CONSTRAINT Test2_num __fk FOREIGN KEY(num )
> References Test(num )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
>|||Are you asking if it is better to enforce referential integrity using a
natural key or a surrogate key? It depends on your specific cirsumstance. If
the natural key is subject to change (for example LastName, EMail, or
ZipCode) then definately use a surrogate key. You don't want to propogate
updates throughout your system every time someone changes their name or
invoice number coding scheme, especially if these foreign keys have been
migrated to external databases like a data warehouse. Also, there are cases
where the natural key is so wide (for example a multi varchar column key)
that it would be a waste of disk storage and memory to attempt to use it in
foreign key relationships. If you do choose to use a surrogate key (such as
an identity column), then you still need to have a unique constraint on the
natural key. However, be forwarned that using a surrogate key for
referential integrity will tick off some relational database theorists.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:eXF%23nF2lFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> What is best, do relation direct or create id (IDENTITY) column?
> for exemple: (direct)
> ---
> CREATE TABLE Test
> (
> TestDes CHAR(20)
> CONSTRAINT Test__TestDes __pk PRIMARY KEY(TestDes)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> CONSTRAINT Test2_TestDes__fk FOREIGN KEY(TestDes)
> References Test(TestDes )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
> for exemple: (id)
> ---
> CREATE TABLE Test
> (
> id_num int IDENTITY(1,1),
> CONSTRAINT Test__id_num __pk PRIMARY KEY(id_num),
> TestDes CHAR(20)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> NOT NULL,
> id_num int
> CONSTRAINT Test2_num __fk FOREIGN KEY(num )
> References Test(num )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
>|||First of all, cascading updates are not possible when using IDENTITY,
because you can't change an identity value.
If your database engine uses locking to isolate transactions, then you
should definitely use surrogate keys.
If you have a limited budget for application development, then you should
definitely use surrogate keys.
Cascading updates can cause deadlocks and reduce concurrency.
Collisions occur more often when using optimistic concurrency without
surrogate keys.
An application that selects from two or more tables without surrogate
keys must issue all of the individual
selects within the same transaction or must include additional logic
to detect and handle key value changes.
Every application that updates a table without a surrogate key must
include additional logic to detect and
handle key value changes.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:eXF#nF2lFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> What is best, do relation direct or create id (IDENTITY) column?
> for exemple: (direct)
> ---
> CREATE TABLE Test
> (
> TestDes CHAR(20)
> CONSTRAINT Test__TestDes __pk PRIMARY KEY(TestDes)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> CONSTRAINT Test2_TestDes__fk FOREIGN KEY(TestDes)
> References Test(TestDes )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
> for exemple: (id)
> ---
> CREATE TABLE Test
> (
> id_num int IDENTITY(1,1),
> CONSTRAINT Test__id_num __pk PRIMARY KEY(id_num),
> TestDes CHAR(20)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> NOT NULL,
> id_num int
> CONSTRAINT Test2_num __fk FOREIGN KEY(num )
> References Test(num )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
>|||Thanks for clarification.
One more question:
What I should use instead CASCADE?
Trigger?
thanks
"Brian Selzer" <brian@.selzer-software.com> escreveu na mensagem
news:%230qTUK3lFHA.3380@.TK2MSFTNGP10.phx.gbl...
> First of all, cascading updates are not possible when using IDENTITY,
> because you can't change an identity value.
> If your database engine uses locking to isolate transactions, then you
> should definitely use surrogate keys.
> If you have a limited budget for application development, then you should
> definitely use surrogate keys.
> Cascading updates can cause deadlocks and reduce concurrency.
> Collisions occur more often when using optimistic concurrency without
> surrogate keys.
> An application that selects from two or more tables without surrogate
> keys must issue all of the individual
> selects within the same transaction or must include additional
> logic
> to detect and handle key value changes.
> Every application that updates a table without a surrogate key must
> include additional logic to detect and
> handle key value changes.
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:eXF#nF2lFHA.1372@.TK2MSFTNGP10.phx.gbl...
>|||Since you can't change an IDENTITY, there is no need for ON UPDATE CASCADE.
I prefer to disallow cascading deletes as well, because they can also cause
deadlocks. In my opinion, the best way to cascade deletes in SQL Server
2000 is in an INSTEAD OF trigger. The reason is that you have control over
the order in which locks are obtained by either issuing select statements
WITH(UPDLOCK), or by specifying the individual delete statements in the
correct order.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:O2Rrop3lFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Thanks for clarification.
> One more question:
> What I should use instead CASCADE?
> Trigger?
> thanks
> "Brian Selzer" <brian@.selzer-software.com> escreveu na mensagem
> news:%230qTUK3lFHA.3380@.TK2MSFTNGP10.phx.gbl...
should
>|||Hi,
If you have time.
Can you show me how to use ' INSTEAD OF trigger' in my sample? Thanks
IF EXISTS(SELECT NAME
FROM sysobjects
WHERE NAME = N'TPARENT'
AND type = 'U')
DROP TABLE TPARENT
GO
CREATE TABLE TPARENT
(
CONSTRAINT pk_tpid
PRIMARY KEY(tpid),
tpid int IDENTITY(1,1),
TPARENT CHAR(30)
NOT NULL
)
go
IF EXISTS(SELECT NAME
FROM sysobjects
WHERE NAME = N'CLILD'
AND type = 'U')
DROP TABLE CLILD
GO
CREATE TABLE CLILD
(
CONSTRAINT fk_tpid
FOREIGN KEY(tpid )
References TPARENT (tpid ),
tpid int,
TPARENT CHAR(30)
NOT NULL
)
go
INSERT INTO TPARENT VALUES ('Test 01')
INSERT INTO TPARENT VALUES ('Test 02')
INSERT INTO TPARENT VALUES ('Test 03')
insert into CLILD VALUES (1, 'Test 01')
insert into CLILD VALUES (2, 'Test 02')
insert into CLILD VALUES (3, 'Test 03')
SELECT * FROM TPARENT
SELECT * FROM CLILD
DELETE TPARENT
DELETE CLILD
UPDATE TPARENT SET tpid = 4 WHERE tpid = 1
UPDATE CLILD SET tpid = 4 WHERE tpid = 1
UPDATE TPARENT SET TPARENT = '' WHERE tpid = 1
UPDATE CLILD SET TPARENT = '' WHERE tpid = 1
"Brian Selzer" <brian@.selzer-software.com> escreveu na mensagem
news:OTI0d53lFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Since you can't change an IDENTITY, there is no need for ON UPDATE
> CASCADE.
> I prefer to disallow cascading deletes as well, because they can also
> cause
> deadlocks. In my opinion, the best way to cascade deletes in SQL Server
> 2000 is in an INSTEAD OF trigger. The reason is that you have control
> over
> the order in which locks are obtained by either issuing select statements
> WITH(UPDLOCK), or by specifying the individual delete statements in the
> correct order.
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:O2Rrop3lFHA.1444@.TK2MSFTNGP10.phx.gbl...
> should
>|||You should always use a relational key. IDENTITY is completely
non-relational and proprietary. The rules are to first look for an
industry standard key. If that fails, then for a natural key. If both
those fail, then very carefully design a key tha tyou can validate and
verify yourself. Get a copy of SQL PROGRAMMING STYLE for details.|||First of all, your example doesn't make any sense. It appears as if you are
trying to create a parent-child relationship, but there isn't a primary key
in the child table, and it appears that the natural key from the parent
table is duplicated in the child.
Here's a simple example of how to do cascade deletes within an instead of
trigger.
CREATE TABLE SalesOrder
(
SalesOrderKey INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_SalesOrder
PRIMARY KEY CLUSTERED,
SalesOrderNumber CHAR(12) NOT NULL CONSTRAINT AK_SalesOrder UNIQUE
NONCLUSTERED,
CustomerKey INT NOT NULL -- REFERENCES Customer(CustomerKey)
-- more columns here
)
CREATE TABLE SalesOrderDetail
(
SalesOrderDetailKey INT IDENTITY(1, 1) NOT NULL CONSTRAINT
PK_SalesOrderDetail PRIMARY KEY CLUSTERED,
SalesOrderKey INT NOT NULL REFERENCES SalesOrder (SalesOrderKey),
SalesOrderLineNo INT NOT NULL,
CONSTRAINT AK_SalesOrderDetail UNIQUE NONCLUSTERED (SalesOrderKey,
SalesOrderLineNo)
-- more columns here
)
GO
CREATE TRIGGER tIOD_SalesOrder ON SalesOrder INSTEAD OF DELETE AS
BEGIN
DECLARE @.X INT
SELECT @.X = SalesOrder.SalesOrderKey
FROM SalesOrder WITH(UPDLOCK)
JOIN deleted
ON (deleted.SalesOrderKey = SalesOrder.SalesOrderKey)
DELETE SalesOrderDetail FROM deleted WHERE
SalesOrderDetail.SalesOrderKey = deleted.SalesOrderKey
DELETE SalesOrder FROM deleted WHERE SalesOrder.SalesOrderKey =
deleted.SalesOrderKey
END
GO
Note how you can control the order in which the SalesOrder and
SalesOrderDetail tables can be locked.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:#TMuRs4lFHA.3780@.tk2msftngp13.phx.gbl...
> Hi,
> If you have time.
> Can you show me how to use ' INSTEAD OF trigger' in my sample? Thanks
>
> IF EXISTS(SELECT NAME
> FROM sysobjects
> WHERE NAME = N'TPARENT'
> AND type = 'U')
> DROP TABLE TPARENT
> GO
> CREATE TABLE TPARENT
> (
> CONSTRAINT pk_tpid
> PRIMARY KEY(tpid),
> tpid int IDENTITY(1,1),
> TPARENT CHAR(30)
> NOT NULL
> )
> go
> IF EXISTS(SELECT NAME
> FROM sysobjects
> WHERE NAME = N'CLILD'
> AND type = 'U')
> DROP TABLE CLILD
> GO
> CREATE TABLE CLILD
> (
> CONSTRAINT fk_tpid
> FOREIGN KEY(tpid )
> References TPARENT (tpid ),
> tpid int,
> TPARENT CHAR(30)
> NOT NULL
> )
> go
>
> INSERT INTO TPARENT VALUES ('Test 01')
> INSERT INTO TPARENT VALUES ('Test 02')
> INSERT INTO TPARENT VALUES ('Test 03')
> insert into CLILD VALUES (1, 'Test 01')
> insert into CLILD VALUES (2, 'Test 02')
> insert into CLILD VALUES (3, 'Test 03')
> SELECT * FROM TPARENT
> SELECT * FROM CLILD
> DELETE TPARENT
> DELETE CLILD
> UPDATE TPARENT SET tpid = 4 WHERE tpid = 1
> UPDATE CLILD SET tpid = 4 WHERE tpid = 1
> UPDATE TPARENT SET TPARENT = '' WHERE tpid = 1
> UPDATE CLILD SET TPARENT = '' WHERE tpid = 1
> "Brian Selzer" <brian@.selzer-software.com> escreveu na mensagem
> news:OTI0d53lFHA.3144@.TK2MSFTNGP12.phx.gbl...
statements
you
must
>|||ReTF,
During initial database design, you should follow Joe Celko's advice and try
to find an industry standard key, and with very few exceptions, every table
should have a natural key. At some point, however, the relational database
engine must be chosen, and if it employs locking to isolate transactions,
then you must transform the database to use surrogate keys, or as close to
them as is possible and practical (IDENTITY). Relational database purists
like Joe tend to ignore the significant costs associated with using natural
keys--that is, the additional development, testing, troubleshooting, and
maintenance that is required for every application program; the
consequential performance and concurrency degradation; and the loss of
flexibility inherent in a database that does not use surrogate keys. (I
think its from spending too much time in the world of Academia.)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123009314.481833.229810@.f14g2000cwb.googlegroups.com...
> You should always use a relational key. IDENTITY is completely
> non-relational and proprietary. The rules are to first look for an
> industry standard key. If that fails, then for a natural key. If both
> those fail, then very carefully design a key tha tyou can validate and
> verify yourself. Get a copy of SQL PROGRAMMING STYLE for details.
>