SQL Server 2008: the Columns in Table Do Not Match an Existing Primary Key or Unique Constraint

SQL Server 2008: The columns in table do not match an existing primary key or unique constraint

It means that the primary key in tblOne hasn't been properly declared - you need to go to tblOne and add the PRIMARY KEY constraint back onto it.

If you're sure that tblOne does have a PRIMARY KEY constraint, then maybe there are multiple tblOne tables in your DB, belonging to different schemas, and your references clause in your FK constraint is picking the wrong one.

If there's a composite key (which your comment would indicate), then you have to include both columns in your foreign key reference also. Note that a table can't have multiple primary keys - but if it has a composite key, you'll see a key symbol next to each column that is part of the primary key.

The columns in 'table' do not match an existing primary key or unique constraint

Caveat: Without more detail, I'm flying almost blind, my answer is based on what I could assume from your question.

If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.

From Books Online:

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY
KEY constraint in another table; it can also be defined to reference
the columns of a UNIQUE constraint in another table.

In this instance, you would need column B in tbl_One to be unique, either by it being the primary key or having a Unique constraint on it.

UNIQUE constraint:

CREATE TABLE tbl_One
(
    A int,
    B int UNIQUE,
    E int
    CONSTRAINT [PK_tbl_One] PRIMARY KEY 
    (
        A ASC,
        B ASC,
        E ASC
    )
)

If it's the case that the data in column B is not unique, you can create a function based CHECK constraint to achieve a similar effect.

Function(Not tested):

CREATE FUNCTION dbo.CheckFunction(@B int)
RETURNS INT
AS BEGIN
    RETURN (SELECT CASE COUNT(*) 
                   WHEN 0 THEN 0 
                   ELSE 1 
                   END 
              FROM tbl_One 
             WHERE B=@B)
END

CHECK constraint:

ALTER TABLE tbl_Two
ADD CONSTRAINT chk_CheckFunction
CHECK (dbo.CheckFunction(B) = 1)

Here is a SQL Fiddle that demonstrates this.

Thanks for pointing out the referential integrity issue Damien, an AFTER DELETE trigger can help get around this, but there are shortcomings. Here is a good discussion on referential integrity managed with foreign keys vs triggers.

The column in table 'Users' do not match an existing primary key or unique constraint

It means that Users is having a combination not present in the parent table IssueBooks. Figure out the value and update it accordingly.

1. Figure out the combination, which is missing in parent

SELECT ADMIN_ID, USN, E_ID
FROM Users AS uo
WHERE NOT EXISTS (SELECT 1 FROM IssueBooks 
                  WHERE ADMIN_ID = uo.ADMIN_ID 
                    AND USN = uo.USN 
                    AND E_ID = uo.E_ID)

2. Now, you need to either update or delete the combination in Users:

UPDATE Users
SET ADMIN_ID = <newvalue>,
    USN = <newvalue>,
    E_ID = <newvalue>
WHERE ADMIN_ID = <oldvalue> 
  AND USN = <oldvalue> 
  AND E_ID = <oldvalue>

or

DELETE Users
WHERE ADMIN_ID = <oldvalue> AND USN = <oldvalue> AND E_ID = <oldvalue>

3. Now create the foreign key accordingly in Users table:

ALTER TABLE Users 
    ADD CONSTRAINT FK_Users_issueBooks 
        FOREIGN KEY (ADMIN_ID, USN, E_ID) 
        REFERENCES IssueBooks(ADMIN_ID, USN, E_ID)

The columns in 'table_XXX' do not match an existing primiary key or UNIQUE constraint

You are not doing it the right way - hence the error. Put userid in your categories table and join to users if you need the name.

In fact, if that field represents the person who created the category, it should be named something like CreatedByUserId. It should still reference users.userid.

SQL Server 2008 Express - Got The columns do not match an existing primary key or UNIQUE constraint despite constraints being set

It looks like the primary key of ModuleClass is a composite key consisting of three columns, (ModuleID, Section, Number). To set a foreign key reference to that table, you'll have to target all three of those columns.

To target all three of those columns, you'll need to include the column "Number" in the table ClassEnrollment. Then you can set

FOREIGN KEY (ModuleID, Section, Number) 
  REFERENCES ModuleClass (ModuleID, Section, Number)

sql server adding foreign key the column do not match an existing primary key or unique constraint

If you have a compound primary key made up of 3 columns in your PRICING table, then all foreign keys that reference that must also include all three columns.

ALTER TABLE dbo.MOV
ADD CONSTRAINT FK_MOV_PRICING
FOREIGN KEY(ID_PRICING_BUY, -new-column-for-start-date-, -new-column-for-end-date-)
REFERENCES dbo.PRICING(ID_PRICE, DATA_START, DATA_END)

A foreign key cannot reference only part of a primary key.

The only other option would be to create a unique index on just dbo.Pricing(ID_Price) - then you could reference that unique constraint from a foreign key.

But if you can create a unique index on just dbo.Pricing(ID_Price), the real question becomes why isn't just ID_Price alone the primary key? If it's already unique -> then why add two more columns to your primary key???

Finding columns that do not match existing primary key

Do a left join to the parent table on the key in question, then examine the values in the child table where the value in the left-joined parent table are null.

For example, if this was your schema...

table1:
    myKey int primary key,
    ...other columns...

table2:
    otherKey int primary key,
    myKeyFromTable1 int
    ...other columns...

You'd do this:

select distinct
    t2.myKeyFromTable1

from table2 t2

left join table1 t1 on t1.myKey = t2.myKeyFromTable1

where t1.myKey is null

That would give you the distinct values in table2 that wouldn't have a corresponding parent in table1.

---

Related Topics