SQL Server how to maintain GUID across tables in same DB

Question

I want to create a DB , where each table's PK will be GUID and which will be unique across the DB,

Example: my DB name is 'LOCATION'. And I have 3 table as 'CITY' , 'STATE' and 'COUNTRY'.

I want that all the 3 tables have same PK field as GUID ,and that value will be unique across DB.

How to do this in SQL Server, any idea? I have never used SQL Server before, so it will be helpful if briefly explained.

Answer 1

What do you mean exactly ? Just create the table, add an Id field to each table, set the data type of the Id field to 'uniqueidentifier', and you're good to go. Next, add a primary constraint on those columns, and make sure that, when inserting a new record you assign a new guid to that column (for instance, by using the newid() function).

Answer 2

create table CITY (
    ID uniqueidentifier not null primary key default newid(),
    .
    .
    .
)

Repeat for the other tables.

Answer 3

I can't think of any good reason to have a unique number shared by 3 tables, why not just give each table a unique index with a foreign key reference? Indexed fields are queried quicker than random numbers would be.

I would create a 'Location' table with foreign keys CityId, StateId & CountryId to link them logically.

edit: If you are adding a unique id across the City, State and Country tables then why not just have them as fields in the same table? I would have thought that your reason for splitting them into 3 tables was to reduce duplication in the database.