December 20, 2010 at 10:31 pm
Hi,
I need to know how to resolve a problem, take the following example.
There're 3 tables to update; table1, table2, table3
FIELDS ------------------------------------
table1: CatId(FK), threadid(PK)
table2: CatId, threadid, lang, cult,
table3: CatId, threadid, lang, cult, post
(Table1 have a FK CatdId that points to a table called categories ..)
I add a FK from table2 (CatId, threadid) to point to table1 (CatId, threadid)
(sql adds same FK to table1)
I add a IX to table2 (threadid, lang, cult) as unique
I add a FK from table3 (CatId, threadid, lang, cult) to point to table2 (CatId, threadid, lang, cult)
(sql adds same FK to table2)
-------------------------------------------
Up to here OK
Now if run the following query it GIVES ME ERRORS
==================================================
DECLARE @CategoryId Int
SET @CategoryId = 1 --or whatever
UPDATE dbo.[table3] SET CatId = @CategoryId WHERE ThreadId = @ThreadId
UPDATE dbo.[table2] SET CatId = @CategoryId WHERE ThreadId = @ThreadId
UPDATE dbo.[table1] SET CatId = @CategoryId WHERE ThreadId = @ThreadId
==================================================
Mens 547, Nivel 16, Estado 0, Procedimiento Forums.Moderation.ChangeThreadCategory, Línea 36
Instrucción UPDATE CONFLICTS WITH RESTRICTION FOREIGN KEY "FK_Forums.Categories.Threads.Languages.Posts_Forums.Categories.Threads.Languages". CONFLICT IN table2
Se terminó la instrucción.
Mens 547, Nivel 16, Estado 0, Procedimiento Forums.Moderation.ChangeThreadCategory, Línea 41
Instrucción UPDATE CONFLICTS WITH RESTRICTION REFERENCE "FK_Forums.Categories.Threads.Languages.Posts_Forums.Categories.Threads.Languages". CONFLICT IN table3
Se terminó la instrucción.
Mens 547, Nivel 16, Estado 0, Procedimiento Forums.Moderation.ChangeThreadCategory, Línea 45
Instrucción UPDATE CONFLICTS WITH RESTRICTION REFERENCE "FK_Forums.Categories.Threads.Languages_Forums.Categories.Threads". CONFLICT IN table2
Se terminó la instrucción.
WHAT CAN I DO ?
(without set UPDATE as CASCADE for that FKs)
ANY IDEAs ¿?
December 21, 2010 at 12:38 am
This was removed by the editor as SPAM
December 21, 2010 at 1:01 am
Please post some DDL data
December 21, 2010 at 10:40 am
Hello, I don't want to disable FK
Here' the DDL
TABLE1=====================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Forums.Categories.Threads](
[DomainId] [int] NOT NULL,
[ForumId] [int] NOT NULL,
[CategoryId] [int] NOT NULL,
[CreatorId] [int] NULL,
[ThreadId] [int] IDENTITY(1,1) NOT NULL,
[Deleted] [datetime] NULL,
CONSTRAINT [PK_Forums.Categories.Threads] PRIMARY KEY CLUSTERED
(
[ThreadId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Forums.Categories.Threads] UNIQUE NONCLUSTERED
(
[DomainId] ASC,
[ForumId] ASC,
[CategoryId] ASC,
[ThreadId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums.Categories.Threads] WITH CHECK ADD CONSTRAINT [FK_Forums.Categories.Threads_Domains.Users] FOREIGN KEY([DomainId], [CreatorId])
REFERENCES [dbo].[Domains.Users] ([DomainId], [UserId])
GO
ALTER TABLE [dbo].[Forums.Categories.Threads] CHECK CONSTRAINT [FK_Forums.Categories.Threads_Domains.Users]
GO
ALTER TABLE [dbo].[Forums.Categories.Threads] WITH CHECK ADD CONSTRAINT [FK_Forums.Categories.Threads_Forums.Categories] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Forums.Categories] ([CategoryId])
GO
ALTER TABLE [dbo].[Forums.Categories.Threads] CHECK CONSTRAINT [FK_Forums.Categories.Threads_Forums.Categories]
GO
ALTER TABLE [dbo].[Forums.Categories.Threads] WITH CHECK ADD CONSTRAINT [FK_Forums.Categories.Threads_Forums.Categories1] FOREIGN KEY([DomainId], [ForumId], [CategoryId])
REFERENCES [dbo].[Forums.Categories] ([DomainId], [ForumId], [CategoryId])
GO
ALTER TABLE [dbo].[Forums.Categories.Threads] CHECK CONSTRAINT [FK_Forums.Categories.Threads_Forums.Categories1]
GO
TABLE2=====================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Forums.Categories.Threads.Languages](
[DomainId] [int] NOT NULL,
[ForumId] [int] NOT NULL,
[CategoryId] [int] NOT NULL,
[ThreadId] [int] NOT NULL,
[LanguageId] [nchar](2) NOT NULL,
[CultureId] [nvarchar](16) NOT NULL,
[Thread] [nvarchar](64) NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateClosed] [datetime] NULL,
[Views] [int] NULL,
[IsAnnouncement] [bit] NULL,
[Preference] [bit] NULL,
CONSTRAINT [IX_Forums.Categories.Threads.Languages] UNIQUE NONCLUSTERED
(
[ThreadId] ASC,
[LanguageId] ASC,
[CultureId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Forums.Categories.Threads.Languages_1] UNIQUE NONCLUSTERED
(
[DomainId] ASC,
[ForumId] ASC,
[CategoryId] ASC,
[ThreadId] ASC,
[LanguageId] ASC,
[CultureId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If True then the Thread will be on top. No replies can be made' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Forums.Categories.Threads.Languages', @level2type=N'COLUMN',@level2name=N'IsAnnouncement'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Only Moderators can be set to True, if True then the Thread will be put on top (below any announcements if they exists)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Forums.Categories.Threads.Languages', @level2type=N'COLUMN',@level2name=N'Preference'
GO
ALTER TABLE [dbo].[Forums.Categories.Threads.Languages] WITH CHECK ADD CONSTRAINT [FK_Forums.Categories.Threads.Languages_Forums.Categories.Threads] FOREIGN KEY([DomainId], [ForumId], [CategoryId], [ThreadId])
REFERENCES [dbo].[Forums.Categories.Threads] ([DomainId], [ForumId], [CategoryId], [ThreadId])
GO
ALTER TABLE [dbo].[Forums.Categories.Threads.Languages] CHECK CONSTRAINT [FK_Forums.Categories.Threads.Languages_Forums.Categories.Threads]
GO
ALTER TABLE [dbo].[Forums.Categories.Threads.Languages] ADD CONSTRAINT [DF_Forums.Categories.Threads.Languages_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO
TABLE3=====================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Forums.Categories.Threads.Languages.Posts](
[DomainId] [int] NOT NULL,
[ForumId] [int] NOT NULL,
[CategoryId] [int] NOT NULL,
[ThreadId] [int] NOT NULL,
[LanguageId] [nchar](2) NOT NULL,
[CultureId] [nvarchar](16) NOT NULL,
[CreatorId] [int] NULL,
[PostId] [int] IDENTITY(1,1) NOT NULL,
[Post] [nvarchar](max) NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateLastUpdate] [datetime] NULL,
[NotifyRepliesAll] [bit] NULL,
[NotifyRepliesQuoted] [bit] NULL,
[PostIdToReply] [int] NULL,
127.0.0.1 [nvarchar](15) NULL,
[MetaKeywords] [nvarchar](128) NULL,
[Deleted] [datetime] NULL,
CONSTRAINT [PK_Forums.Categories.Threads.Languages.Posts] PRIMARY KEY CLUSTERED
(
[PostId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums.Categories.Threads.Languages.Posts] WITH CHECK ADD CONSTRAINT [FK_Forums.Categories.Threads.Languages.Posts_Domains.Users] FOREIGN KEY([DomainId], [CreatorId])
REFERENCES [dbo].[Domains.Users] ([DomainId], [UserId])
GO
ALTER TABLE [dbo].[Forums.Categories.Threads.Languages.Posts] CHECK CONSTRAINT [FK_Forums.Categories.Threads.Languages.Posts_Domains.Users]
GO
ALTER TABLE [dbo].[Forums.Categories.Threads.Languages.Posts] WITH CHECK ADD CONSTRAINT [FK_Forums.Categories.Threads.Languages.Posts_Forums.Categories.Threads.Languages] FOREIGN KEY([DomainId], [ForumId], [CategoryId], [ThreadId], [LanguageId], [CultureId])
REFERENCES [dbo].[Forums.Categories.Threads.Languages] ([DomainId], [ForumId], [CategoryId], [ThreadId], [LanguageId], [CultureId])
GO
ALTER TABLE [dbo].[Forums.Categories.Threads.Languages.Posts] CHECK CONSTRAINT [FK_Forums.Categories.Threads.Languages.Posts_Forums.Categories.Threads.Languages]
GO
ALTER TABLE [dbo].[Forums.Categories.Threads.Languages.Posts] ADD CONSTRAINT [DF_Forums.Categories.Threads.Languages.Posts_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO
Any wrong ?
ANy idea ?
December 21, 2010 at 10:49 am
put the previous sugestion in a single transaction, so others cannot insert invalid data.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 21, 2010 at 10:58 am
put the previous sugestion in a single transaction, so others cannot insert invalid data
I don't understand you. What do you mean ..
December 21, 2010 at 3:23 pm
goodyes (12/21/2010)
put the previous sugestion in a single transaction, so others cannot insert invalid data
I don't understand you. What do you mean ..
Just put the reply stewartc-708166 provided within at single sql transaction.
Begin transaction
begin try
stewartc-708166 code ....
commit transaction
end try
begin catch
rollback transaction
end catch
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 21, 2010 at 4:48 pm
OK, i do it
One question about
Will that code (that use Tran) LOCK that tables until finish, ..
So until end or commit transaction, will no one put any record on this tables ?
December 22, 2010 at 12:26 am
This was removed by the editor as SPAM
December 22, 2010 at 3:11 pm
You use IDENTITY which is a physical storage locator and not relational. You need to go back, throw this crap and get a real key. In the meantime, it needs an accurate name -- it is not a physical_insertion_attempt_cnt; it is "physical_insertion_attempt_cnt" and that name will let you see how illogical your design is. What you probably wanted was a sequence
I don't understand you
A category is an attribute property, so "category_id" is absurd and illegal. This is a "something_category" attribute, not an entity with an identifier. Unless they change a lot or there is a huge number of them, I would make this a CHECK() constraint, not a REFERENCES.
I don't understand you, .. a CategoryId is an Identity of a Category field used to classify items, like: food, cars, films, ..
How does a thread-creator get to be NULL? A thread just appears by itself?
Anonymous posts, and i don't want to reference any ID being like id1=anonymous, as if the user accidentally deletes this id, then errors throw
We do not use BIT flags in SQL -- that was assembly language. Think about how stupid a NULL bit is.
What do you use for ?
There is an ISO CHAR(3) language code; there is no "language_id"; do you know how to find ISO Standards? Why did you use NVARCHAR(2) for a fixed length code that uses only Latin-1? Do you want math symbols and Chinese in that column? You just invite bad data.
Read again, LanguageId is NChar(2)
The IP is too small for Version 6. The preference column is so vague I assumed it meant "sexual preference" because that is what I see on forms these days. Other data element names are vague or wrong.
All IPs of todays fit inside a NVarChar(16) as it was defined
Can you explain what organization defines that VARCHAR(16) culture_id you have? The usual convention is a combination of the ISO language and country code. Surely, you did careful research and would make up your codes.
Culture is NVarChar(16)
Language and culture are different fields as user can query for any combination, and i dont have time to let you know why
How can a domain, forum and physical insertion attempt count be part of a Language? If a lot of people see Esperanto (your views column) what does that mean? That table is totally messed up.
Cause they refererences other tables,
A)Forums; includes DomainId(FK) and ForumId(PK)
B)Forums.Categories: DomainId(FK), ForumId(FK), CategoryId(PK)
B2)Forums.Categories.Languages: .. CategoryId(FK), LanguageId(FK), CultureId(FK)
C)Forums.Categories.Threads DOMAINID(FK), FORUMID(FK), CATEGORYID(FK), THREADID(PK)
I use that For integrity
Take your mention to not use domainid and forumid .. then maybe some Thread can be on X CategoryId that was not intended for that domain/forum ..
Example:
Domain.com(id1) have a Forum(id1) that have CatA(id1) and CatB(id2)
Domain.NET(id2) have a ForumB(id2) that have CatC(id3) and CatD(id4)
If some record (thread) needs to go only for Domain/Forum/CatA, someone can put a Thread intended for .com/Forum/CatA on CatD(id4) that not belongs from the Forum intended for that thread, so for integrity, as per your example an error can occurr..
The culture code (language/country) should be an attribute of a message in a thread. Everything in this disaster is inside out.
NOT, as a same thread can be submitt in one or more languages and cultures.
Again, as per your example, if lang/cult is a field of the same table, then for each lang & code one PK will be required (and this ensures a lot of work on programming), and also is not following the basic 3 rules of DB design.
So anoter table is ..Threads.Languages
I spent over an hour trying to make sense of this. It is so bad, I want to use it in a book as an a example.
Don't worry about, its logical that you don't understand that, as i'm developing this software close to a 1 year ago, and you don't know anything more than the one you read on this thread
CELKO (12/22/2010)
Instead of dumping the CREATE TABLE statements in raw form, could you please edit them for human beings to read? When you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
I'm sorry but i post this thread only to get some help for a question, .. some reply me to put the DDL and i do as fast as i can
You need to completely start over. You need more help than you can get on a forum. The one thing you seem to have done right is not being afraid of multi-column keys. But then you throw in that non-relational IDENTITY as a magical universal key.
I don't think as you, but i invite you to get my full .sql DDL if you want, maybe you're an expert, but as is, i think you need to understand the product before do severe
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply