Menu
Can a TABLE have a primary key without a clustered index?
And can a TABLE have a clustered index without having a primary key?
Can anybody briefly tell me the relationship between primary key and clustered index?
231
A primary key is a unique index that is clustered by default. By default means that when you create a primary key, if the table is not clustered yet, the primary key will be created as a clustered unique index.
The primary key is the default clustered index in SQL Server and MySQL. This implies a 'clustered index penalty' on all non-clustered indexes.
We can apply a Primary Key constraint and a Clustered Index constraint to different columns in the same table or to the same column. It's a common practice to apply a Clustered Index to a Primary Key. Since the Primary Key is often used to connect data, it's frequently used in searches.
Yes, primary key is automatically indexed in MySQL because primary key, index, etc gets stored into B-trees. All engines including InnoDB as well as MyISAM automatically supports the primary key to be indexed.
A primary key is a logical concept - it's the unique identifier for a row in a table. As such, it has a bunch of attributes - it may not be null, and it must be unique. Of course, as you're likely to be searching for records by their unique identifier a lot, it would be good to have an index on the primary key.
A clustered index is a physical concept - it's an index that affects the order in which records are stored on disk. This makes it a very fast index when accessing data, though it may slow down writes if your primary key is not a sequential number.
Yes, you can have a primary key without a clustered index - and sometimes, you may want to (for instance when your primary key is a combination of foreign keys on a joining table, and you don't want to incur the disk shuffle overhead when writing).
Yes, you can create a clustered index on columns that aren't a primary key.
64
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
