JET Referential Integrity superior than SQL
shripa...@gmail.com
example table I had:
User Table
User Name field
============
john
wilfred
thomas
Table2
======
User1 - User2 - User3
Basically I needed update/delete cascade relationships from:
UserTable.Username -> Table2.User1
UserTable.Username -> Table2.User2
UserTable.Username -> Table2.User3
This is possible in Access, but SQL Server chokes and gives me an
error:
Msg 1785, Level 16, State 1, Server IS_VOL\IIS_VOL, Line 1
Introducing FOREIGN KEY constraint 'FK__table2__user2__21B6055D' on
table 'table
2' may cause cycles or multiple cascade paths. Specify ON DELETE NO
ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 1, Server IS_VOL\IIS_VOL, Line 1
Could not create constraint. See previous errors.
This is totally stupid. This does not cause a cycle or something. I am
very disappointed with SQL Server. Can we force these kind of multiple
DRI Constraints ? This is possible in DB2 and Oracle. Even self
referencing cascades are possible in Access/DB2/Oracle but not in SQL
Server. I also require self referencing cascades (Table A col1 - Table
A col2) for my app.
I know I can use triggers but that's like tedious. I have over 100
tables with a huge amount of relationships. Is there any SQL Client
out there that can just setup DRI triggers automatically (point and
click) ?
Please help. Many thanks.
Regards,
Shripal.
Uri Dimant
If it does not help please post table's strucure+ sample data
CREATE TABLE users --
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO GF VALUES (1,'A')
INSERT INTO GF VALUES (2,'B')
INSERT INTO GF VALUES (3,'C')
CREATE TABLE User1
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES users ([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
CREATE TABLE User2
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES users ([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
..........
<shripa...@gmail.com> wrote in message
news:1d3ce94d-a097-42a1...@r24g2000vbp.googlegroups.com...
shripa...@gmail.com
>
> - Show quoted text -
Hey,
thanks for the reply. actually the actual problem is:
CREATE TABLE USERS
(
ID INT PRIMARY KEY INDENTITY,
USERNAME VARCHAR(255) NOT NULL UNIQUE
)
CREATE TABLE TABLE2
(
ID INT PRIMARY KEY IDENTITY,
USER1 VARCHAR(255) FOREIGN KEY REFERENCES USERS(USERNAME) ON DELETE
CASCADE ON UPDATE CASCADE,
USER2 VARCHAR(255) FOREIGN KEY REFERENCES USERS(USERNAME) ON DELETE
CASCADE ON UPDATE CASCADE,
USER3 VARCHAR(255) FOREIGN KEY REFERENCES USERS(USERNAME) ON DELETE
CASCADE ON UPDATE CASCADE
)
GO
AND IT GIVES:
Msg 1785, Level 16, State 1, Server IS_VOL\IIS_VOL, Line 1
Introducing FOREIGN KEY constraint 'FK__table2__user2__21B6055D' on
table 'table
2' may cause cycles or multiple cascade paths. Specify ON DELETE NO
ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 1, Server IS_VOL\IIS_VOL, Line 1
Could not create constraint. See previous errors.
THIS IS CRAZY IT WORKS ON ACCESS! IT WORKS IN DB2! IT WORKS IN ORACLE!
WHY NOT SQL SERVER ? HOW THE HELL AM I SUPPOSED TO MAKE TRIGGERS FOR
OVER 100 TABLES ??????? IS MICROSOFT NUTS ?
Uri Dimant
Yes it won't work,your option is to remove ON DELETE CASCADE clause and use
trigger
<shripa...@gmail.com> wrote in message
news:cb9d6cf9-b143-4838...@v13g2000yqm.googlegroups.com...
shripa...@gmail.com
Can someone help me write a stored procedure to create triggers for
enforcing Referential Integrity ?
Something like this ?
EXEC sp_CreateRelationship
@prmtbl = Orders,
@sectbl = OrderDetails,
@prmcol = orderid,
@seccol = orderid,
@deletecascade = 1,
@updatecascade = 1
EXEC sp_CreateRelationship
@prmtbl = Employees,
@sectbl = Employees,
@prmcol = empid,
@seccol = mgrid,
@deletecascade = 1,
@updatecascade = 1
Best regards,
Shripal.
--CELKO--
This is one of the many reasons that SQL insiders call SQL Server a
“lesser SQL”; it also screws up unary math operators, UNIQUE
constraints with NULLs, etc. The reasons have to do with the Sybase
history and an underlying UNIX style file model.
I have consulted on 17 different SQL products over the decades, so I
will assume that the question is rhetorical. I am not going to post
that stuff in a newsgroup without being paid for the book it would
involve.
>> How the hell am I supposed to make TRIGGERs for Over 100 tables? <<
Text editor macros. Or move to a better product; DB2 would be my
choice since it is a much better SQL in virtually every way.
>> Is Microsoft nuts? <<
Never attribute to malice that which can be explained by stupidity. Or
Greed.
Alex Kuznetsov
Regardless of your problems with cascade, your table might not be
normalized properly. Very likely this design violates 2NF.
steve dassin
news:cb9d6cf9-b143-4838...@v13g2000yqm.googlegroups.com...
>.
>THIS IS CRAZY IT WORKS ON ACCESS! IT WORKS IN DB2! IT WORKS IN ORACLE!
>WHY NOT SQL SERVER ? HOW THE HELL AM I SUPPOSED TO MAKE TRIGGERS FOR
>OVER 100 TABLES ??????? IS MICROSOFT NUTS ?
MS isn't nuts. It's users who put up with this who are nuts:) Sql server is
an immature system for mature developers who want to model data
relationally. You should study the Dataphor product. Throw out the entire
DDL of sql server and replace it with dataphor. Use sql server to store your
data but develop in dataphor. This combination will maximize your
development effort as well as leveraging the best of what each product as to
offer.
Multiple cascade paths to the same table (your example)
http://beyondsql.blogspot.com/2009/01/multiple-cascade-paths-to-same-table.html
Dataphor is open source, get a copy here:
http://databaseconsultinggroup.com/downloads/
mat
@s1g2000prg.googlegroups.com>, jcel...@earthlink.net says...
> >> This is crazy it works on ACCESS! It works in DB2! It works in Oracle! Why not SQL Server? <<
>
> This is one of the many reasons that SQL insiders call SQL Server a
> constraints with NULLs, etc. The reasons have to do with the Sybase
> history and an underlying UNIX style file model.
>
> I have consulted on 17 different SQL products over the decades, so I
> will assume that the question is rhetorical. I am not going to post
> that stuff in a newsgroup without being paid for the book it would
> involve.
>
> >> How the hell am I supposed to make TRIGGERs for Over 100 tables? <<
>
> Text editor macros. Or move to a better product; DB2 would be my
> choice since it is a much better SQL in virtually every way.
>
> >> Is Microsoft nuts? <<
>
> Never attribute to malice that which can be explained by stupidity. Or
> Greed.
>
mat
steve_...@rac4sql.net says...
project. It's website is defunct, the devs blog seems to indicate it's
not a high priority to them; I could not find much on the product,
period.
--CELKO--
Not that I know about; I have not consulted for them for a lot of
years, and if I was I would be under an NDA. Currently, they have a
very simple cycle detection algorithm which makes it easy to disallow
certain DDL. It looks at the tables but not the columns within the
tables.
The classic situation is a set of three tables, A, B and C. A change
in A cascades to B and cascades to C. But a change in B also cascades
C. What happens if C.x is changed by both A and B actions to
different values?
One answer is an early SQL engine was that the last change was used;
this was non-deterministic. A good answer is to report a fault and
rollback. A better answer is to give a warning at compile time. The
safe answer is to now allow this at all.
These algorithms are really hard and require some changes to the
indexing methods.
steve dassin
"mat" <m...@notarealdotcom.adr> wrote in message
news:MPG.23eb82a6d...@msnews.microsoft.com...
No, dataphor is not dead:) Use the link to download it and dive in. There
are several of us who would be more than willing to assist you. My blog is
full of introductory examples (all with sql server) and explanations. Their
site was hacked to death so they're rebuilding it. And, if you read their
blog, you know their working on coming out with a new project. Give it a
shot and the big light in your head may go on. Within every sql programmer
there's a relational one trying to break out:)