asked on 9/4/2010

How do I connect the primary key or unique key in the best way?

Hi,

I've searched google etc for an understanding on Primary Keys and Unique Keys but I thought I'd ask here too.

I have a few tables that I want to make sure that ONLY the values in those tables are entered in to a master one.

So, I have:

Country
pk                         - bigint, indentity auto incerment (1)
countryCode             - nvarchar(3)
countryName             - nvarchar(150)

City
pk                         - bigint, indentity auto incerment (1)
countryCode             - nvarchar(3)
cityCode                   - nvarchar(3)
cityName             - nvarchar(150)

Store
pk                         - bigint, indentity auto incerment (1)
countryCode             - nvarchar(3)
cityCode                   - nvarchar(3)
cityName             - nvarchar(150)
storeCode             - nvarchar(3)
storeName             - nvarchar(150)

Now, I have them set up with the PK in each then I use a UNIQUE key between them to only get the values from the other table, so store gets City and City gets Country.

Is this right?

It seems that going down the line a few level that the end table of the unique string could be really long!

Any help or guidance would be much appreciated.

Regards,

Ken

Microsoft SQL Server 2008

Last Comment

JoeNuvo

9/7/2010

JoeNuvo

9/4/2010

1. You should use countryCode, cityCode, storeCode as PRIMARY KEY by itself without PK field, since you state that it will be unique value. However, you still can keep PK if you want, but just make it as IDENTITY (AKA autonumber)

script example
CREATE TABLE [Country] (

pk bigint IDENTITY (1,1) , -- this line, keep or remove is depend on your need
countryCode nvarchar(3) PRIMARY KEY,
countryName nvarchar(150)
)

2. in order to validate that, City table should enter only valid countryCode, Store table should enter valid cityCode.
This can be done either by your application front end. Or you may setup FOREIGN KEY to perform control over the table

script example

countryCode must be exists for data enter into City table
ALTER TABLE [City]

ADD CONSTRAINT FK_City_Country FOREIGN KEY ( countryCode )
REFERENCES [Country] ( countryCode )

cityCode must be exists for data enter into Store table
ALTER TABLE [Store]

ADD CONSTRAINT FK_Store_City FOREIGN KEY ( cityCode )
REFERENCES [City] ( cityCode )

Ephraim Wangoya

9/5/2010

Also in your Store table you do not need a field for cityName since you alredy have the cityCode

You can get it by joining Store to City
select cityName
from city
inner join store on (store.cityCode = city.CityCode)

ASKER CERTIFIED SOLUTION

JoeNuvo

9/7/2010

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial