composite keys
satya - Sat Apr 28 2012 14:03:26 GMT-0400 (Eastern Daylight Time)
sql server composite keys
sql server composite keys
satya - Sat Apr 28 2012 14:36:54 GMT-0400 (Eastern Daylight Time)
can one of the columns in a composite key carry a null?
can one of the columns in a composite key carry a null?
Search for: can one of the columns in a composite key carry a null?
satya - 4/25/2014 4:03:07 PM
Example
create table my_table (
id_part1 int not null,
id_part2 int not null,
primary key (id_part1, id_part2)
)
satya - 4/25/2014 4:03:55 PM
sqlserver composite key constraint create table syntax example
sqlserver composite key constraint create table syntax example
Search for: sqlserver composite key constraint create table syntax example
satya - 4/25/2014 4:16:20 PM
My Example
CREATE TABLE [dbo].[t_SatQuestions](
[f_SatQuestion_id] [int] IDENTITY(1,1) NOT NULL,
[f_test_id] [int] NOT NULL,
[f_section_id] [int] NOT NULL,
[f_question_seq] [int] NOT NULL,
[f_question_name] [varchar] (50) NOT NULL,
[f_number_of_options] [int] NOT NULL,
[f_correct_option] [int] NOT NULL,
[f_answer_explanation] [varchar] (128) NOT NULL,
[f_ownerUserid] [varchar](50) NOT NULL,
[f_created_by] [varchar](50) NOT NULL,
[f_created_on] [datetime] NOT NULL,
[f_last_updated_by] [varchar](50) NOT NULL,
[f_last_updated_on] [datetime] NOT NULL,
primary key (f_test_id, f_question_seq),
constraint fk_SatQuestionsToTest foreign key (f_test_id) references t_SatTests(f_sattest_id),
constraint fk_SatQuestionsToSection foreign key (f_section_id) references t_SatSections(f_satsection_id),
) ON [PRIMARY]
satya - 4/25/2014 6:26:32 PM
tsql candidate keys
tsql candidate keys
satya - 4/25/2014 6:27:40 PM
syntax for creating a unique index in sqlserver
syntax for creating a unique index in sqlserver
satya - 4/25/2014 6:28:58 PM
sqlserver create a unique index as part of creating a table
sqlserver create a unique index as part of creating a table
Search for: sqlserver create a unique index as part of creating a table
satya - 4/25/2014 7:00:02 PM
what i did finally
Created a unique key index on the identity column.
satya - 4/25/2014 7:01:38 PM
Example
CREATE TABLE [dbo].[t_SatSections](
[f_SatSection_id] [int] IDENTITY(1,1) NOT NULL,
[f_section_name] [varchar](50) NOT NULL,
[f_section_description] [varchar] (256) NOT NULL,
[f_ownerUserid] [varchar](50) NOT NULL,
[f_created_by] [varchar](50) NOT NULL,
[f_created_on] [datetime] NOT NULL,
[f_last_updated_by] [varchar](50) NOT NULL,
[f_last_updated_on] [datetime] NOT NULL,
constraint PK_t_SatSections PRIMARY KEY CLUSTERED (f_section_name ASC),
constraint IX_t_SatSections unique nonclustered (f_SatSection_id asc)
) ON [PRIMARY]
satya - 4/26/2014 2:58:11 PM
Drop creating tables and foreign keys
once you attach the foreign keys the tables become interdependent. You can no longer drop and create tables in isolation.
So during testing and priming the data remove the foreign keys and put them back and once all is tested you can reintroduce them.
satya - 1/1/2020, 1:56:49 PM
How can I list all foreign key constraints in SQL Server
How can I list all foreign key constraints in SQL Server
Search for: How can I list all foreign key constraints in SQL Server
satya - 1/1/2020, 1:57:06 PM
How can I delete all foreign key constraints in SQL Server?
How can I delete all foreign key constraints in SQL Server?
Search for: How can I delete all foreign key constraints in SQL Server?
satya - 1/1/2020, 1:57:25 PM
Best practices for foreign keys in SQL Server
Best practices for foreign keys in SQL Server
satya - 1/2/2020, 9:44:22 AM
Document on creating tables and their constraints
satya - 1/2/2020, 9:45:14 AM
Example
CREATE TABLE dbo.Employee (
EmployeeID INT PRIMARY KEY CLUSTERED
);
FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(SalesPersonID)
satya - 1/3/2020, 9:05:36 AM
clustered and non clustered indexes
satya - 1/3/2020, 9:11:14 AM
Briefly
1. The rows are ordered by the clustered index
1. which means the rows are already sorted by that index
2. there can be only one clustered index
3. PK is automatically a clustered index
4. sp_helpindex will display indexes on a table
5. A non-clustered index is a traditional index providing a quicker access
6. there can be any number of non clustered indexes