Create a table with a Union and specify Primary Key

I want to create a table with a union. Which I have already accomplished. I want to specify the Primary Key in the statement.
Or would I have to use another statement. How would I do that? With an update and what would the syntax be?
Thanks before hand,
itarinLike this?

USE Northwind
GO

DECLARE @.OrderID int

SELECT @.OrderId = OrderId FROM Orders

SELECT @.OrderId
GO

DECLARE @.OrderID int

SET @.OrderId = (SELECT OrderId FROM Orders)

SELECT @.OrderId
GO

USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
GO

INSERT INTO myTable99(Col1) SELECT 1
GO

SELECT * FROM myTable99
GO

DECLARE @.Col1 int
SELECT @.Col1 = 0

UPDATE myTable99 SET @.Col1 = Col1 = @.Col1+1

SELECT * FROM myTable99
SELECT @.Col1
GO

DROP TABLE myTable99
GO

SELECT Char(160)

USE Northwind

CREATE TABLE myTable99(Col1 int)
GO

INSERT INTO myTable99(Col1)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
GO

DECLARE myCursor99 CURSOR FOR SELECT Col1 FROM myTable99
DECLARE @.Col1 int

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @.Col1
SELECT '@.@.ERROR = ' + CONVERT(varchar(5),@.@.ERROR) + ' @.@.FETCH_STATUS = ' + CONVERT(varchar(5), @.@.FETCH_STATUS)
SELECT @.Col1

WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM myCursor99 INTO @.Col1
SELECT '@.@.ERROR = ' + CONVERT(varchar(5),@.@.ERROR) + ' @.@.FETCH_STATUS = ' + CONVERT(varchar(5), @.@.FETCH_STATUS)
SELECT @.Col1
FETCH NEXT FROM myCursor99 INTO @.Col1
SELECT '@.@.ERROR = ' + CONVERT(varchar(5),@.@.ERROR) + ' @.@.FETCH_STATUS = ' + CONVERT(varchar(5), @.@.FETCH_STATUS)
SELECT @.Col1
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO

DROP Table myTable99
GO


CREATE TRIGGER myTrigger99 ON EmployeeGamingLicense
FOR UPDATE
AS

