Table reference issue

Question

Table reference issue

pmb88

SSC Veteran

Points: 293
More actions
February 5, 2024 at 6:08 pm

#4373725
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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pmb88 SSC Veteran Points: 293 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

pmb88 wrote:

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

jcelko212 32090 wrote:

Have you ever read a book on RDBMS?

You can explain things without being such an absolute condescending asshole, Joe!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SoCal_DBD Hall of Fame Points: 3383 More actions · Answer 6

Jeff Moden wrote:

jcelko212 32090 wrote:
Have you ever read a book on RDBMS?
You can explain things without being such an absolute condescending asshole, Joe!

Actually no, no he cannot. I blocked him years ago and never looked back. I strongly encourage others to do the same.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 9

Jonathan AC Roberts

SSCoach

Points: 18361

February 7, 2024 at 4:13 pm

#4374869

Did you mean "Dijkstra"?

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 10

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.