Database constraint

[angelica]

I have two table for which I want to make a link in the diagram but it seems for some reason, I am not allowed to do it. I am trying to join LA.LANo to LABook.LANo.

Here is the message I get:

table LA:
LAID (Int)- Primary Key, LANo(nvarchar 15) Primary Key, supplierFID etc


table LABook:
LABookID (int)-Primary Key, LANo (nvarchar15) etc

Any one can help me with this please?

[mendhak]

You'll need to specify LaBook.LANo as participating in a foreign key relationship in the table designer for LaBook. In addition LA.LANo must be a primary key. If you're still getting the error then ensure that LA.LANo is marked as unique.

[szlamany]

You show table LA with two primary keys - both LAID and LANo - that's not possible.

Please be more accurate in your posting - otherwise we have no idea what your table really looks like.

Also - try doing this with a DDL statement instead of the GUI.

Here's an example

ALTER TABLE PayAdjust_T
	ADD CONSTRAINT FKPayAdjustPayroll
	FOREIGN KEY (MasId) REFERENCES Payroll_T(MasId)

Here I am altering my table PayAdjust_T and telling it that the column MASID (in PayAdjust_T must be in the Payroll_T table (where it's also called MasId).

The datatypes of both MasId columns must be the same.

And of course - what I believe your problem to be...

In my case the field MasId in Payroll_T is a PRIMARY KEY. It must be either a primary key or have a UNIQUE constraint.

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 your case since LABookId is already a PRIMARY KEY then you cannot make LANo (in the LABook table) a PRIMARY KEY - but you can make it a UNIQUE constraint.

But actually - you should not have LANo in the LA table. That's breaking rules of normalization. You should actually have LABookId in the table LA. But I'm guessing that it's way too late to make this change in your code.

Or you remove LABookId as a column in the table LABook. Why is it needed? Is it used anywhere in another table?

[angelica]

hi ,

I cant make any alterations now otherwise I might as well start afresh.

I have LAID and LANo both foreign keys. I selected both of them and clicked the Primary Key icon.

I only need LAID to use it in the ordering DESC or ASC. I wonder if I can still increment LAID without having it as a primary Key? If so it will solve my problem.

What is your advice pls?

[szlamany]

So - just to verify - you want to make it so that a LANo cannot be entered into the LA table unless that LANo already exists in the LABook table - right?

And second - need to verify this also - the LANo values in the LABook table are unique - right? You don't have the same 15 character value appearing more then once - right?

To prove that do :

Select LANo From LABook Group by LANo Having Sum(1)<>1

You should get no rows returned - if you do stop and post back here.

Otherwise - try this

In a query window type

ALTER TABLE LA
	ADD CONSTRAINT FKLALABook
	FOREIGN KEY (LANo) REFERENCES LABook(LANo)

and tell me what error it give - if in fact it gives an error message.

[angelica]

Hi Szlamany,

I ve temporarily solved the problem becuase I realized it was coming from a couple of records I had deleted manually from the table LABook so the reference integrety constraint was giving an error.

However I still need to know if I can improve on it without changing the tables structures.

LA.LANo and LABook.LANo is a one to many releationship and there might be LA.LANo not existing temporarily in LABook.LANo.

How can I sort LA No ? By the way when I tried sorting from the property of the combo box control I got all records wrong. Dont know how it happens cos Im not so experienced but I do like to find a solution to sort records by LA.LANo without creating problems when populating a combo box.

Thanks