composite keys

composite keys

sql server composite keys

Search for: sql server composite keys

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?

How to make a composite key: SOF


create table my_table (
    id_part1 int not null,
    id_part2 int not null,
    primary key (id_part1, id_part2)
)

sqlserver composite key constraint create table syntax example

Search for: sqlserver composite key constraint create table syntax 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]

tsql candidate keys

Search for: tsql candidate keys

syntax for creating a unique index in sqlserver

Search for: syntax for creating a unique index in sqlserver

sqlserver create a unique index as part of creating a table

Search for: sqlserver create a unique index as part of creating a table

Created a unique key index on the identity column. 


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]

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.

How can I list all foreign key constraints in SQL Server

Search for: How can I list 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?

Best practices for foreign keys in SQL Server

Search for: Best practices for foreign keys in SQL Server

Document on creating tables and their constraints


CREATE TABLE dbo.Employee (
    EmployeeID INT PRIMARY KEY CLUSTERED
);

FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(SalesPersonID)

clustered and non clustered indexes

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