Table Key validation

I have a new Table A with only one Key = X,Y.

When I insert a new record I must validate not Only both fields combination on the same time (X,Y), but also the Field X for all table.

EX: If I insert field X=ZZ, and there is already one ZZ in my table, the user must shoose another value for X.

How can I validadate this?


Is it your Primary key ?

Yes, and the only one in my Table

Then you can not just validate only one field because for primary key you need to validate all the fields while updating or amending records.

In the begining, there was no need to validate only one fo the field, so I created the double key. But now is importante to validate so.

how can I overcome the situation?

Any suggestions?


Can you just elaborate your requirement in more detail ? So it is possible for us to provide you the better solution.

I have a Table with a double Key like (nº Par,nº Prod). When I insert a new record in my table I pretend validate not only the (nº Par,nº Prod) key, but also the nº Par field in my table. This field nº Par must be unique in my table. How can I enforce this condition by C/AL?? thanks

It is your primary key so I don’t think it is possible because combination of these two fields can be unique.

But you can put Error message on oninsert trigger of table if nPar field value is duplicate

Or else you need to change your primary key

Amol This is a good solution: “But you can put Error message on oninsert trigger of table if nPar field value is duplicate” like this? IF nPar= Rec.nPar THEN ERROR(Text001); I pretend that user can not proceed until nPar <> Rec.nPar How can I do That?

I think the problem here is that Luana does not understand what the concept “primary key” means.

Luana, what you are saying is you want to “validate two fields”, and then you describe what you want to happen to one of the fields, and that you put both fields into the primary key to make that happen. You need to understand what makes a key the primary key, because simply putting those fields in there won’t “validate” anything.

First, learn what “primary key” means. This has nothing to do with validation, it has to do with making records in the table uniquely identifiable. If you set the primary key to “nº Par,nº Prod” that means that each Combination of those two fields must be unique. This means that any “nº Par” value can be repeated an unlimited number of times, provided that the “nº Prod” value is different for each one. If you need only the “nº Par” field to be unique, then maybe the primary key should just be “nº Par”.

DenSter, I know the meening of the primary key. The problem is that on first functional study, the primary key was this one ( nPar,Y). But now the primary Key shoud be X or as is not possible to change, I call for experience help :slight_smile:

But in SQL DataBase, I can Not change primary Key esealy? So I have to implement one solution.

I have already records in my table, but for sure don’t have any nPar duplicate.

You should definitely NOT change the primary key constraint within SQL Management Studio. ALL table design should happen in the Table Designer within the classic NAV client.

What do you want to do? First you are talking about validating fields, and now you are talking about changing the primary key of a table that already has records in them. How is it possible that a table first has records uniquely identified by a combination of two fields, and then the design changes to be uniquely identified by one other field? You are not making a lot of sense to me.

You probably need to export the data, change the design of the table (you can’t change the primary key with records in the table), change the data (because different key values are now supposed to be unique) and import the data into the redesigned table.

Take it one step at a time.

What if you keep the primary key as it is. and leaving second field always blank?