INSERT INTO TERMINATION(Status, [TM #], LastName, FirstName, SocialSecurityNumber, DateHired, Title)
SELECT STATUS, [TM#], LASTNAME, FIRSTNAME, [SSN#], HIREDATE, JOBTITLE
FROM inserted
WHERE STATUS = 'TERMINATED'

--Assuming you want them removed as well, and assumin SSN# is the key
DELETE FROM EmployeeGamingLicense o
WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])

USE Northwind
GO

CREATE TABLE myTable00(Col1 int NOT NULL)
CREATE TABLE myTable01(Col1 int NOT NULL)
GO

INSERT INTO myTable00(Col1) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
INSERT INTO myTable01(Col1) SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
GO

SELECT * INTO myTable99 FROM myTable00 UNION ALL SELECT * FROM myTable01
GO

sp_Help myTable99
GO

ALTER TABLE myTable99 ADD CONSTRAINT [PK_Col1] PRIMARY KEY CLUSTERED
(
[Col1]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

sp_Help myTable99
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable00
DROP TABLE myTable01
DROP TABLE myTable99
GO

covering Primary Keys.. What's too much

I have a table which I'd like to create a Primary Key that would have to
cover 6 fields to enforce uniqueness.
My question is, is that too much. I know it's common to have tables with
PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields, 2
varchar(5) fields, 1 tinyint]
I know I can use a surrogate "ID" identity field as the PK, but I prefer to
use PK's that are more natural if possible.
Any thoughts on this?
Thanks,
ChrisWhatever has to be unique must be constrained to be unique. IOW, if your
business rules require that this table have a 6-col combination that is
unique, then you should make that unique - either via a PRIMARY KEY or
UNIQUE constraint. That said, if you will have other tables referencing
this table, then it would make sense to create a surrogate key and then use
that key in the FOREIGN KEY constraints.
That said, consider the surrogate key anyway and cluster on it. Make it,
say, your PK. Then, create a UNIQUE constraint on the 6 col combo. Inserts
into the table - if you use an identity for the PK, will perform well and
the table (i.e. clustered index) will not need to be defragged. The
nonclustered index on the UNIQUE constraint would likely need a fill factor
< 100 to mitigate fragmentation.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Chris" <rooster575@.hotmail.com> wrote in message
news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
I have a table which I'd like to create a Primary Key that would have to
cover 6 fields to enforce uniqueness.
My question is, is that too much. I know it's common to have tables with
PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields, 2
varchar(5) fields, 1 tinyint]
I know I can use a surrogate "ID" identity field as the PK, but I prefer to
use PK's that are more natural if possible.
Any thoughts on this?
Thanks,
Chris|||This table holds only FK's and could be considered a "detail" table.
It's also almost completely a "read" table. Writing is completed and then
done again very seldomly.
If I add an "ID" column as a PK, it will never be referenced by another
table. [No FK's will exist], which makes me lean more towards the 6-key PK
option. Does this sway your answer at all?
I appreciate your help. After re-reading your response, the solution seems
clear. What do you think?
-Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
> Whatever has to be unique must be constrained to be unique. IOW, if your
> business rules require that this table have a 6-col combination that is
> unique, then you should make that unique - either via a PRIMARY KEY or
> UNIQUE constraint. That said, if you will have other tables referencing
> this table, then it would make sense to create a surrogate key and then
> use
> that key in the FOREIGN KEY constraints.
> That said, consider the surrogate key anyway and cluster on it. Make it,
> say, your PK. Then, create a UNIQUE constraint on the 6 col combo.
> Inserts
> into the table - if you use an identity for the PK, will perform well and
> the table (i.e. clustered index) will not need to be defragged. The
> nonclustered index on the UNIQUE constraint would likely need a fill
> factor
> < 100 to mitigate fragmentation.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> I have a table which I'd like to create a Primary Key that would have to
> cover 6 fields to enforce uniqueness.
> My question is, is that too much. I know it's common to have tables with
> PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields,
> 2
> varchar(5) fields, 1 tinyint]
> I know I can use a surrogate "ID" identity field as the PK, but I prefer
> to
> use PK's that are more natural if possible.
> Any thoughts on this?
> Thanks,
> Chris
>|||If that is the case, then you're probably fine without the surrogate key.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Chris" <rooster575@.hotmail.com> wrote in message
news:%23P2RCumhGHA.3996@.TK2MSFTNGP03.phx.gbl...
This table holds only FK's and could be considered a "detail" table.
It's also almost completely a "read" table. Writing is completed and then
done again very seldomly.
If I add an "ID" column as a PK, it will never be referenced by another
table. [No FK's will exist], which makes me lean more towards the 6-key PK
option. Does this sway your answer at all?
I appreciate your help. After re-reading your response, the solution seems
clear. What do you think?
-Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
> Whatever has to be unique must be constrained to be unique. IOW, if your
> business rules require that this table have a 6-col combination that is
> unique, then you should make that unique - either via a PRIMARY KEY or
> UNIQUE constraint. That said, if you will have other tables referencing
> this table, then it would make sense to create a surrogate key and then
> use
> that key in the FOREIGN KEY constraints.
> That said, consider the surrogate key anyway and cluster on it. Make it,
> say, your PK. Then, create a UNIQUE constraint on the 6 col combo.
> Inserts
> into the table - if you use an identity for the PK, will perform well and
> the table (i.e. clustered index) will not need to be defragged. The
> nonclustered index on the UNIQUE constraint would likely need a fill
> factor
> < 100 to mitigate fragmentation.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> I have a table which I'd like to create a Primary Key that would have to
> cover 6 fields to enforce uniqueness.
> My question is, is that too much. I know it's common to have tables with
> PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields,
> 2
> varchar(5) fields, 1 tinyint]
> I know I can use a surrogate "ID" identity field as the PK, but I prefer
> to
> use PK's that are more natural if possible.
> Any thoughts on this?
> Thanks,
> Chris
>|||Thanks again.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eebldFnhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> If that is the case, then you're probably fine without the surrogate key.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23P2RCumhGHA.3996@.TK2MSFTNGP03.phx.gbl...
> This table holds only FK's and could be considered a "detail" table.
> It's also almost completely a "read" table. Writing is completed and then
> done again very seldomly.
> If I add an "ID" column as a PK, it will never be referenced by another
> table. [No FK's will exist], which makes me lean more towards the 6-key PK
> option. Does this sway your answer at all?
> I appreciate your help. After re-reading your response, the solution seems
> clear. What do you think?
> -Chris
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
>|||>> I have a table which I'd like to create a Primary Key that would have to cove
r 6 fields [sic] to enforce uniqueness. My question is, is that too much. <<
This is rare but possible. Since we do not have any specs or knwo
anything about the problem, all we can do is generalize. I have seen
some people create super-keys (sertial number + manufacturer, not
knowning that serial number has manufacturer in it).
1) it is not a surrogate key because it is exposed to the user; that is
the definition from Dr. Codd.
2) you will need to use a UNIQUE (c1,.. ,c6) constraint anyway, so you
now have two indexes
3) You will need code to assure that the IDENTITY is always validate
and points to the right target.