Home Multiple primary key constraints?
Reply: 3

Multiple primary key constraints?

FrenkyB
1#
FrenkyB Published in 2017-11-14 09:49:03Z

I have a table like this:

CREATE TABLE BACAEN
(
CAEN_KEY INT PRIMARY NOT NULL,
CAET_KEY INT,
BUUN_KEY INT
)

I've already defined primary key on CAEN_KEY column. What I also need is to have unique records for CAET_KEY and BUUN_KEY together. (So, combination with same CAET_KEY / BUUN_KEY is allowed only once). The easiest way would be to generate one more composite primary key, but this is not possible because sql server doesn't allow multiple primary key constraints. What are alternatives to this?

Please note: I can not drop primary key constraint on CAEN_KEY.

Jayasurya Satheesh
2#
Jayasurya Satheesh Reply to 2017-11-14 09:56:02Z

Try a unique key Instead. Change the design as follows

CREATE TABLE BACAEN
(
CAEN_KEY INT PRIMARY KEY,
CAET_KEY INT  NOT NULL,
BUUN_KEY INT  NOT NULL,
CONSTRAINT uq_BACAEN UNIQUE(CAET_KEY , BUUN_KEY );
)

Now, both the columns CAET_KEY and BUUN_KEY will be having a unique combination as well as they can't hold NULL values as I have specified them as NOT NULL

If want to allow NULL values to the CAET_KEY and BUUN_KEY, that's also fine but you can have only 1 record with NULL in both fields

Usman Rana
3#
Usman Rana Reply to 2017-11-14 09:53:40Z
ALTER TABLE youtablename  ADD CONSTRAINT uq_yourtablename UNIQUE(CAET_KEY , BUUN_KEY );

ps. If your table is already populated, make sure to remove all those entries conflicting with this constraint.

Nandish B
4#
Nandish B Reply to 2017-11-14 10:03:34Z

Use following query to create new Unique Index on your table

CREATE UNIQUE INDEX IDX_Composite_index  ON BACAEN (CAET_KEY,BUUN_KEY);

the above query works even if the table is populated but there should not be duplicates.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.495486 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO