CODEWITHSUNDEEP
mysqlsql-server
Ben
Ben

Reputation: 2523

Can I add a Unique key on table creation in SQL?

I am trying to translate a collection of MySQL functions to SQL, and I'm having issues with a UNIQUE KEY issue:

-- -----------------------------------------------------
-- Table testform
-- -----------------------------------------------------
CREATE TABLE `testform` (
    `FormId` INT(11) NOT NULL AUTO_INCREMENT,
    `TTId` INT(11) NULL DEFAULT NULL,
    `TestName` VARCHAR(100) NULL,
    PRIMARY KEY (`FormId`),
    UNIQUE KEY `TF_Composite` (`TTId`, `TestName`));

When I try and test this in SQLFiddle, it's giving me the error

Incorrect syntax near the keyword 'KEY'.

I have tried searching for this, but so far all I have come up with is "Unique Constraints". Is there a difference between a "Key" and a "Constraint" in SQL? And if so, how can I add this in the table creation statement?

Upvotes: 0

Views: 242

Answers (4)

Pramod Tiwari
Pramod Tiwari

Reputation: 297

For MySQL Database

CREATE TABLE `phone` (
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`country` DECIMAL(5,0) UNSIGNED NOT NULL,
`area` DECIMAL(5,0) UNSIGNED NOT NULL,
`number` DECIMAL(8,0) UNSIGNED NOT NULL,
`extension` DECIMAL(5,0) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ix_phone` (`country`, `area`, `number`, `extension`),
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

For alter Table :

ALTER TABLEphone ADD UNIQUE INDEXix_phone(country,area,number,extension);

Upvotes: 0

Teju MB
Teju MB

Reputation: 1383

Try This.

CREATE TABLE testform (
    FormId INT(11) NOT NULL AUTO_INCREMENT,
    TTId INT(11) NULL DEFAULT NULL,
    TestName VARCHAR(100) NULL,
    PRIMARY KEY (FormId),
    CONSTRAINT TF_Composite UNIQUE (TTId,TestName));

More Details..

Upvotes: 0

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

Your syntax is all messed up. Please look at books on-line (MSDN).

https://msdn.microsoft.com/en-us/library/ms174979.aspx

The sample code below create a table in tempdb. This table automatically gets destroyed when the service is restarted.

-- Just a example, throw away after reboot
USE [tempdb]
GO

-- Create the table
CREATE TABLE DBO.TESTFORM 
(
    FORM_ID INT IDENTITY(1, 1) NOT NULL ,
    TT_ID INT NULL,
    TEST_NAME VARCHAR(100) NULL,
    CONSTRAINT PK_FORM_ID PRIMARY KEY (FORM_ID),
    CONSTRAINT UN_COMPOSIT UNIQUE (TT_ID, TEST_NAME)
);

-- Seventies Band
INSERT INTO TEMPDB.DBO.TESTFORM VALUES (1, 'John');
INSERT INTO TEMPDB.DBO.TESTFORM VALUES (2, 'Paul');
INSERT INTO TEMPDB.DBO.TESTFORM VALUES (3, 'Mary');
GO

-- Show data
SELECT * FROM  TEMPDB.DBO.TESTFORM 
GO

The image below shows the data in this table.

enter image description here

Upvotes: 1

Pramod Tiwari
Pramod Tiwari

Reputation: 297

For Better Understanding about Primary and Unique you can refer below page.
Primary and Unique Key Creation

Upvotes: 0

Related Questions