Rejuvenated: Script Creates and Drops for Candidate Keys and Referencing Foreign Keys

Posted by Adam Machanic on SQL Blog See other posts from SQL Blog or by Adam Machanic
Published on Mon, 05 Apr 2010 02:48:00 GMT Indexed on 2010/04/05 3:33 UTC
Read the original article Hit count: 610

Filed under:

foreign keys

|

indexes

|

primary keys

|

scripts

|

unique constraints

Once upon a time it was 2004, and I wrote what I have to say was a pretty cool little script . (Yes, I know the post is dated 2006, but that's because I dropped the ball and failed to back-date the posts when I moved them over here from my prior blog space.) The impetus for creating this script was (and is) simple: Changing keys can be a painful experience. Sometimes you want to make a clustered key nonclustered, or a nonclustered key clustered. Or maybe you want to add a column to the key. Or remove...(read more)

© SQL Blog or respective owner

Related posts about foreign keys

Should all foreign table references use foreign key constraints

as seen on Programmers - Search for 'Programmers'
Closely related to: Foreign key restrictions -> yes or no? I asked a question on SO and it led me to ask this here. If I'm faced with a choice of having a circular reference or just not enforcing the restraint, which is the better choice? In my particular case I have customers and addresses… >>> More
Rejuvenated: Script Creates and Drops for Candidate Keys and Referencing Foreign Keys

as seen on SQL Blog - Search for 'SQL Blog'
Once upon a time it was 2004, and I wrote what I have to say was a pretty cool little script . (Yes, I know the post is dated 2006, but that's because I dropped the ball and failed to back-date the posts when I moved them over here from my prior blog space.) The impetus for creating this script was… >>> More
Fix Firefox Not Scrolling with Up/Down Arrow Keys or Home/End Keys

as seen on How to geek - Search for 'How to geek'
If you’ve encountered a problem where your Firefox installation no longer scrolls when you use the up or down arrow keys, and even the Home or End keys don’t work anymore, there’s an easy fix. When this problem happens, you’ll notice that moving the arrow keys around just… >>> More
Entity Framework and multi-tenancy database design

as seen on Stack Overflow - Search for 'Stack Overflow'
I am looking at multi-tenancy database schema design for an SaaS concept. It will be ASP.NET MVC - EF, but that isn't so important. Below you can see an example database schema (the Tenant being the Company). The CompanyId is replicated throughout the schema and the primary key has been placed on… >>> More
Update multiple rows with known keys without inserting new rows if nonexistent keys are found

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, Let's imagine that we have table items... table: items item_id INT PRIMARY AUTO_INCREMENT title VARCHAR(255) views INT Let's imagine that it is filled with something like (1, item-1, 10), (2, item-2, 10), (3, item-3, 15) I want to make multi update view for this items from data taken… >>> More

Related posts about indexes

Stairway to SQL Server Indexes: Step 1, Introduction to Indexes

as seen on SQL Server Central - Search for 'SQL Server Central'
Indexes are the database objects that enable SQL Server to satisfy each data access request from a client application with the minimum amount of effort, resulting in the maximum performance of individual requests while also reducing the impact of one request upon another. Prerequisites: Familiarity… >>> More
When SQL Server Nonclustered Indexes Are Faster Than Clustered Indexes

as seen on SQL Server Central - Search for 'SQL Server Central'
SQL Server Clustered indexes can have enormous implications for performance of operations on a table. But are there times when a SQL Server non-clustered index would perform better than a clustered index for the same operation? Are there any trade-offs to consider? Check out this tip to learn more… >>> More
text indexes vs integer indexes in mysql

as seen on Stack Overflow - Search for 'Stack Overflow'
Hey, I have always tried to have an integer primary key on a table no matter what. But now I am questioning if this is always necessary. Let's say I have a product table and each product has a globally unique SKU number - that would be a string of say 8-16 characters. Why not make this the PK… >>> More
SQL SERVER – Disabled Index and Update Statistics

as seen on SQL Authority - Search for 'SQL Authority'
When we try to update the statistics, it throws an error as if the clustered index is disabled. Now let us enable the clustered index only and attempt to update the statistics of the table right after that. Have you ever come across the situation where a conversation never gets over and it continues… >>> More
Macro doesn't work in the function.

as seen on Stack Overflow - Search for 'Stack Overflow'
I have problems with following code: http://lisper.ru/apps/format/96 The problem is in "normalize" function, which does not work. It fails on the fifth line: (zero-p a indexes i) (defun normalize (a &optional indexes i) "Returns normalized A." (pragma (format t "Data=~A ~A ~A" a indexes… >>> More