SELECT TOP 1000 [LicensePlate]
,[Manufacturer]
,[Model]
,[Colour]
,[Year]
,[EngineSize]
,[Value]
FROM [Cars2].[dbo].[Cartable1]
Above is my layout for a cars table. I am completely new to SQL and was wondering how I would set 'Licence Plate' as the primary key?
First find any duplicates by that column.
SELECT
C.LicensePlate,
AmountDuplicates = COUNT(*)
FROM
Cars2.dbo.Cartable1 AS C
GROUP BY
C.LicensePlate
HAVING
COUNT(*) > 1
If any record shows up, you need to either delete all the duplicates or update their license plates so they don't repeat anymore.
You will also need to check for NULL values and update or delete them (primary key can't be null).
SELECT
C.*
FROM
Cars2.dbo.Cartable1 AS C
WHERE
C.LicensePlate IS NULL
Then you can add the PRIMARY KEY constraint with:
ALTER TABLE Cars2.dbo.Cartable1
ADD CONSTRAINT PK_Cartable1 -- Name of the constraint
PRIMARY KEY (LicensePlate)
You might get an error if LicensePlate can hold NULL values. You can change it with an ALTER TABLE:
ALTER TABLE Cars2.dbo.Cartable1 ALTER COLUMN LicensePlate VARCHAR(20) NOT NULL -- The proper data type
If you already have a primary key defined on that table you will have to drop it and then create your new one (one table can only have 1 primary key constraint at a time). You can check which one is it with the following query:
USE Cars2; -- The database name here
DECLARE @TableName VARCHAR(100) = 'Cartable1'
DECLARE @SchemaName VARCHAR(100) = 'dbo'
SELECT
ColumnName = Col.Column_Name,
ConstraintName = tab.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS Tab
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS Col ON
Col.Constraint_Name = Tab.Constraint_Name AND
Col.Table_Name = Tab.Table_Name
WHERE
Constraint_Type = 'PRIMARY KEY' AND
Col.Table_Name = @TableName AND
Col.TABLE_SCHEMA = @SchemaName
Once you see the result, you can drop the current primary key with another ALTER TABLE:
ALTER TABLE Cars2.dbo.Cartable1 DROP CONSTRAINT ConstraintNameFromThePreviousQuery
CREATE TABLE Cartable1(
LicensePlate int NOT NULL PRIMARY KEY,
Manufacturer varchar(255) NOT NULL,
Model varchar(255),
Colour varchar(255),
Model varchar(255),
Year int,
EngineSize int,
Value float
);
You do this when you create a table or through an alter table statement:
create table [Cars2].[dbo].[Cartable1] (
LicensePlace varchar(?) primary key,
. . .
);
I recommend adding the primary key when you create the table and before you add any data into it.
If You already have table with data try this ,
ALTER TABLE [Cars2].[dbo].[Cartable1]
ADD CONSTRAINT PRIMARY_KEY_LicensePlate PRIMARY KEY(LicensePlate)
如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！
