When deleting a row with a primary key referenced by a foreign key
-
Oracle8i Has SQL commands which goes like this:
DEFERRABLE INITIALLY DEFERRED
ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
An example:
CREATE TABLE RACE_DETAIL (
RACECOURSE_NAME VARCHAR2(20) NOT NULL,
RDETAIL_DATE DATE NOT NULL,
RDETAIL_TIME DATE NOT NULL,
HORSE_NAME VARCHAR2(20) NOT NULL,
JOCKEY_ID NUMBER(6) NOT NULL,
POSITION NUMBER(2),
COMMENTS CHAR(200));
ALTER TABLE RACE_DETAIL
ADD (
CONSTRAINT PK_RDETAIL PRIMARY KEY(RACECOURSE_NAME,
RDETAIL_DATE, RDETAIL_TIME, HORSE_NAME) DEFERRABLE
INITIALLY DEFERRED);
ALTER TABLE RACE_DETAIL
ADD ( CONSTRAINT FK_HORSE_NAME FOREIGN KEY(HORSE_NAME)
REFERENCES HORSE(HORSE_NAME) ON DELETE CASCADE DEFERRABLE INITIALLY
DEFERRED);
ALTER TABLE RACE_DETAIL
ADD (
CONSTRAINT FK_JOCKEY_ID FOREIGN KEY(JOCKEY_ID)
REFERENCES JOCKEY(JOCKEY_ID)ON DELETE SET NULL DEFERRABLE INITIALLY
DEFERRED);
My question is:
What is Microsoft SQL-Server commands which are equivalent to it, and how will it be applied to the example above?
Any help is greatly appreciated.
Best Regards,
Edwar
-
CREATE TABLE [Payes] (
[PkPaye] [int] IDENTITY (1, 1) NOT NULL ,
[FkEmploye] [int] NOT NULL ,
...
CONSTRAINT [PK_Payes] PRIMARY KEY CLUSTERED
(
[PkPaye]
) ON [PRIMARY],
CONSTRAINT [FK_Payes_DatesDebutsSemaines] FOREIGN KEY
(
[FkSemaine]
) REFERENCES [DatesDebutsSemaines] (
[PkDateDebutSemaine]
) ON DELETE CASCADE
) ON [PRIMARY]
GO
this is the part that delete the child rows :
) ON DELETE CASCADE
You can also use ON UPDATE CASCADE to update the childs when the pk is changed
-
Thanks Remi,
But what should I do if I really don't want to delete the child rows... I just want to Set the Foreign Key to Null in the child table when deleting the
parent table...
E.g. you want to delete the customer from the customer table, but want to keep history about pervious sales for the customer in a child table.
Thanks for any replies!!
Edwar
-
Edwar, IF you DON'T want the data integrity porvided by the FK don't add it. This way the CHILD table won't be deleted. However, you will need to code to make sure that the relationships are maintained on INSERT and other....
Now a good question would be IF the parent is gone HOW are you going to be able to FIND the children>
Why not just have a DELETED DATE/TIME in the PARENT or some other variant to denote no longer active?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I second AJ's opinion on this... You can't have integretity and don't have it at the same time. You could use AJ's idea or you could also create an history table and insert all relevant data there before you delete both the parents and child's rows... But that could be a mess if the client becomes active again...
-
Edwar,
On SQL Server 2005 they heard your prayers and now the cascade option allows for NULL replacement. So you could be able to replace ON DELETE CASCADE with ON DELETE SET NULL in the previously posted DDL
Unfotunatly, that, is still in BETA and there is nothing equivalent on 2000. You will have to use TRIGGERS to comply with your requirements.
HTH
* Noel -
Thanks to both of you AJ And Remi for the suggestions. I will try and implement something, but I really don’t know what.
All I wanted to know is e.g. if the owner of the horse disappears or dies, then you don’t want to delete the horse (it might be work thousands of $$), and that is why I want the owner ID to be set to NULL in the horse table. E.g.
CREATE TABLE OWNER (OWNER_ID NUMBER(6) NOT NULL,
FNAME VARCHAR2(20) NOT NULL,
LNAME VARCHAR2(20)NOT NULL,
PLACING NUMBER(3) DEFAULT NULL,
ADDRESS1 VARCHAR2(40),
ADDRESS2 VARCHAR2(40),
ADDRESS3 VARCHAR2(40),
CITY VARCHAR2(20),
HOME_PHONE CHAR(20),
MOBILE_PHONE CHAR(20));
ALTER TABLE OWNER
ADD(
CONSTRAINT PK_OWNER PRIMARY KEY(OWNER_ID) DEFERRABLE
INITIALLY DEFERRED );
ALTER TABLE HORSE
ADD (
CONSTRAINT PK_HORSE_NAME PRIMARY KEY(HORSE_NAME)
DEFERRABLE INITIALLY DEFERRED);
ALTER TABLE HORSE
ADD ( CONSTRAINT FK_OWNER_ID FOREIGN KEY(OWNER_ID)
REFERENCES OWNER(OWNER_ID) ON DELETE SET NULL DEFERRABLE INITIALLY
DEFERRED);
Thanks again for all the help!
Edwar
-
Edwar,
Like I said no RI with FK will solve your problem with SQL 2000 but here is a proposition
create function dbo.CheckOwner(@id = null int)
returns int
as
begin
if @id IS NULL or Exists(select * from Owners where OwnerID = @ID)
Return 0
else
Return 1
end
Create table Horse ( HName, ...
OwnerID int null check (dbo.CheckOwner(OwnerID) = 0) -- 0 means OK
)
It may not be pretty but it does what you need until 2005 comes out
HTH
* Noel -
Thanks noeld, This clears the picture.
Best wishes to all who replied
Edwar
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply