Constraint Question

[gloria98734]

Hello,

I have a table like this:

TABLENAME

Columns:

ID
CU_ID
EMAIL_ADDRESS
DATE

And some sample data like this:

ID CU_ID EMAIL_ADDRESS DATE
1 200 test@domain.com 10/08/07
1 200 test1@domain.com 10/08/07

I am trying to create a constraint that will not allow me to add another record with the same email address if the CU_ID already exist.

For example:

If I try to add this to the existing records,

ID CU_ID EMAIL_ADDRESS DATE
1 200 test@domain.com 10/08/07

should not work because the email address already existit is already added

but this,

ID CU_ID EMAIL_ADDRESS DATE
1 201 test@domain.com 10/08/07

should work because cu_id is new.

Any help would be greatly appreciated.

Thanks a lot

 

[rajeevnandanmishra]

It looks that you require a composite primary key.
You can use following statement to create this:

alter table TABLENAME add constraint pk_TABLENAME primary key (cu_id, email_address)

Though, consider that if any of these columns are nullable, then you first need to make them "not null".

R

 

[gloria98734]

thanks a lot rajeevnandanmishra , btw long nickname