Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks
Can you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks
|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks
|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks
|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]
|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]
|||Is this the Steps Tom:
..
_______
| Tech | -- 1 -- --N [ Ticket ]
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
..
_______
| Tech | -- 1 -- --N [ Ticket ]
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||When do i make the new Column the Primary key and whats the code to do that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
sql
Showing posts with label rules. Show all posts
Showing posts with label rules. Show all posts
Wednesday, March 28, 2012
Replacing a Primary Key
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
ThanksCan you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we wan
t
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]|||Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||When do i make the new Column the Primary key and whats the code to do that
?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_I
d
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id reference
s
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above fo
r
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
ThanksCan you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we wan
t
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]|||Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||When do i make the new Column the Primary key and whats the code to do that
?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_I
d
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id reference
s
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above fo
r
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1
Replacing a Primary Key
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
ThanksCan you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>>Can you give us some more detail? What exactly is being moved? How about
>>posting the DDL for the tables involved?
>[quoted text clipped - 10 lines]
>>Thanks|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>>Can you give us some more detail? What exactly is being moved? How about
>>posting the DDL for the tables involved?
>[quoted text clipped - 10 lines]
>>Thanks|||Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>>Could you change the FK to use ON DELETE CASCADE and then just update the
>>PK?
>[quoted text clipped - 15 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via SQLMonster.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>>Could you change the FK to use ON DELETE CASCADE and then just update the
>>PK?
>[quoted text clipped - 15 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||When do i make the new Column the Primary key and whats the code to do that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>>I'd do the following:
>[quoted text clipped - 25 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via SQLMonster.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>>I'd do the following:
>[quoted text clipped - 25 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
ThanksCan you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>>Can you give us some more detail? What exactly is being moved? How about
>>posting the DDL for the tables involved?
>[quoted text clipped - 10 lines]
>>Thanks|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>>Can you give us some more detail? What exactly is being moved? How about
>>posting the DDL for the tables involved?
>[quoted text clipped - 10 lines]
>>Thanks|||Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>>Could you change the FK to use ON DELETE CASCADE and then just update the
>>PK?
>[quoted text clipped - 15 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via SQLMonster.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>>Could you change the FK to use ON DELETE CASCADE and then just update the
>>PK?
>[quoted text clipped - 15 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||When do i make the new Column the Primary key and whats the code to do that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>>I'd do the following:
>[quoted text clipped - 25 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via SQLMonster.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>>I'd do the following:
>[quoted text clipped - 25 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
Monday, March 26, 2012
Replacement for my LIKE Clause
Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
[vbcol=seagreen]
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ?
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
[vbcol=seagreen]
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.
Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN ?
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
>
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.
sql
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
[vbcol=seagreen]
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ?
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
[vbcol=seagreen]
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.
Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN ?
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
>
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.
sql
Subscribe to:
Posts (Atom)