February 5, 2024 at 6:08 pm
We've been tasked for updating a system for a client on a more modern approach. Currently they do their business in access forms.
The problem is when I try to reference the table I added to their existing table, I get the following error ,' columns in table do not match an existing primary key or UNIQUE constraint.' Here is the table I want to add a foreign key to:
CREATE TABLE [dbo].[REPORTS_GROUPS_MAP](
[GROUP_IDX] [int] NOT NULL,
[VERSION_IDX] [int] NOT NULL,
CONSTRAINT [PK_REPORTS_GROUPS_MAP] PRIMARY KEY CLUSTERED
(
[GROUP_IDX] ASC,
[VERSION_IDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
I want the VERSION_IDX in the table above to reference this table:
CREATE TABLE [dbo].[ITEMS](
[ITEM_IDX] [int] NOT NULL,
[VERSION_IDX] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](64) NULL,
CONSTRAINT [PK_ITEMS] PRIMARY KEY CLUSTERED
(
[ITEM_IDX] ASC,
[VERSION_IDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
The Version_IDX in the source table is an identity column so each row should be unique. Not sure as to why then I am getting a unique error. The solution seems to be either to drop the composite key or have the VERSION_IDX column also have unique constraint.
February 5, 2024 at 6:19 pm
Adding in a UNIQUE constraint makes this work. Without it, SQL does not know that it is unique, despite the IDENTITY property.
DROP TABLE IF EXISTS dbo.REPORTS_GROUPS_MAP;
DROP TABLE IF EXISTS dbo.ITEMS;
CREATE TABLE dbo.ITEMS
(
ITEM_IDX INT NOT NULL
,VERSION_IDX INT IDENTITY(1, 1) NOT NULL
,NAME VARCHAR(64) NULL
,CONSTRAINT PK_ITEMS
PRIMARY KEY CLUSTERED(
ITEM_IDX ASC
,VERSION_IDX ASC
)
,CONSTRAINT UQ_ITEMS_Version_IDX
UNIQUE(VERSION_IDX)
);
GO
CREATE TABLE dbo.REPORTS_GROUPS_MAP
(
GROUP_IDX INT NOT NULL
,VERSION_IDX INT NOT NULL
,CONSTRAINT PK_REPORTS_GROUPS_MAP
PRIMARY KEY CLUSTERED(
GROUP_IDX ASC
,VERSION_IDX ASC
)
,CONSTRAINT FK_REPORTS_GROUPS_MAP_VERSION_IDX
FOREIGN KEY(VERSION_IDX)
REFERENCES dbo.ITEMS(VERSION_IDX)
);
GO
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 5, 2024 at 6:28 pm
Thank you. I'll have to add the unique constraint. I would remove the composite key, but in this case I'm a bit more wary as it might affect their work in access.
February 5, 2024 at 6:33 pm
Thank you. I'll have to add the unique constraint. I would remove the composite key, but in this case I'm a bit more wary as it might affect their work in access.
You are right to be wary. Changes like this should be thoroughly tested before being implemented.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 5, 2024 at 9:53 pm
Why do you think you need a non-relational IDENTITY column in this table? The primary key is supposed to be unique already; this is a definition, not an option. Is your source data that you're trying to move over this screwed up? Do you need to scrub it off-line? Have you ever read a book on RDBMS? Again, by definition, a key is a subset of the columns in the table and has nothing to do with a counter for the insertion order of rows into that table. Remember my definition i IDENTITY is not a column; it is a table property.
CREATE TABLE Items
( item_name VARCHAR (64) NOT NULL PRIMARY KEY);
I'm a little surprised that you don't have a UPC, ISBN, or some other industry standard to identify these items. These industry identifiers usually have check digits, regular expressions, or some other way of validation.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 6, 2024 at 1:40 pm
Have you ever read a book on RDBMS?
You can explain things without being such an absolute condescending asshole, Joe!
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2024 at 3:49 pm
February 6, 2024 at 9:00 pm
Be glad you never had a class under Dykstra at UT. His favorite way of talking to students was to tell them "You're doing everything completely wrong!" But you knew when you heard him say that it was true! Asking a poster if he's ever read a book on RDBMS is a serious question. When his code is full of errors, then you might have to post an entire college course to help him. Of course, many of these people do not want to learn anything; they want you to do their homework for them. I feel sorry for some of them because they were told to learn SQL from a book on SQL, and they have no background whatsoever in RDBMS, so this is very much like having to do calculus problems for a living when you never got as far as algebra. And you do not understand how limits work.
30 years ago I started posting on SQL forums and newsgroups, I used to be super polite. No matter how dumb the question. I used to post references to articles, books and other sources. I used to post DDL and DML for them. After about a decade of this, I got sick of doing their job for them and watching them post again with the same screw-ups they had done the first time. They didn't want a real answer; they wanted somebody to do their job or homework for them. In fact, I just ran into a posting on Quora where a Chinese student gave an incorrect skeleton of three tables in text not DDL and a list of 4 or 5 queries he wanted other people to do for him. They were all very typical homework queries).
I don't think I'm the only person here who thinks the posters should make at least a minimal effort in their code and specifications. Don't you get tired of people who want you to read the manual of a particular product for them, because they are too lazy to open a manual or Google it? There are times I feel that I wasted 10 years of my life on ANSI X3 H2, writing my 10 books, and several hundred articles on SQL and RDBMS.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 7, 2024 at 3:35 pm
Comparing yourself to an even bigger asshole doesn't lessen the fact that you're an asshole. In fact, it confirms just how much of an asshole you are. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2024 at 4:13 pm
Did you mean "Dijkstra"?
February 7, 2024 at 4:33 pm
Yes. Arrgh! Next time I will need see the spelling correction is doing its job. Dijkstra taught the University of Texas in Austin before his death. State got a lot of oil money and instead of spending it on the usual bunch of stupid things, they dropped a lot of it into the University system. He was one of their "super star teachers" recruits.Roth and Korth also did some interesting work with non-first normal form (NFNF) databases. I never took a class under him (dammit, my loss), but I worked with some of his students during the "dot com boom). One thing they did agree on about him as a teacher was that he did not suffer fools gladly, that quote about "you're doing everything completely wrong" was typical.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply